How to sort null values last with Laravel Eloquent

Photo by Konstantin Kleine on Unsplash

Say you have a column called birthday and you want to sort it based on oldest date first. Your Eloquent query for a model called User will look like this:

User::where('active', true)->orderBy('birthday', 'asc');

Great! It worked. But then when you deployed to production, you quickly remembered that birthday is actually an optional field for users and it’s possible that it’s null. Now what?

Unfortunately, orderBy() will always put null values first if you are sorting by ascending order. So how can we work around that?

By using orderByRaw().

The above query would then be rewritten as:

User::where('active', true)->orderByRaw('-birthday DESC');

A few things to note:

1. orderByRaw() has no comma in between the brackets, unlike orderBy().
2. There is a “minus” sign before the column.
3. Instead of asc, we are now sorting as desc. This is because we have inverted the values in #2 and so the sorting must also be inverted now to get the right results.

And with this, your oldest dates will be shown on the top whereby the null values will now be correctly displayed at the bottom.

Happy coding!

Source: https://laracasts.com/discuss/channels/laravel/how-to-write-eloquent-orderby-to-put-null-columns-to-the-last