Ordering with ActiveRecord and PostgreSQL may seem like an obvious and simple thing: in most cases you just specify one or several criteria with direction of ordering like
order(name: :asc) and that’s all, maybe in more complex scenarios you would need to use
CASE statement to provide some more customized conditions. But how could we approach sorting with forcing blank values to be the last ones, regardless of the direction of the sort? You might be thinking now about
NULLS LAST statement for this purpose, but that’s not going to handle empty string. For this case you need something special: time to meet
NULLIF conditional expression.
NULLIF to the rescue
Imagine that you want to sort users by
fullname ascending and for whatever reason it happens that the records contain both null and empty string values (besides some meaningful data of course). As previously mentioned, the following expression:
order("fullname ASC NULLS LAST") won’t be enough: it will work only for null values and the blank strings will get in a way. Fortunately, PostgreSQL offers
NULLIF conditional statement which takes 2 arguments. If argument 1 equals argument 2 it will return
NULL, otherwise it will return argument 1. In our case we want to return
fullname is a blank string, so the final ordering statment could look like this:
order("NULLIF(fullname, '') ASC NULLS LAST"). That’s all you need for such sorting!
NULLIF conditional expression can help tremendously in sorting by nullifying empty strings and always placing them in the end when combined with
NULLS LAST statement.