Add a select based on a conditional to doctrine query builder

Question

How can you add a select based on a conditional to the doctrine query builder?

I'd like to replicate SQL similar to this:

select p.id, p.id = 3 as first_result
from problem p
order by first_result desc, p.id

Do need a separate column in select part or you just need to order your results based on `p.id = 3`? — M Khalid Junaid, Jul 18 '18 at 06:43
Don't need the separate select result - that's just how I did it in SQL — Geoff Maddock, Jul 18 '18 at 21:28

score 0 · Answer 1 · answered Jul 19 '18 at 05:40

As per docs you could use expression and mark this column as HIDDEN (not to include in results) but can be use in query like in your case to order results

DQL would look like

SELECT p, p.id = 3 AS HIDDEN first_result
FROM YourProblemEntity p 
ORDER BY first_result DESC, p.id

Or you can introduce CASE expression

SELECT p, 
CASE WHEN p.id = 3 THEN 1 ELSE 0 END AS HIDDEN first_result
FROM YourProblemEntity p 
ORDER BY first_result DESC, p.id

See DQL SELECT Examples

Add a select based on a conditional to doctrine query builder

1 Answers1