Avoid N+1 problem using Eloquent ORM
As you might have used Eloquent in all of your Laravel projects you feel the comfort the Eloquent brings to your life.
Eloquent is saving tons of time from developers, but it can run into issues if you don’t take care of the queries. One of the problems that can happen is known as the N+1 problem.
What is the N+1 problem?
Let’s explain it in practice:
Suppose you have two models called User and Post.
As you can see a user can have many posts and a post is written by a user (author).
Now we’re going to have a list of all posts with their authors’ names next to them.
- We should have a route to see the page:
2. In the controller we have to retrieve all of the posts from the database:
3. Finally, we will show the posts with their authors:
And this is the result:
It seems like everything is working perfectly fine :)
No, there’s a problem here.
As you see there are too many SQL queries here. This is called the N+1 problem.
What is the solution for N+1 problem?
Eloquent has the eager loading functionality which can be helpful in this scenario.
So let’s see how can we implement eager loading:
In the PostController.php file we will get the posts adding with('author') to the query. The author is actually the name of the relation method used in the Post model.
So using this methodology we can get all of the associated users in a single query instead of having N number of queries (N = number of posts being retrieved from DB).
So this is the result:
The query time got 3.29 ms from 32.61 ms which is insane.😎
In a nutshell for solving the N+1 problem you should use eager loading.
