Symfony3-Doctrine : Order by Case When

Question

I'm working on a Symfony 3.4 project.

I want to order a table by updated_at if exists (not null), by created_at if not.

In SQL, this works :

SELECT * FROM `contract`
ORDER BY
  (CASE WHEN ISNULL(`updated_at`) THEN `created_at` ELSE `updated_at` END)
DESC 

I tried a lot of things but I don't manage to make it work with Doctrine Query Builder.

First, I tried this (syntax error) :

$contracts = $em->createQuery(
    'SELECT c
    FROM AppBundle:Contract c
    ORDER BY (CASE WHEN c.updatedAt = :update THEN c.createdAt ELSE c.updatedAt END) DESC')
->setParameter('update', NULL)
->getResult();

Then, I tried this according to this topic, but I have no result (no error) :

$contracts = $rp->createQueryBuilder('c')
    ->select('(CASE WHEN c.updatedAt != :update THEN 1 ELSE 0 END) AS HIDDEN orderDate')
    ->orderBy('orderDate', 'DESC')
    ->addOrderBy('c.createdAt', 'DESC')
    ->setParameter('update', NULL)
    ->getQuery()->getResult();

How can I sort my contracts by their updated date if they have been updated, or by their created date if they haven't been modified ?

If it helps, I use the DoctrineExtensions bundle for other queries, I saw IfNull and IfElse classes but I don't how to use them with my case.

Answer 1

After several attempts, I finally found the solution.

Use COALESCE : returns the first value not null in the list, so if A is null and B not null, then COALESCE(A,B) will return B.

$contracts = $rp->createQueryBuilder('c')
  ->select('c')
  ->addSelect('COALESCE(c.updatedAt,c.createdAt) AS HIDDEN orderDate')
  ->orderBy('orderDate', 'DESC')
  ->getQuery()->getResult();

No need to use the DoctrineExtensions bundle.