Chris Chris - 1 month ago 14
PHP Question

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

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

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.