DEV Community

Arif Iqbal
Arif Iqbal

Posted on • Updated on

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.

N+1 problem in Laravel

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>
Enter fullscreen mode Exit fullscreen mode

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()
    ]);
});
Enter fullscreen mode Exit fullscreen mode

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();
    ]);
});
Enter fullscreen mode Exit fullscreen mode

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

solution for n+1 problem in laravel

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()
    ]);
});
Enter fullscreen mode Exit fullscreen mode

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.

Clockwork Laravel debugging

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
.

Discussion (0)