How to ORDER BY CASE in Doctrine2 (Symfony2)

Question

I want to run this query by using Doctrine in Symfony 2.3. But it seems like Doctrine does not understand CASE statement. Can anyone help? Thank you in advance!

SELECT max(id) id, name
FROM cards
WHERE name like '%John%'
GROUP BY name
ORDER BY CASE WHEN name like 'John %' THEN 0
           WHEN name like 'John%' THEN 1
           WHEN name like '% John%' THEN 2
           ELSE 3
      END, name

Answer 1

If you are using createQueryBuilder then you can use like

$query->addSelect("(CASE WHEN name like 'John %' THEN 0
           WHEN name like 'John%' THEN 1
           WHEN name like '% John%' THEN 2
           ELSE 3 END) AS HIDDEN ORD ");
$query->orderBy('ORD', 'DESC');

Note that you must have "HIDDEN".

You can do with doctrine native query as well.