JDatabaseQuery - How to select a group_concat MySQL with Joomla CMS 3.8 API

For Joomla! 3.x Coding related discussions, please use: http://groups.google.com/group/joomla-dev-general

Moderator: ooffick

Forum rules
Please use the mailing list here: http://groups.google.com/group/joomla-dev-general rather than this forum.
mumia7575
Joomla! Fledgling
Joomla! Fledgling
Posts: 1
Joined: Wed Feb 14, 2018 2:48 am

JDatabaseQuery - How to select a group_concat MySQL with Joomla CMS 3.8 API

Postby mumia7575 » Wed Feb 14, 2018 2:56 am

Hi, I read the whole JDatabaseQuery documentation and I can't find anything that solves this.

Any idea how can I accomplish something like this?

My code:

Code: Select all

$query = $db->getQuery(true);
$query->select($db->quoteName(array('a.id','a.name', 'a.licence', 'a.site', 'a.certificate', 'GROUP_CONCAT(DISTINCT b.produtos separator \' \')', 'GROUP_CONCAT(DISTINCT c.sector_actividade 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');
Last edited by toivo on Wed Feb 14, 2018 10:21 am, edited 1 time in total.
Reason: mod note: moved to 3.x Coding

User avatar
toivo
Joomla! Exemplar
Joomla! Exemplar
Posts: 8932
Joined: Thu Feb 15, 2007 5:48 am
Location: Nottingham, UK
Contact:

Re: JDatabaseQuery - How to select a group_concat MySQL with Joomla CMS 3.8 API

Postby toivo » Wed Feb 14, 2018 10:47 am

You need to assign an alias to the concatenated strings, something like this:

Code: Select all

$query->select($db->quoteName(array('a.id','a.name', 'a.licence', 'a.site', 'a.certificate', 'GROUP_CONCAT(DISTINCT b.produtos separator \' \') AS Portfolio', 'GROUP_CONCAT(DISTINCT c.sector_actividade separator \' \') AS Sector')));
Toivo Talikka, Global Moderator
my first programs were assembled and run in 16KB :)
http://archive.computerhistory.org/resources/text/GE/GE.GE-115SystemSoftware.1967.102646096.pdf#zoom=100


Return to “Joomla! 3.x Coding”

Who is online

Users browsing this forum: No registered users and 5 guests