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.