How to ORDER BY CASE in Doctrine2 (Symfony2)

Posted on

How to ORDER BY CASE in Doctrine2 (Symfony2) – Here in this article, we will share some of the most common and frequently asked about PHP problem in programming with detailed answers and code samples. There’s nothing quite so frustrating as being faced with PHP errors and being unable to figure out what is preventing your website from functioning as it should like php and mysql . If you have an existing PHP-based website or application that is experiencing performance issues, let’s get thinking about How to ORDER BY CASE in Doctrine2 (Symfony2).

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%'
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

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.

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.

I had similar issue, where i had to put a few number prefix’es on the top of result.
So I resolved like this:

    $qb = $this->createQueryBuilder('numberPrefix');
            (CASE WHEN country.code = :firstCountryCode THEN 1
            WHEN country.code = :secondCountryCode THEN 2
            WHEN country.code = :thirdCountryCode THEN 3
            WHEN country.code = :fourthCountryCode THEN 4
            ELSE 5 END) AS HIDDEN ORD')
                'firstCountryCode' => $firstCountryCode,
                'secondCountryCode' => $secondCountryCode,
                'thirdCountryCode' => $thirdCountryCode,
                'fourthCountryCode' => $fourthCountryCode,

This one does the job for me when ordering by a relation-table or a local column if no relation exists:

$doctrineQuery->add('orderBy', '(CASE WHEN COUNT(relation_table.uid)>0 THEN relation_table.price ELSE current_table.generic_price END) ASC');