How to ORDER BY CASE in Doctrine2 (Symfony2)
case-when, doctrine-orm, mysql, php, symfony
Solution
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.
Problem
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 ```