Hello Artisans,

How are you doing? In this tutorial, I will show you about Laravel N+1 query problem. Do you know how to deal with a Laravel project that has numerous N + 1 query problems?

If you don’t know about eager loading in Laravel then you are a right place. In this tutorial i will discuss about laravel eager loading and we will solve N+1 query problem.

What is the n + 1 Problem?

When accessing Eloquent relationships as properties, the relationship data is “lazy loaded”. Laravel provides eloquent relationships . They allow us to access related models from another model.

But if we’re not careful, it’s easy to introduce a n + 1 problem – which essentially means that we execute an additional query every time we access the relationship that was not eager loaded.

To illustrate the N + 1 query problem, consider a Post model that is related to User. Let’s take a look at this example that we have multiple $posts and every post has an user, that we can access with $post->user.

If we did not eager to load the user, Laravel will still let us access it, but it will execute a query to get it. Now imagine we didn’t eager load it and we’re doing something like this

$posts = Post::all();

foreach ($posts as $post) {
    dd($post->user);
}

Do you know what is happening in that query? Every time the $post->user is called, Laravel will execute an extra query to load the author. Something like this for each post:

select * from users where id = ?

This assumes the posts the table has an user_id column, by which the user is queried. If we have 10 posts, this will execute 1 query to get all the posts and 10 additional queries to get their related users.  This is definitely bad practice. 

Laravel Solve this N+1 problem or eager loading problem with  (using with(‘relationship’)):  Let’s think this is our relationship with the user and this is our Post model.

app/Models/Post.php

namespace App/Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    /**
     * Get the user that owns the phone.
     */
    public function user()
    {
        return $this->belongsTo('App\User');
    }
}

Now see our eager loader query to solve N+1 problem in laravel.

$posts = Post::with('user')->all();

foreach ($posts as $post) {
    dd($post->user);
}

In this example, the first query will fetch all the posts, and the 2nd query will fetch all their related users simultaneously. Now our N+1 problem is solved by eager loaded query.

Thankfully, we can use eager loading to reduce this operation to just 2 queries. When querying, you may specify which relationships should be eager loaded using the with method. Something like this

select * from posts

select * from users where id in (1, 2, 3, 4, 5, ...)

Eager Loading Multiple Relationships

Sometimes you may need to eager load several different relationships in a single operation. To do it just see below example.

$posts= App\Models\Post::with(['user', 'publisher'])->get();

Hope this Laravel N+1 problem tutorial will help you.