JDatabaseQuery - How to select a group_concat Joomla 3.8 CMS API

159 views
Skip to first unread message

ExtremeGame Tuga

unread,
Feb 14, 2018, 3:02:52 AM2/14/18
to Joomla! CMS Development
Hi, I am trying to achieve a query on Joomla with the new API.

I read the documentation and there is nothing that explains how I can select a group_concat available in mysql.

Any example of how I can accomplish this is welcome.

My code so far:

$db = JFactory::getDbo();

$query = $db->getQuery(true);
$query->select($db->quoteName(array('a.id','a.name', 'a.licence', 'a.site', 'a.certificate', 'GROUP_CONCAT(DISTINCT b.products separator \' \')', 'GROUP_CONCAT(DISTINCT c.activities separator \' \')')));
$query->from($db->quoteName('atable', 'a'));
$query->join('INNER', $db->quoteName('btable', 'b') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('b.parentid') . ')');
$query->join('INNER', $db->quoteName('ctable', 'c') . ' ON (' . $db->quoteName('a.id') . ' = ' . $db->quoteName('c.parentid') . ')');
$query->group($db->quoteName('a.id'));
$query->order('a.name ASC');

This didn't work.

Viper

unread,
Feb 14, 2018, 5:16:20 AM2/14/18
to Joomla! CMS Development
quoteName() does not support SQL function to quote. Only column names accepted.

$query->select(
            $db
->quoteName(array('a.id','a.name', 'a.licence', 'a.site', 'a.certificate'))
       
)
       
->select('GROUP_CONCAT(DISTINCT ' . $db->quoteName('b.products') . ' separator \' \')')
       
->select('GROUP_CONCAT(DISTINCT ' . $db->quoteName('c.activities') . ' separator \' \')');
Reply all
Reply to author
Forward
0 new messages