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

Dwain

In general, try not to confuse DQL (Doctrine's Query Language) with SQL - DQL is its own dialect, and simply goes about things a different way, which would make an implementation of case highly messy to say the least. An advantage of this is that it forces you to do business logic stuff like that case statement elsewhere where it is more appropriate. – Nonsuch

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.

Obola

Thank you for the "HIDDEN" thing - never seen that, but a thing I really have been needed for several years :) – Rhiana

The doctrine changes the name of "ORD" to "sclr16" in this case. What can I do? – Giacinta

It's worth to mention that "CASE" statement doesn't work properly without "ELSE". We've struggled for an hour yesterday to figure out why it doesn't work like that :) – Noelyn

Answer 2

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');
    $qb
        ->select('country.code','numberPrefix.prefix')
        ->addSelect('
            (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')
        ->innerJoin('numberPrefix.country','country')
        ->orderBy('ORD, country.id')
        ->setParameters(
            [
                'firstCountryCode' => $firstCountryCode,
                'secondCountryCode' => $secondCountryCode,
                'thirdCountryCode' => $thirdCountryCode,
                'fourthCountryCode' => $fourthCountryCode,
            ]
        );

Uncrown

Answer 3

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.

Hanse

@Benjamin your link is broken – Wyrick

@PaoloStefan Updated link: Doctrine 2 DQL does support CASE natively, see Case Expressions – Spyglass

Answer 4

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');

Heptane