PostgreSQL In Action: Sorting With NULLIF Expression
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 NULL if 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!
Wrapping up
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.