search

doctrine / orm

Doctrine Object Relational Mapper (ORM)
https://www.doctrine-project.org/projects/orm.html
MIT License
9.91k stars 2.5k forks source link

[Feature request][DQL] Support CoalesceExpression in ORDER BY #5905

Open stof opened 8 years ago

stof commented 8 years ago

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.

Ocramius commented 8 years ago

SELECT COALESCE(yadda, yadda) AS HIDDEN thing FROM ... ORDER BY thing ASC?

stof commented 8 years ago

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)

Ocramius commented 8 years ago

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

stof commented 8 years ago

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)

Ocramius commented 8 years ago

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.

simonsolutions commented 1 month ago

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.