Order By DAY() Doctrine Symfony2

Question

I,m using Beberlei DoctrinExtensions Bundle to do queries with DAY(), MONTH() and YEAR() on Symfony2.

Everything works on selects. But, when I try to make some OrderBy, I got a Syntax Error.

Here is my code:

SELECT a FROM AppUserBundle:User a WHERE MONTH(a.dataNascimento) >= MONTH(CURRENT_DATE())
    AND DAY(a.dataNascimento) >= DAY(CURRENT_DATE()) 
    ORDER BY DAY(a.dataNascimento)

And the error:

[Syntax Error] line 0, col 151: Error: Expected end of string, got '('

Without ORDER BY clause the query works.

Someone can help me?

Answer 1

I'm fairly sure you can't use functions within DQL ORDER BY clauses (even though you can within SQL). The way around this is to add the field to your SELECT clause with HIDDEN so that Doctrine doesn't return that and shift your results into mixed mode. So your DQL would be:

SELECT a, DAY(a.dataNascimento) AS HIDDEN myday
    FROM AppUserBundle:User a
    WHERE MONTH(a.dataNascimento) >= MONTH(CURRENT_DATE())
        AND DAY(a.dataNascimento) >= DAY(CURRENT_DATE()) 
    ORDER BY myday

Doctrine never explicitly states this in their documentation however a similar example is hidden away on the DQL page.