5

I have two tables: #__records and #__parents. In the first table some records may have parent records that are stored in the same table but relations between childs and parents are stored in the second table. For example

#__records:

id  name
1   Child1
2   Child2
3   Parent1

#__parents

id  child parent
1     2    3
2     1    3

Now I have the query:

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.id, a.name')
    ->from('#__records as a');
$query->select('b.parent as parentid');
$query->join('LEFT', '#__parents AS b ON b.child = a.id');
$db->setQuery($query);
return $query;

It returns the names of records (a.name) but with IDs of parents (b.parent). I need the second "left join" or another way to get the names of parents instead of their IDs. Something like:

select c.name as parentname from #__records as c where c.id=b.parent
  • If you would like to see a solution that employs recursion to identify parent rows that are one or more "generations" above, please update your question to provide sample data with at least a grandparent relationship among three rows and express how you would like the output to be prepared. Performing recursive queries is a mega-bad idea as Lanah stated below. The tables should be wholly transferred to php for recursive processing. Seem overwhelming? We are here to help. – mickmackusa Feb 2 at 13:42
8
$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select('a.id, a.name, c.id as parent_id, c.name as parent_name')
    ->from('#__records as a');
$query->select('b.parent as parentid');
$query->join('LEFT', '#__parents AS b ON b.child = a.id');
$query->join('LEFT', '#__records AS c ON b.parent = c.id');
$db->setQuery($query);
return $query;

You definitely can do multiple left joins. Just alias the table to a different alias ('c' in this case) and then have the select also alias the columns they aren't the same as what you are pulling for the child.

  • Thank you, works! Just edit the alias parent_id to parentid i.e. without underscore in the first select method as it's in the second one – stckvrw May 7 '15 at 19:53
  • But how to be if record has many parents? Now with your code I get the same record many times in the list for each parent_name. How to push the parent_name to array()? – stckvrw May 17 '15 at 10:24
0

if you need to do "many to many" connections its better to catch table A and B into php and then do the operators, but if you need only if A table i relative to C based on B table, upper answer is good ; )

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.