In this lesson, we're going to look at how we can order records by the value of a belongsToMany relationship column. Consider this page which lists all the books in a library management app. We're showing the book name, author, and the last checkout information, which includes the borrowed date and the user's name. Our database includes a users table, a books table, and a checkouts table. The checkouts table is acting as a pivot table between our users and our books tables. You can see that the checkouts table also includes a borrowed date column, which keeps track of when the users checked out the book. Let's take a look at our models as well. We have a User model, which has a belongsToMany books relationship, which is using a Checkout pivot model, and is also getting the borrowed date column from the checkouts table. I've also set up the inverse belongsToMany users relationship on the Book model.

and is also getting the borrowed_date column from the checkouts table. I've also set up the inverse belongsToMany users relationship on the Book model. And finally, if we look at the Checkout pivot model, we can see that we have a borrowed_date cast set up, plus a user belongsTo relationship. And you might be wondering, why do we need this belongsTo relationship? Well, it's because I'm using a dynamic relationship to get the last checkout for each book. This is going to be helpful information for us to verify that our ordering is working properly. You don't need to be worried about the details of the dynamic relationship for this lesson. However, if you are interested in learning more about it, be sure to see the creating dynamic relationships using subqueries lesson. OK, so our page is currently ordering the books by their name. However, in this lesson, we're going to look at how to order the books by the date it was last borrowed, as well as the name of the user who last borrowed the book.

OK, so our page is currently ordering the books by their name. However, in this lesson, we're going to look at how to order the books by the date it was last borrowed, as well as the name of the User who last borrowed the book. Let's start with the last borrowed date, because that's actually the easier thing to order by. And that's because the borrowed date exists on our checkouts pivot table. And if you think about it, checkouts are essentially a hasMany relationship to books. A Book has many checkouts. We just happen to be using the checkouts as our pivot table for our belongsToMany relationship between our books and users. Meaning if we want to sort by a column in the checkouts table, we can use the exact same techniques as we covered in the ordering by hasMany relationships lesson. I'm going to move through this piece pretty quickly, but be sure to see that lesson for more information on ordering by hasMany relationships. Let's start by going to the BooksController and removing the existing name order by.

I'm going to move through this piece pretty quickly, but be sure to see that lesson for more information on ordering by hasMany relationships. Let's start by going to the BooksController and removing the existing name orderBy. So the first approach we can use to order by the last borrowed date is using a join. We'll select all the books columns and then we'll join in the checkouts table where the checkouts.book_id is equal to the books.id. Then we'll group the results by the book_id. And then we'll do a raw orderBy where we get the latest borrowed date from the checkouts table and we'll sort that descending. And now if we hit refresh in the browser, we can see that it's working. If we look at our last checkouts column, we can see that this book was checked out three days ago. This one one week ago, this one two weeks ago and so on. Beautiful.

Can I still do this? Absolutely. The orderBy methods in Laravel also allow you to provide a closure where you can manually write a sub query. Let's try that. orderBy descending and then we'll pass it a closure, which takes an instance of the query builder. And then we're going to select the borrowed_date from the checkouts table where the checkouts.book_id is equal to the books.id of the parent query. And we'll order that by the latest borrowed_date. And then we'll get the first record.

And we'll order that by the latest borrowed date. And then we'll get the first record. And if we hit refresh in the browser, we can see that it's still working. OK, so to this point, we haven't really been ordering by a belongsToMany relationship column. As I already noted, checkouts are really a hasMany relationship to our books. So let's make things a little more interesting and try to sort by the name of the User who last checked out the book instead. Again, we can use a sub query to accomplish this. Order by from the users table and we'll select the name. However, this time we need to include a join in our sub query to pull in the checkouts table. And we're doing this because our checkouts table is what connects our books with our users.

However, this time we need to include a join in our sub query to pull in the checkouts table. And we're doing this because our checkouts table is what connects our books with our users. So we'll join in the checkouts table where the checkouts.user_id equals the users.id. Next, we'll check to see if the checkouts.book_id equals the books.id of the parent query. From there, we'll order the checkouts by the borrowed_date column to get the latest one. And finally, we'll take the first record. And now if we hit refresh in the browser, we can see that this is working. We're ordering our books by the name of the user who last checked out that book. How cool is that? Now, just a quick warning before we wrap up this lesson.

How cool is that? Now, just a quick warning before we wrap up this lesson. While this technique is great and definitely useful in certain situations, if you're dealing with a ton of data, this approach will likely not be super fast. So if you run into situations where this is causing performance issues, that's probably a good time to introduce some caching. Let me give you a quick example of what I mean by going to our books migration. As a first step of denormalization, I'd probably start by adding a last_checkout_id foreign key to this table. Then anytime a book is checked out, I'd update this foreign ID.

Then anytime a Book is checked out, I'd update this foreign ID.