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.

Answer 2

CASE is vendor-specific and not supported natively by doctrine.

If the result is smallish, my recommendation is to pull the whole result set then sort the array.

If the result set will be too large, you should write a native query and hydrate the entity. See the Doctrine Documentation on Native SQL for more information on this. It looks scary, but makes sense once you walk through an example.

As a last resort, you could just bypass doctrine and use low-level native SQL. See this post for details.

I know Doctrine Extensions has an IfElse function that may work, but I haven't heard many success stories.