Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

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?

Try to use with RawValue()

Example:

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


140.8k
Accepted
answer

Or add GROUP_CONCAT as dialect extension.



815
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.