Orm 4: NULLs ordering

Orm 4.0 will arrive later this quarter and will bring handful of small features. One of them is explicit NULLs ordering.

The ordering of NULLs is a topic with a “random” default points of view. It depends if you ask a PHP developer, MySQL developer or even PostgreSQL developer. The each platform, language, SQL server choose how to behave when one operand is a NULL and it seems almost everyone differ in the opinion on it.

Let’s see the following table with default behavior when NULL appears.

PHPMySQLPostgreSQLSQL Server
ASC NULLS FIRST โœ” โœ” โœ”
ASC NULLS LAST โœ”
DESC NULLS FIRST โœ”
DESC NULLS LAST โœ” โœ” โœ”

What’s more, Oracle behaves the same as PostgreSQL.

This differences in default behavior led us to decision remain default ordering behavior as “undefined” and provide new ordering constants which will unify NULLs ordering for ArrayCollection & DbalCollection regardless SQL engine.

We are introducing new ordering constants:

  • ICollection::ASC_NULLS_FIRST
  • ICollection::ASC_NULLS_LAST
  • ICollection::DESC_NULLS_FIRST
  • ICollection::DESC_NULLS_LAST
// sort books by their publish date and put unpublished books first
$books = $books->orderBy('publishedAt', ICollection::DESC_NULLS_FIRST);

These new constants provides unified & safe experience when needed. As usual, you may combine them with other ordering expressions when two entities are considered the same.

Only with your support we can make Orm the best Orm ever. Test it by requiring "nextras/orm": "4.0.x-dev". Comment or open an issue on GitHub. Thank you.