Search code examples
sqlsymfonydoctrinedql

Doctrine query Builder case statement throw an unexpected syntax error

I'm currently quit new with doctrine, and I've been trying for the last 2 hours to make a select to get the sum of a specific field but only when a boolean is true, and then, another select for the sum of this field when the boolean is false

I tried to make my select like :

sum(case when (f.prevision = 1) then f.total end)
sum(case when (f.prevision = 0) then f.total end)

and then I'm also having others selects and join, but those ones aren't that important. The thing is that doctrine throw an error : 

[Syntax Error] line 0, col 212: Error: Unexpected ')'

And I tried the select with native SQL by console like :

SELECT sum(case when prevision = 1 then total end)
from membre_facturation
GROUP BY id_utilisateur

and have no error, and even the expected result. Is something wrong with doctrine and case statement ?

Thanks in advance for your help. And sorry if some things are missing, I didn't want to write too much code, which would have been kind of "too much"

Solution

  • Alright, I've been trying a bit more offline, then without seeing your answer. And it appears that in the case of the query builder, the ELSE statement isn't optional. I wrote my query like : 

    sum(case when (f.prevision = 1) then f.total else 0 end)

    And it is actually working. I will also check the coalesce function. Maybe it will also work. Or maybe I was doing something else wrong, but the thing is that adding the else statement sorted things out for me.

    Any idea if one is faster than the other one ? From what I saw, "CASE" and "COALESCE" are quite similar.