Eloquent optimization: displaying only one record of a one to many relationship

Sometimes we need to display data for only one record in our one-to-many relationships. For example:

• Displaying the title of the last video each user posted
• Showing the most sold product in each category
• Showing the last comment on every blog post
• etc.

In this article, I want to go over the different ways we can do this and figure out the most efficient way in both terms of time and memory

The naive approach

In this article, I'm going to use the example of a platform where users can publish videos, and I want to show the title of the last video for each user.

One way to do this is retrieving the users in the controller and then in our blade template order the videos by the created_at date, grab the first and then show the title.

    return view('users', ['users' => User::get()]);

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->videos->sortByDesc('created_at')->first()->title }}</td>
    @endforeach

In this database, I seeded 30 users with 100 videos each to figure out the performance of the queries.

Using Laravel debugbar, I can see that we are making 32 queries to the database and loading 330 models into memory!

This is very inefficient for what we are doing. Let's fix this query.

Solving the n + 1 issue

If you are not familiar with n + 1 issues, what I'm referring to is that we are making one query per user, meaning that if we are showing 30 users, we would make 31 queries, if we are showing 100 users we will make 101 queries.

This is the issue we are currently facing with our application. One way that Laravel offers to fix this is by using eager loading. And we can do this by adding a with('videos') call to our eloquent query.

    return view('users', ['users' => User::with('videos')->get()]);

This simple change solves our n + 1 issues, If I look at the Laravel debugbar output now, I see that the queries we reduced to only 2!

This is a big improvement, but we are still loading 330 models into memory, meaning that we are retrieving all the videos for every user from the database. Let's fix that.

Retrieving only the last video

Fortunately, Laravel offers a simple way to create a relationship for the latest video, the relationship will look something like this in our User model:

    public function latestVideo()
    {
        return $this->hasOne(Video::class)->latestOfMany();
    }

This way we can retrieve only the last video from a user using $user->latestVideo, and since it's a relationship we can eager load it as well. So let's make use of our new relationship in our controller.

    return view('welcome', ['users' => User::with('latestVideo')->get()]);

And In our blade template:

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->latestVideo->title }}</td>
    @endforeach

Now If I check Laravel debugbar, we are only making 2 queries since we are using eager loading, and we are loading 60 models into memory, 30 users, and 30 videos.

But I still think we can make it a little better, what if we didn't have to load any video model and instead only fetched the title?

Fetching only the last video title

To fetch only the title of the latest video, we can use the addSelect method. Using this method, we can add a subselect that will be added as a column to our query. Here is what I came up with:

    $users = User::addSelect(['latest_video_title' => Video::query()
        ->select('title')
        ->whereColumn('user_id', '=', 'users.id')
        ->orderByDesc('created_at')
        ->limit(1) 
    ])->get();
    
    return view('users', ['users' => $users]);

In this query we select the title, we add a where to make sure the video belongs to the user order the results by the created_at column in descending order and then limit the results to only 1 to grab the first one.

This is not as easy to read as the previous method, so let's create a scope in our user model instead of doing this work in the controller:

    public function scopeWithLastestVideoTitle($query)
    {
        $query->addSelect(['lastest_video_title' => Video::query()
            ->select('title')
            ->whereColumn('user_id', '=', 'users.id')
            ->orderByDesc('created_at')->limit(1) 
        ]);
    }

And in our controller, we can just call the scope:

    return view('welcome', ['users' => User::withLastestVideoTitle()->get()]);

Then we need to change our blade template to use this new generated column:

    @foreach($user as $users)
        <!-- Table html -->
        <td>{{ $user-> }}</td>
        <td>{{ $user->lastest_video_title }}</td>
    @endforeach

Using Laravel debugbar for the last time, I can see that we got our page from making 32 queries and loading 330 models to making 2 queries and loading only 30 models into memory. Pretty neat!

If you liked this article, follow me on Twitter, I post tips and other Laravel content there.