doctrine
[Feature request][DQL] Support CoalesceExpression in ORDER BY
The ORDER BY clause allows to use functions. Today, I tried to use COALESCE there, thinking that it was a function (which it is in SQL). However, the DQL parser implements it as a dedicated type (a CoalesceExpression) rather than a function. Because of that, using Coalesce does not work in ORDER BY clauses.
It would be great to support it.
SELECT COALESCE(yadda, yadda) AS HIDDEN thing FROM ... ORDER BY thing ASC?
yeah, this is the workaround I used. But it would be great to support it properly (it is not different than a function in my opinion).
Btw, the same is true for NULLIF (and CASE)
We don't support any kind of expression in the ORDER BY clause, as far as I can remember. I'm unsure what DB platform support looks like there...
You are wrong (remembering older versions maybe ?). Here is the EBNF:
OrderByItem ::= (
SimpleArithmeticExpression | SingleValuedPathExpression |
ScalarExpression | ResultVariable | FunctionDeclaration
) ["ASC" | "DESC"]
As you can see, FunctionDeclaration is supported (which is how function calls are represented in DQL, which is a weird name as it does not declare anything)
Hmm... fairly sure that ScalarExpression won't work in OrderByItem... Do you know of any tests verifying it? I think the docs may be wrong here.
Even if it's an old issue, I came across this problem today too. Using ORM 3.2.1 and DBAL 4.0.4 with MySQL/MariaDB (both). Orderby doesn't support COALESCE even when mysql can handle it in a direct request. Working around with a coalescing CASE-WHEN does the job. So functions are possible in order-by clause. It would be great to have the coalesce there also possible, and not having to use a workaround with case.
