In this lesson, we're going to look at a couple of examples of situations where you want to order your results with null values always last. Imagine you have a books page like this, which lists all the Book models in a library. By default, they're being sorted by the book name, but you decide you want to see the books that are currently checked out first. For example, you want to see this book Bored by Jeffrey Wei listed first, along with all other books that are currently checked out. How would you do that? Well, if we look at our books migration, we can see that a Book has a user_id foreign key for the User that currently has it checked out, which technically means we could just sort by the user_id column to show the borrowed books first. Let's try that. Let's go to the BooksController and update our query. Right before the existing orderBy name statement, let's add an orderByDesc user_id.

Let's go to the BooksController and update our query. Right before the existing orderBy name statement, let's add an orderBy descending userId. And now if we hit refresh in the browser, we can see that all our borrowed books are now listed first. Great. Well, almost great. If we look closely here, we can see that this has actually broken our bookName ordering for the books that have been checked out. For example, agile web development with Rails starts with an A, so it should be listed first in our list. And this makes sense since we're now ordering by the userId, which, of course, is going to be different for each user. So what we really want to sort by is whether the userId is null or not. And we can actually do this easily enough with an orderByRaw method. Let's update our query orderByRaw userId is null.

And we can actually do this easily enough with an orderByRaw method. Let's update our query orderByRaw user_id is null. What we're doing here is running an expression in our orderBy that checks to see if the user_id is null or not, meaning it'll return either a true or a false, and then it'll sort based on that. If we go to the browser and hit refresh, we can see that everything is working the way we want. Our borrowed books are still listed first and the books are now being properly ordered by their name. Nice. Let's look at another example. In the same app, we also have a users page. Now, imagine we wanted to add the ability to order these users by their town by clicking on that heading and clicking the heading again will toggle a direction the opposite way.

Now, imagine we wanted to add the ability to order these User models by their town by clicking on that heading and clicking the heading again will toggle a direction the opposite way. As you can see, I already have the template set up to do this. We just need to update the UsersController to actually do the ordering. Let's go to the UsersController and make this change. Let's start by adding a when condition to our query that checks to see if the sort query string is set to town. Then we're going to run whatever query we set within this closure. query, orderBy, town, and we're going to sort that by the direction that we're getting in the request. Nice and simple. Let's give this a try in the browser. And if we click on our heading to order the users by their town ascending,

Nice and simple. Let's give this a try in the browser. And if we click on our heading to order the users by their town ascending, we can see that we get a bunch of null values first. If we look through the paginated results, we can see that it is ordering the town properly, but because some users have no town set, we're getting the null values first. I'd much rather see the null values always listed last regardless of the sort direction. And of course, we now know how to do this. Before ordering by the town, let's first add a raw orderBy that checks to see if the town is null. And now if we hit refresh in the browser, you can see that this is now working the way we want it. If we're sorting ascending, the null values are last.

And now if we hit refresh in the browser, you can see that this is now working the way we want it. If we're sorting ascending, the null values are last. And if we sort descending, the null values are again last. This is much better. So using an is null expression like this is a super handy technique when you want to ensure that your null values are always ordered last. Okay, before we wrap up this lesson, I want to show you one extra little bonus tip for those of you who are using Postgres. Postgres actually has built in support for ordering with nulls always last. Let's switch our database driver to Postgres from MySQL.

Postgres actually has built in support for ordering with nulls always last. Let's switch our database driver to Postgres from MySQL. Now let's go back to our UsersController and update our query. Let's remove the existing orderBy logic and add a new raw orderBy. Query orderByRaw passing in the direction with nulls last. And now if we go back to our browser and reorder ascending, we can see that it's still working the way we want it. If we go to the last page here, we can see that our null values are last. And if we sort descending, again, it's still working. Let's take a peek at our query in the Laravel debug bar.

And if we sort descending, again, it's still working. Let's take a peek at our query in the Laravel debug bar. SELECT * FROM users, ORDER BY town DESC with NULL values always last. And then we ORDER BY name ASC. Very cool. Postgres makes this super easy to do. However, we need to be careful here. We just introduced a serious SQL injection vulnerability. We're taking the direction from the request and simply inlining it directly in our query. That's no good because someone can theoretically now change the direction to something else and modify our query.

That's no good because someone can theoretically now change the direction to something else and modify our query. So how do we protect ourselves from this? You might be thinking, can't we just pass the direction as a binded parameter? And unfortunately, no, it's not possible to bind SQL keywords like ascending or descending. You can only bind values. Instead, we can solve this using a simple validation check. We'll set the direction to equal and then we'll check to see if the direction lowercase equals ascending. And if it does, then we'll just use ascending. Otherwise, we'll use descending and then we'll update our query to use this direction value.

And if it does, then we'll just use ascending. Otherwise, we'll use descending and then we'll update our query to use this direction value. By doing it this way, we know for sure that we'll always end up with either ascending or descending as our direction. However, I find this kind of long and messy and not very expressive. To be honest, here's how I'd prefer to write this. Query::orderByNullsLast with the town as our column name and the direction as the direction from our request. Basically exactly like a regular orderBy statement, except with the nulls last added to it. This is a great candidate for a query builder macro. Let's add one. Let's go to our AppServiceProvider and add this in our boot method.

Let's add one. Let's go to our AppServiceProvider and add this in our boot method. Builder, and we'll just remember to import this class right away. And what we're looking for here is the Illuminate\Database\Query\Builder. Then we'll call the macro function to create our new orderByNullsLast method. As a second argument, we'll provide a callback, which takes the columnName as the first argument, and the direction as the second argument, which will default to ascending. Next, we'll just write the same orderByRaw query that we wrote in our UsersController. Return this orderByRaw column direction with nulls last. However, as we've already talked about, we need to protect ourselves from SQL injection vulnerabilities.

Return this order by raw column direction with null last. However, as we've already talked about, we need to protect ourselves from SQL injection vulnerabilities. Let's start with the column name. Eloquent provides a wrap method to help ensure that the variable is always handled as a column name and not executed as an expression. And for the direction, we can just use the exact same technique that we used in our UsersController, where we check to see if the direction is equal to ascending. And if it is, we use ascending. Otherwise, we use descending. Okay, that's it. Our new macro is ready to go.

Okay, that's it. Our new macro is ready to go. Let's give it a try in the browser. And if we hit refresh, keeping an eye on this query, we can see that everything has basically stayed the same, except our town column name has now been wrapped in quotes. And if we change our sort direction to descending, we can see that we're still getting our null values last. So, by adding a new macro, we were able to nicely simplify our code in the UsersController, plus we now have this new query builder method available should we want to use it anywhere else in our application. Creating macros like this is a great way to extend Eloquent for your application to add additional database vendor specific functionality that doesn't really make sense to include in Laravel's core.

Creating macros like this is a great way to extend Eloquent for your application to add additional database vendor specific functionality that doesn't really make sense to include in layer about core.