How to display names of parents instead of their ids

Question

I'm creating my custom component. In backend I have the list of single records. They have parent records in the DB as parent_id and therefore are displayed in backend only as ids. How to display names of these parents instead of their ids?

In my models/records.php there is the query:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('id, header, parent_id, ordering, published');
$query->from('#__records');
return $query;

and in views/records/view.html.php there is $this->items = $this->get('Items'); The names of records are stored in the DB in the field header

Answer 1

Use subquery.

SELECT 
    *,
    (SELECT header FROM `tablename` WHERE `id`=r.parent_id) AS parent_header
FROM 
    `tablename` AS r

parent_header has name of parent record.

With Joomla! JDatabaseQuery object

$db = JFactory::getDBO();

// Create subquery to get `header` column value
$subQuery = $db->getQuery(true)
    ->select('header')
    // You have this row in same table? If not, change table name
    ->from('`#__records`')
    ->where('id=r.parent_id');

$query = $db->getQuery(true)
  ->select('id, header, parent_id, ordering, published')
  // Inject subquery select
  ->select('('.$subQuery->__toString().') AS parent_header')
  ->from('`#__records` AS r');

return $query;

Query output:

SELECT 
    id, header, parent_id, ordering, published,
    (
        SELECT 
            header
        FROM 
            `lab0f_records`
        WHERE 
            id=r.parent_id
    ) AS 
        parent_header
FROM 
    `lab0f_records` AS r

Answer 2

You need to left join the header name when you create the query in your model getListQuery() method. The actual code using the Joomla query object looks like so:

$query->select('a.id, a.header, a.parent_id, a.ordering, a.published');
$query->from('#__records AS a');

$query->select('p.header as parent_name');
$query->join('LEFT', '#__records AS p ON p.id = a.parent_id');

This would be the official Joomla way to grab and use the actual name versus an ID. You can access the parent name name using the following in your views:

$this->item->parent_name

One side note is using pure MySQL syntax is not recommended, especially if you plan on releasing this component to JED (Joomla Extension Directory) as it will not work on any Joomla installations not using MySQL.

By exclusively using the Joomla DB abstraction classes to handle all DB CRUD operations, you're ensuring your component's code can work with any supported DB any particular Joomla installation is using.

Hope this helps!