In this lesson, we're going to look at how we can get one record from a HasMany relationship in the most efficient way. Now, imagine you have a User's page like this in your app, which lists the User's name and email address. However, a request comes in to add a third column to display their last login date as well. This seemingly simple request actually presents some interesting complexity. Let's jump into the code. In this app, we're tracking logins in a separate table, which we can see in this logins table migration. Each login record includes the user_id, their ip_address, and the date that they logged in. And if we look at our User model, we can see that we have a HasMany logins relationship already set up. Pretty straightforward stuff. Okay, let's start by adding the new last login date column to our User's Blade template.

Pretty straightforward stuff. Okay, let's start by adding the new last login date column to our users Blade template. We'll add a new table heading, and now let's add a new column for the last login date. So, how exactly do we get the user's last login date? One solution is to simply run a query for each User to get this information. User, logins, and then we order them by the latest. We get the first one, and then we grab the created_at value from that record, and do diffForHumans to display it nicely. And if we refresh the page in the browser, sure enough, our new column has been added. However, if we look at our queries tab in the Laravel debug bar, we'll notice a little problem. We're now running 17 database queries, and if we look at those queries, we can see that 15 of them are exactly the same.

However, if we look at our queries tab in the Laravel debug bar, we'll notice a little problem. We're now running 17 database queries, and if we look at those queries, we can see that 15 of them are exactly the same. For every User we display, we're now running an additional query to get their last login. We've just created an n plus 1 issue, meaning if our page displays 50 Users, we're now executing a total of 52 queries. Let's see if we can do better. This time, let's try eager loading all the login records instead. Let's go to our UsersController and add a with('logins') eager load to our query. Next, let's update our blade view slightly. Since we're now working with a Collection, we'll sort the records using the sortByDescending method instead. Let's go back to the browser to see if this has helped.

Since we're now working with a collection, we'll sort the records using the sortByDesc method instead. Let's go back to the browser to see if this has helped. Again, we're currently at 17 database queries, and if we hit refresh, we're now down to only three. Two for the users, and one for the corresponding login records. Success! Well, not exactly. Yes, we did fix our n plus 1 issue. However, we've actually introduced a much bigger problem. Let's look at our models tab. Each User in this demo app has been seeded with 500 login records, and with only 15 users on this page, that adds up to 7,500 login records being loaded from the database.

Each user in this demo app has been seeded with 500 login records, and with only 15 users on this page, that adds up to 7,500 login records being loaded from the database. Further, if we look at our memory usage, we can see that we're using over 13 megabytes of memory, which is almost 10 megabytes more than where we were before. And loading this many records will not only consume memory, it will also require additional computation, since each record must be initialized and hydrated as an Eloquent model. And honestly, this is a pretty conservative example. You could easily run into similar situations that result in millions of records being loaded. I find this example interesting, since quite often when we see n + 1 issues in our apps, we'll reach for eager loading to solve the problem.

I find this example interesting, since quite often when we see n+1 issues in our apps, we'll reach for eager loading to solve the problem. However, in this situation, eager loading all this data was actually a much worse solution than simply running those 15 extra database queries. Okay, at this point, you might be thinking, I guess I have no choice but to simply cache the last_login on the users table. For example, if we go to our users migration, maybe we could add a last_login_id foreign key to this table. And whenever a User logs in, we'll create a new login record, and then simply update this new last_login_id column on the users table. And from there, we can just use a normal lastLogin relationship on our User model. And this would be a totally valid solution.

And from there, we can just use a normal last login relationship on our User model. And this would be a totally valid solution. There are absolutely situations where denormalization like this is appropriate. However, quite often caching isn't this simple. In fact, caching can get ridiculously complicated, ridiculously fast. So before we throw in the towel and reach for caching, let's take a look at another approach I like to use to solve these kinds of problems. And that's using subqueries. Subqueries allow us to add extra columns to our query that are computed from another table, meaning we can run a subquery within our users query to automatically get the user's last login date.

Subqueries allow us to add extra columns to our query that are computed from another table, meaning we can run a subquery within our users query to automatically get the user's last login date. Let's try that. In our UsersController, let's remove the logins eager load. Now let's add a subquery instead. What you may not realize is that Laravel actually has built in support for selecting data from a subquery. You can do that using the addSelect method. And the first thing we need to provide is the name of our new computed column. We'll call it last_login_at. From there, we can provide a query that's going to be executed as a subquery when our user query runs.

We'll call it lastLoginAt. From there, we can provide a query that's going to be executed as a subquery when our User query runs. Login, and we'll make sure we import that right away. Select, and we're going to grab the createdAt column from the logins table, where the column userId equals the user's ID on the parent User query. We're going to order the logins to get the latest ones first, and then we're going to take just one record. And the reason why we're going to take one record is because a subquery can only return one column back. Now let's update our view to display this new lastLoginAt attribute. User lastLoginAt.

Now let's update our view to display this new lastLoginAt attribute. User lastLoginAt. Much simpler. Note, however, we no longer have our diffForHumans date formatting here. And that's because we're no longer working with a Carbon date instance. We'll come back to this in a moment. Okay, let's go to the browser and give this a try. Remember, we're currently at over seven and a half thousand models being loaded from the database, and our memory usage is over 13 megabytes. If we hit refresh, we can see that our lastLoginDate is still being displayed, albeit unformatted.

and our memory usage is over 13 megabytes. If we hit refresh, we can see that our last login date is still being displayed, albeit unformatted. However, if we look at our Laravel debug bar now, we can see that we're down to only 15 User models being loaded from the database. We're not loading any Login models anymore. And our memory usage is down to just over four megabytes. And if we look at our queries tab, we can see that we're only running two database queries. This is a huge improvement. And this was all possible because we used a subquery. Now let's take a closer look at the query we just wrote.

And this was all possible because we used a subquery. Now let's take a closer look at the query we just wrote. Let's paste it in a table plus and format it so it's easy to read. And then let's just run it. And now if we look at the results here and we scroll right to the end, we can see that we have a lastLoginAt column in our results. It's as if this column existed right on our users table. However, it's being computed on the fly in our subquery. We can see the subquery up here in between these brackets. We're selecting the createdAt date from the logins table,

We can see the subquery up here in between these brackets. We're selecting the created_at date from the logins table, where the user_id on the logins table equals the user's id of the parent query. And then we're ordering it by the created_at date to get the latest one. And then we're sending a limit of one. Then we're taking whatever is returned from that subquery and we're assigning it to the column name last_login_at. And note here, if we were to remove the limit one and rerun this query, we'll get an error that the subquery returns more than one row, which is why we need to have that limit one in place.

we'll get an error that the subquery returns more than one row, which is why we need to have that LIMIT 1 in place. So at this point, you might be wondering, have we not simply moved our multiple database query problem from layer belt to the database layer? And the answer is yes and no. Yes, in that the database is technically still responsible for running all of these queries. One query to get the User and then individual subqueries to get the last login date for each User.

One query to get the User and then individual subqueries to get the last login date for each User. However, databases are highly optimized for performing tasks like this. They are much better suited for this type of work than Laravel or php is. And not only that, from Laravel's perspective, we're now running only one database query to get this data, which means only one round trip from our web server to our database server. The end result is much, much better performance. OK, let's fix our date formatting now. Let's go back to our Blade view and add the diffForHumans method.

OK, let's fix our date formatting now. Let's go back to our Blade view and add the diffForHumans method back to our last login date. However, if we hit refresh in the browser, we'll get an error. Call to member function diffForHumans on string. And that makes sense since our last login date is not a Carbon date instance, but rather a string. Previously, this worked because our Login model was automatically casting the loginCreatedAt attribute to a Carbon instance. However, now that we're getting our last login date directly in our users query,

casting the lastLoginAt attribute to a Carbon instance. However, now that we're getting our lastLoginAt date directly in our users query, this date casting isn't happening. Fortunately, Laravel provides an elegant solution for this called query time casting. Let's update our query. We'll add a withCast call to our query, which automatically converts our lastLoginAt column to a date time Carbon instance. This is essentially the exact same casting that Laravel provides for models.

to a date time Carbon instance. This is essentially the exact same casting that Laravel provides for models. And now if we hit refresh in the browser, we can see that our date is nicely formatted again. Beautiful. The last thing I want to do before we wrap up this lesson is to move this sub query logic to a scope on the User model. Let's start by cutting this from our UsersController and replacing it with a lastLoginAt scope call. Now let's go to our User model and create this new scope.

and replacing it with a lastLoginAt scope call. Now let's go to our User model and create this new scope. public function scopeLastLoginAt, and that's going to take an instance of the QueryBuilder. And then we just paste in the QueryBuilder logic from our UsersController. And if we hit refresh in the browser, we can see that everything is still working properly. I really like moving QueryBuilder logic like this from my controllers to my models. Not only does this make the queries in my controller more expressive,

from my controllers to my models. Not only does this make the queries in my controller more expressive, it also makes it much easier to use this query builder logic elsewhere in my application. In the next lesson, we'll take this technique one step further by using it to create dynamic relationships.