Unable to use SELECT MAX CASE with doctrine

Question

I'm trying to create a DQL request using max(case...) I am using PostgreSQL

Here's a table exemple : (Key values are random)

[------TABLE------]
[TYPE |KEY|VALUE  ]
[SWEET|843|WAFFLE ]
[SWEET|258|NUTELLA]

And here's what i'd like :

[TYPE |DESSERT|SAUCE  ]
[SWEET|GAUFRE |NUTELLA]

And here's the (working) SQL i've done :

select 
    type,
    max(case when key=843 then valeur end) as dessert,
    max(case when key=258 then valeur end) as sauce
from
    TABLE
group by type

But, I can't convert this SQL query to DQL, here's what I tied (not working) :

$qb = $this->createQueryBuilder('table')
->select('table.type')
->addSelect('max(case when table.key=843 then table.value end) as dessert')
->addSelect('max(case when table.key=258 then table.value end) as sauce')
->addGroupBy('table.type')

And here's the error I have :

 [Syntax Error] Error: Unexpected ')' 

I tried with and without max, and the DQL generated is correct. Can you help? Thanks a lot guys!

Answer 1

The solution is to add the ELSE in the case when clause:

$qb = $this->createQueryBuilder('table')
->select('table.type')
->addSelect('max(case when table.key=843 then table.value else null end) as dessert')
->addSelect('max(case when table.key=258 then table.value else null end) as sauce')
->addGroupBy('table.type')

Have a nice day