GROUP_CONCAT Issue

48 Posts

herooutoftime Reply #1, 15 years, 3 months ago

Hi,

I’m facing a problem which I’m absolutely not getting solved. We started developing a component in modX 2.0.4-pl, now we made an upgrade to modX 2.1.1-pl.

$c->select('`Table`.*, `Document`.`pagetitle` AS subject, `Document`.`publishedon` AS date, GROUP_CONCAT(";", CONCAT_WS(",", Group.id,Group.name,Group.color)) AS groups');

Below are the toSQL() method outputs...

In 2.0.4 everything is alright, the select is working as expected.

SELECT `Newsletter`.*, `Document`.`pagetitle` AS subject, `Document`.`publishedon` AS date, GROUP_CONCAT(DISTINCT CONCAT_WS(",", Group.id, Group.name, Group.color)) AS groups

In 2.1.1 something’s messing around with the `backticks`...

SELECT `Newsletter`.*, `Document`.`pagetitle` AS subject, `Document`.`publishedon` AS date, GROUP_CONCAT(DISTINCT CONCAT_WS(", [color=red]`"`[/color], Group.id, Group.name, Group.color)) AS groups

The components code wasn’t changed at all, it’s on the same server, same webserver, same mysql-server, the database is a duplicate. Nothing really changed, but we are still kind of stuck in this issue.

Any help would be really appreciated.

Thanks a lot.

**** Environment Information ****
modX 2.0.4-pl (traditional) + modX 2.1.1-pl (traditional)
Linux 2.6.32-27-generic #49-Ubuntu SMP x86_64 GNU/Linux
Ubuntu 10.04.1 LTS
PHP 5.3.2-1ubuntu4.9 with Suhosin-Patch (cli) (built: May 3 2011 00:45:52)
MySQL-Server version: 5.1.41-3ubuntu12.9 (Ubuntu)

MODX Staff
10,725 Posts

opengeek Reply #2, 15 years, 3 months ago

A slight behavior change in xPDO 2.1 requires you to express this as:

$c->select(array(
    'subject' => '`Document`.`pagetitle`',
    'date' => '`Document`.`publishedon`',
    'groups' => 'GROUP_CONCAT(";", CONCAT_WS(",", Group.id,Group.name,Group.color))'
));

Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek

48 Posts

herooutoftime Reply #3, 15 years, 3 months ago

Thanks OpenGeek,

that solved my problem...
Looks like I’m kind of lazy reading the changelog...
Sorry for that.