In this lesson, we're going to look at how we can order records by the value of a HasMany relationship column. Consider this page which lists all the Users in this app with their name, email address, and the date and IP address of their last login. Since the logins are a HasMany relationship on the User model, we're using a dynamic relationship to get the last login record. And if you haven't yet, be sure to see the Creating Dynamic Relationships Using Subqueries lesson to learn how to do this. Now, currently this page is ordering the Users by their name. However, what if we wanted to order them by their last login date? How would we do that? This is a common enough thing to want to do, but it's actually surprisingly tricky. There's two approaches we can use. The first is to use a join, and the second is to use a subquery. Let's try the join approach first. In our UsersController, we'll update our query to remove the existing name orderBy.

Let's try the join approach first. In our UsersController, we'll update our query to remove the existing name orderBy. Next, let's join in the logins table where the loginUserId equals the userId. Next, we'll add a select statement to only get the columns from the users table. We do this since by default, Laravel will get all the columns from both the users table and the logins table. Next, we'll order these records descending by the createdAt column on the logins table. And now if we hit refresh in the browser, we can see that it's working. We're getting our users ordered by the date and time of their last login. Except, we have a little problem. We're now getting duplicate records for each user. For example, if we look at Keyshawn here, we can see that his record is duplicated down here.

Except, we have a little problem. We're now getting duplicate records for each User. For example, if we look at Keyshawn here, we can see that his record is duplicated down here. And the same goes for Claudie. In fact, we're getting 500 duplicates for each User since I've seated each User with 500 logins. We're now getting a total of 30,000 User records instead of only 60 Users, the amount of Users in our database. Okay, let's fix this. Let's go back to our query and add a GROUP BY on the users.id column. This will ensure that we only get one record back for each User. However, if we hit refresh in the browser, we're now getting an error. Expression number one of ORDER BY clause is not in GROUP BY clause and contains non-aggregated column logins.created_at, which is not functionally dependent on columns in GROUP BY clause.

Expression number one of order by clause is not in group by clause and contains non-aggregated column logins.created_at, which is not functionally dependent on columns in group by clause. What on earth does that mean? Let's take a closer look at our query. Because of our logins join, we're getting 500 records for each User, a row for every login record they have. And of course, we only want one record per User, which is why we're grouping them by the user_id. However, we're then telling MySQL to order the grouped rows by the created_at column on the logins table. But if there are 500 rows per User, that means we have 500 different login.created_at values. How does MySQL know which one of the 500 created_at values to order by? Well, it doesn't, which is why we're getting this error.

How does MySQL know which one of the 500 created_at values to order by? Well, it doesn't, which is why we're getting this error. Let's update our query to tell MySQL which value we want to sort by. To do this, we'll use a raw order by and we'll use the max aggregate function to specify which created_at value we want to order by. And finally, we'll sort descending just like before. And now if we hit refresh in the browser, we're back in business. Nice. And if we look at the Laravel debug bar, we can see that this query is running in about 42 milliseconds. Okay, now let's take a look at the subquery approach. This time, instead of using a join to pull in the logins table data, we're going to use a subquery to get this information instead.

Okay, now let's take a look at the subquery approach. This time, instead of using a join to pull in the logins table data, we're going to use a subquery to get this information instead. We'll use the orderBy method in descending order and then we'll pass in a subquery. Login, and we'll make sure we import that right away. Select the created_at column where the logins user_id column equals the user_id column of the parent query. We'll order it by the latest and then we'll take the first record. And now if we hit refresh in the browser, we can see that it's working in the exact same way. And if we look at our queries in the Laravel debug bar again, we can see that this approach is actually running slightly faster than the join approach at 25 milliseconds. What's really great about this approach is that we can run the subquery right in our orderBy statement, which means we don't run into any of the issues that we ran into with a join approach.

What's really great about this approach is that we can run the subquery right in our orderBy statement, which means we don't run into any of the issues that we ran into with a join approach, such as having to remove the extra logins columns or deal with the duplicate rows. This also makes it super easy to tuck it away in a User model scope. Let's do that for a second before we wrap up this lesson. Let's copy our orderBy query code and replace it with an orderByLastLogin scope call. Now let's jump into the User model and let's create the new scope. public function scopeOrderByLastLogin, which takes an instance of the queryBuilder. And then we can just paste in our code. And now let's just hit refresh in the browser to make sure it still works.

And then we can just paste in our code. And now let's just hit refresh in the browser to make sure it still works. And sure enough, it does.