0

I am looking to group_concat the results from the fields from multiple rows where there is a an intermediate table

'#__ext_resources'

a.id a.title
1    Blaa
2    Blee 
3    Blii 

Joining table '#__ext_resource_categories'

rc.resource_id rc.category_id
1               1
1               2
2               2
3               3

Table whose title field needs concatenating '#__categories'

c.id c.title
1    category boo
2    category foo
3    category noo

So the query I have created will give resource fields plus categories plus the category id

 a.id a.title category_id c.title
 1     Blaa   1,2         boo, foo
 2     Blee   2           foo  
 3     Blii   3           noo

The following works as MySQL

SELECT a.id, a.name, 
GROUP_CONCAT(DISTINCT rc.category_id) category_ids,
GROUP_CONCAT(DISTINCT c.title) categories,

FROM db_ext_resources a
LEFT JOIN db_ext_resources_categories   rc  ON a.id                 = rc.resource_id
LEFT JOIN db_categories                           c   ON rc.category_id       = c.id

WHERE a.id = 192

But what is the Joomla code please? The following works - with no intermediate joining table

$db = $this->getDbo();

$query = $db->getQuery(true);

$query->select('a.*')
->select('(SELECT GROUP_CONCAT(DISTINCT rc.category_id  SEPARATOR ", ") category_ids 
          FROM #__ext_resources_categories  WHERE resource_id = a.id) category_ids')
->join('LEFT', $db->quoteName('#__ext_resources_categories',  'rc') . ' ON (' . $db->quoteName('a.id')                . ' = ' . $db->quoteName('rc.resource_id') . ')')
                                ->join('LEFT', $db->quoteName('#__categories',                          'c') .  ' ON (' . $db->quoteName('rc.category_id')      . ' = ' . $db->quoteName('c.id') . ')')

BUT if I put

->select('(SELECT GROUP_CONCAT(DISTINCT c.title SEPARATOR ", ")        categories WHERE rc.resource_id = a.id AND c.id = rc.category_id) categories')

in there or numerous variants it doesn't seem to work nor

->select('(SELECT GROUP_CONCAT(DISTINCT c.title SEPARATOR ", ") categories FROM #__categories LEFT JOIN  '.$db->quoteName("#__dbsupportplus_resources_categories",  "rc").' ON a.id = rc.resource_id AND rc.category_id = c.id) categories')

I have tried putting the whole MySQL statement into a $query->select('(".$var.")')

To no avail. Any suggestions please?

  • If your question is resolved, please award the green tick to one of the answers below so that this page is deemed resolved in the system. – mickmackusa Jan 28 at 13:34
1

Finally did it sticking everything inside a ->select

$MySQL = all the rest of the raw mysql query including the GROUP_CONCAT, FROM, LEFT JOIN statements
$query->select('a.*')
      ->select($mysql);

But if anyone has a way that uses the Joomla code it would be much appreciated.

  • This would be the easiest method of running an query of this type. Joomla is still lacking query methods for database interaction such as group_concat, subqueries, etc. Maybe one day we will have those. For now the method you are using will have to suffice. – Terry Carter Mar 14 '16 at 18:35
0

Here is the Joomla method-based version of your working mysql:

$query = $db->getQuery(true)
            ->select(
                array(
                    "A.id",
                    "A.name",
                    "GROUP_CONCAT(DISTINCT B.category_id) category_ids",
                    "GROUP_CONCAT(DISTINCT C.title) categories"
                )
            )
            ->from("db_ext_resources A")
            ->leftJoin("db_ext_resources_categories B ON A.id = B.resource_id")
            ->leftJoin("db_categories C ON B.category_id = C.id")
            ->where("A.id = 192");

I am using an array in the select() call to minimize code "width". I am not calling qn() because none of the tablenames or columns require it. If the 192 is actually a variable, be sure to cast it as an integer with (int) for security reasons.

This is what $query->dump() will produce:

SELECT A.id,A.name,GROUP_CONCAT(DISTINCT B.category_id) category_ids,GROUP_CONCAT(DISTINCT C.title) categories
FROM db_ext_resources A
LEFT JOIN db_ext_resources_categories B ON A.id = B.resource_id
LEFT JOIN db_categories C ON B.category_id = C.id
WHERE A.id = 192

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.