We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

Parse error when using order by inside a GROUP_CONCAT as part of a having clause

When using the query builder I'm attempting to use a having clause that is a group concat that uses a order by to ensure the proper order. However I get an error "Syntax error, unexpected token ORDER, near to ' BY bar.bazId) '1,2,3" when I attempt this. The general query looks like

SELECT foo.* FROM foo JOIN bar ON bar.fooId = foo.id AND bar.bazId IN (1,2,3) GROUP BY foo.id HAVING GROUP_CONCAT(bar.bazId ORDER BY bar.bazId) = '1,2,3'

This works when directly running the sql in mysql workbench and works in the query builder if I remove ORDER BY bar.bazId. Is there a proper way to get this functionality using the query builder, is this a quirk of mysql that isn't supported or is this a bug?



6.8k

Try to use with RawValue()

Example:

->having(new \Phalcon\Db\RawValue("GROUP_CONCAT(bar.bazId ORDER BY bar.bazId) = '1,2,3')")


145.0k
Accepted
answer

Or add GROUP_CONCAT as dialect extension.



1.1k
edited Nov '19

Using RawValue() didn't work but creating a dialect extension did. The dialect I used is

$dialect->registerCustomFunction(
'GROUP_CONCAT',
function($dialect, $expression) {
return sprintf(
" GROUP_CONCAT(%s)",
StringHelper::removeQuotes($dialect->getSqlExpression($expression['arguments'][0]))
);
}
);

In the query I had to wrap my GROUP_CONCAT argument in quotes in order for the custom function to be executed. I'm not sure why but this also caused them to be passed as part of the string in the variable$expression['arguments'][0]['value'].Thanks for the help.