How to ORDER BY CASE in Doctrine2 (Symfony2)

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