Laravel and N + 1 Problem | How To Fix N + 1 Problem

Hello Artisan 

In this brand new tutorial i will share about Laravel N+1 query problem. Do you know how to deal with 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.

 

laravel-eager-loading-example

What is n + 1

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 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 load the user, Laravel will still let us access, 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 table has an user_id column, by which the user is queried. If we have 100 posts, this will execute 1 query to get all the posts and 100 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 user and this is our Post model.

app/Post.php

namespace App;

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 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\Post::with(['user', 'publisher'])->get();

Hope this Laravel N+1 problem tutorial will help you. In this example tutorial we will try to know what is N+1 problem and how we can solve this problem with Laravel eager loaded.