Next up, we have some useful subquery additions to Laravel that were contributed by Jonathan Reining. So let's take a look around. I'm going to boot up php artisan tinker and then switch to edit mode, so we have a little more breathing room. Now, for an example, at Laracast, we record every video you watch. So to demonstrate that, I have a little tutorial database set up here. We have users, and then we have a watching table. Each record shows the user_id and then a video that they started watching. So, yeah, maybe I want to grab not just the user, so let's find somebody, 41, okay.

Each record shows the userId and then a video that they started watching. So, yeah, maybe I want to grab not just the User, so let's find somebody, 41, okay. I want to grab not just this User, if I run it, yeah, I grab it, Khalid, here, but I also want to track the id of the most recently watched lesson. Okay, we can use a subquery for this, and there's many ways we could write it. We could do multiple queries, we could do a raw statement, or we can use this new PR. Let's see how. Once again, I'm going to boot up edit mode, and once again, we'll find the User, but first, I'm going to add a subquery. So we'll do add select, and what do we want the column name to be?

I'm going to add a subquery. So we'll do addSelect, and what do we want the column name to be? How about last_watched_video? So now here, we could either reference a different Eloquent model, so for example, if you had a Watching model, you could perform your query like that, but I don't have that in this case, so we'll do a closure and reference the query builder. Let's say query, and we're going to select what? Well, I want to grab this lesson_id here. So let's select the lesson_id from the watching table, but we have to connect this record with the User in question, right?

So let's select the lesson_id from the watching table, but we have to connect this record with the User in question, right? So we're not going to use where, that would compare values for each column, instead, we will compare at the columns themselves. So where the user_id for this table matches up with the id from the users table, right? So where user_id matches up with the users table in the id column there. Finally, we only want one, and then let's grab the most recent one, and that should be our query. So now, if we give this a run, let's close this out, and let's find our users. So let's see, let's just find someone.

So now, if we give this a run, let's close this out, and let's find our users. So let's see, let's just find someone. How about the User with an ID of 41, and the User with an ID of 50, and let's see what we get. All right, so now for each User, yes, I have their generic data, but we also have this new column, the ID of their most recently watched video. Now if you're curious what's happening there, let's do this, I have a little snippet here to listen for any database queries, and then I will rerun this query. Okay, so notice we got a subquery here. We're selecting all users, but also the result of this subquery.

Okay, so notice we got a subquery here. We're selecting all Users, but also the result of this subquery. And remember, for subqueries, you can add them in place of a column value, you can add them in place of an orderBy, you can even add them in place of the from table name. So all over the place. Anyways, we're selecting the lesson_id from the watching table where the user_id matches up, so we're connecting that watching table back to the users table, we're ordering it, and then we're just grabbing the one value. And that's how we populate this. Let's review one more example.

And that's how we populate this. Let's review one more example. Now like I said, subqueries can be used in a number of places. This time, let's grab my users, but order them according to those who have most recently watched videos. So somebody who hasn't watched in a year will be at the bottom, somebody who watched a minute ago will be at the top. Okay, so once again, let's go back to edit mode. So this time, yes, I still ultimately want to find those users, but we do want to order them according to who has most recently watched content.

So this time, yes, I still ultimately want to find those users, but we do want to order them according to who has most recently watched content. So we'll do the query builder this time. And let's work it out. What are we ordering by? We are ordering by the created_at timestamp for the most recently watched video for the user, right? So we're going to say select created_at from the watching table. We want to grab the most recent one, so we'll say the latest one first. And then finally, we have to connect it back to the users table.

We want to grab the most recent one, so we'll say the latest one first. And then finally, we have to connect it back to the users table. So where the user_id column on that watching table matches up with the id column on the users table. And remember, here, we're writing SQL. So even though the Eloquent model is named User, the actual table is called users. So I have to reference it properly. Finally, I only want to grab the most recent one, and that should be our orderBy query. So now, once again, we're going to work with what user_id 41 and 50. And if we give it a run, let's see what we get.

So now, once again, we're going to work with what user ID 41 and 50. And if we give it a run, let's see what we get. So it looks like Lolita is the most recently active person. Let's verify it. For the user with an ID of 41, their most recent watched video was 2017. It's a little bit old. I need to get to work. But anyways, October 10th. And then for the user with an ID of 50, you can see September 21st of that year. So if I switch back, there's ID.

And then for the User with an ID of 50, you can see September 21st of that year. So if I switch back, there's ID. Oh, you know what? I actually got it in reverse. The last item would be the person who has most recently watched content. So if we run it one more time, let's swap it out to descending order. So now, this is what we want. We have a collection of users ordered according to who has most recently watched content on the site. And if you want to dig into the query, you can review it there.