How to ORDER BY CASE in Doctrine2 (Symfony2)

user_2931469 0
Viewed 15687

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
symfony case-when doctrine-orm mysql php
4 Answers

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,
            ]
        );
user_100056920

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