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

New Issue

admin · 2026-01-22T15:00:27+01:00

admin commented

2026-01-22 15:00:27 +01:00

Originally created by @stof on GitHub (Jun 28, 2016).

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.

Originally created by @stof on GitHub (Jun 28, 2016). 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.

admin commented

2026-01-22 15:00:28 +01:00

@Ocramius commented on GitHub (Jun 28, 2016):

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

@Ocramius commented on GitHub (Jun 28, 2016): `SELECT COALESCE(yadda, yadda) AS HIDDEN thing FROM ... ORDER BY thing ASC`?

admin commented

2026-01-22 15:00:28 +01:00

@stof commented on GitHub (Jun 28, 2016):

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)

@stof commented on GitHub (Jun 28, 2016): 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`)

admin commented

2026-01-22 15:00:29 +01:00

@Ocramius commented on GitHub (Jun 28, 2016):

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

@Ocramius commented on GitHub (Jun 28, 2016): 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...

admin commented

2026-01-22 15:00:29 +01:00

@stof commented on GitHub (Jun 28, 2016):

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)

@stof commented on GitHub (Jun 28, 2016): 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)

admin commented

2026-01-22 15:00:30 +01:00

@Ocramius commented on GitHub (Jun 30, 2016):

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.

@Ocramius commented on GitHub (Jun 30, 2016): 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.

admin commented

2026-01-22 15:00:30 +01:00

@simonsolutions commented on GitHub (Jul 9, 2024):

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.

@simonsolutions commented on GitHub (Jul 9, 2024): 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.

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#5168