2

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

  • can you add the code you have written in getListQuery() function? – subashbasnet8 Apr 21 '15 at 9:21
3

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
  • Thanks, it works! But can you rewrite it like example by the link – stckvrw Apr 22 '15 at 5:09
  • Edited my answer. Take a look. – Rene Korss Apr 22 '15 at 6:44
5

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!

  • @stckvrw This should be the preferred/implemented solution because using subqueries in the SELECT clause means firing the subquery for each row in the main query -- not good for performance. stackoverflow.com/a/49738666/2943403 – mickmackusa Feb 4 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.