Syntax error when using ORDER BY inside GROUP_CONCAT

[jklaze]

Hello everyone,
today I was facing this weird behaviour: trying to execute a raw sql query from phalcon I get an "unexpected token ORDER, near BY ..[etc]".
My query is something like this:

$query = "SELECT B.code, GROUP_CONCAT(COALESCE(C.name, '' ) ORDER BY A.index) AS names FROM GoSales\Models\TableA A LEFT JOIN GoSales\Models\TableB B ON 1 = 1 LEFT JOIN GoSales\Models\TableC C ON C.B_id = B.id AND C.A_id = A.id GROUP BY B.id ORDER BY B.code"; return \Phalcon\DI::getDefault()->get('modelsManager')->executeQuery($query);

I'm sure there is no syntax error in the query since in phpMyAdmin it works smooth as butter but in my Phalcon code prints the error mentioned before.
I already visited this forum Q&A: https://forum.phalcon.io/discussion/3540/syntax-error-when-try-to-use-order-by-with-group-concat#
and this guy had my exact same problem but he was using PHQL and the final answer was to use raw SQL.

Also I enabled the phpMyAdmin sql log and the query does not appear so I'm sure it's not even executed at all

[niden]

GROUP_CONCAT is not supported natively by Phalcon's adapters since it is driver specific. So your query will not run.

In order to make Phalcon aware of this functionality, you can add a custom function to the dialect and that will do the trick. Have a look at this page and examples

https://docs.phalcon.io/4.0/en/db-phql#custom-dialect

[jklaze]

The fact is that by removing the ORDER BY the GROUP_CONCAT is actually working, maybe is just partially supported(?)

[niden]

Perhaps PHQL ignores it and just sends it through and when ORDER BY is around it doesn't. I do know for a fact that there is no code supporting that. The question has been raised since v1.3 (believe it or not) so the custom functions functionality was added to allow for such cases. Try it with the custom function if you have a bit of time and see if that suits your needs.

PS: out of curiosity I did a grep in the phalcon repo and yes there is no match of GROUP_CONCAT