The N+1 Problem in Laravel

Ep#26@Laracasts: Clockwork, and the N+1 Problem

This post is a part of the Week X of 100DaysOfCode Laravel Challenge series.

On the blog listing page of our Blog Project, we have N blog posts and behind the scenes Laravel runs N+1 queries to fetch those posts and their categories. The first query fetches posts and the rest fetch the category for each post.

That is because in our posts.blade.php we loop through all of the posts and then for each post we access its category as:

<p>
<a href="/categories/{{ $post->category->slug }}">
{{ $post->category->title }}
</a>
</p>

but in our route definition we have pulled the posts only and passed them to the view as:

Route::get('/', function () {
    return view('posts', [
        'posts' => Post::all()
    ]);
});

Laravel by design, lazy load posts. It says why should I load relations if they are not referenced anywhere. That makes sense but then we can fall into the N+1 trap.

The solution is to change the way how we load our posts. Instead of Lazyloading, we Eager load our posts. Currently, we use Post::all() to fetch all posts. We change it to Post::with("category")

Route::get('/', function () {    
    return view('posts', [
        'posts' => Post::with("category")->get();
    ]);
});

Now only two queries will be run, one for fetching all the posts, and the second for fetching the relations for those posts.

To debug the N+1 problem, you can use the Laravel Log Facade or the logger() helper function to log the database queries using the DB facade. In your route definition for the blog listing page, log the queries before and after fixing the N+1 problem.

Route::get('/', function () {
    Illuminate\Support\Facades\DB::listen(function($query) {
        logger($query->sql, $query->bindings);
    });

    return view('posts', [
        'posts' => Post::all()
    ]);
});

Another tool to easily debug this issue is to install the Clockwork Laravel package and the companion Chrome/FireFox extension. When you install the composer package and the browser extention, now visit your blog page open the dev tools to see the new tab Clockwork.

To see all the changes made to the Blog project in this episode, visit the commit on Github Ep#26: Clockwork, and the N+1 Problem
.