How can I order NULL values first on a Doctrine 2 collection using annotations?

Question

I have a project using Symfony 2 and containing Doctrine 2 entities. Some of these entities are related to each other. This association is defined by an annotation:

/**
 * @ORM\OneToMany(targetEntity="Event", mappedBy="firstEntityId" cascade={"persist", "remove"})
 * @ORM\OrderBy({"dateEnd" = "DESC", "dateBegin" = "DESC"})
*/
private $events;

As you can see, this association contains several events that have a start and an end date. When retrieving this collection, I want to have the most recents events (i.e. those which have not ended yet or have ended recently) sorted first.

The problem with the current approach is that it will sort events with an end date of NULL after all other events.

How can I tell Doctrine to sort the events with an end date of NULL first and then sort the remaining events by descending end date?

I have so far seen several questions on SO about how to tell Doctrine how to order entities. However, none of them mention annotations. Tricks with reversing the sign as suggested e.g. in Doctrine 2 Order By ASC and Null values in last do not work because Doctrine does not accept anything other than a property name and ASC or DESC in the annotation.

Answer 1

It's an old post but i found a pretty simple solution if you are using doctrine query builder :

$sortDirection = 'ASC';
$qb = $this->createQueryBuilder('e');
$qb->addSelect('CASE WHEN e.valueToOrder IS NULL THEN 1 ELSE 0 END AS HIDDEN myValueIsNull');

//other stuffs
//$qb->where ...

$qb->orderBy('myValueIsNull','ASC');
$qb->addOrderBy('e.valueToOrder',':sortDirection');
$qb->setParameter(':sortDirection',$sortDirection);

return $qb->getQuery()->getResult();

PHP way, besides being slower, avoid to use offsets (for an infinite scroll for example)

Thanks to https://stackoverflow.com/a/23420682/6376420

Answer 2

Probably not. There is an SQL syntax that allows to ORDER BY column DESC NULLS FIRST. However, it is not supported by all DB vendors and thus if I scanned the merge request correctly, has not been merged into DQL. Depending on which database platform you use, you may be lucky. The comments in the merge request provide insight into how to extend Doctrine at different points to implement the behavior, maybe that helps you to do it by yourself.

Answer 3

My workaround is to create add an additional select to the query and extract the entity from the resulting array collection, it would be better to have it only examined in query time and not select it (to keep the result array intact) but I have not found a proper solution to this yet (using QueryBuilder).

$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$queryBuilder->select('e')
    ->from(Entity::class, 'e')
    // We use ZZZZ here as placeholder to push the null values last, use 'AAAA' to sort them first.
    ->addSelect('CASE WHEN(e.name IS NULL) THEN \'ZZZZ\' ELSE e.name END AS name')
    ->addOrderBy('name', 'ASC');

    // As we have a array result due to the "addSelect()" above, we must extract the entities now, in this example by looping over the result array.
    $entities = array_map(function ($contributor) {
        return $contributor[0];
    }, $queryBuilder->getQuery()->getResult());