Solving N+1 Query without creating Memory issue in Laravel
One of common problem of programmers when they create an app with ORM is N+1 query in their application. N+1 query problem is an inefficient way of database querying when our application produces a query every object calling. The problem mostly occurs when we provide list of data which we fetch from database without implementing eager loading technique. Fortunately, Laravel with its Eloquent has provide us eager loading tool to enable us to simply put which table which will be eager loaded.
Let’s have simple example about how to use eager loading in Laravel. Let’s say, we have a simple web app that want to show list of first articles title from the app users. Then we may have simple model relationship like this:
and there will be simple action to fetch and pass all user data like this:
with simple test.blade.php view to show the users list with their respective first article title:
And when our test page is opened, it will be displayed like this:
I use debugbar(https://github.com/barryvdh/laravel-debugbar) to show how our test page perform. There are 11 queries to show 10 user’s first article titles. One query is for loading all users data, and the rest is for loading user’s respective article data. It can be seen that 10 users produce 10 different article queries. This condition is called N+1 query problem.
Solving N+1 query problem with eager load
You may feel that it has no problem with your app performance at all. But, what if we have to show more than 10 data? And often, we also have to deal with more complex logic consisting more than one N+1 queries in one page. That condition can produce more than 11 queries, or even it can produce exponentially-growth queries. So, how we solve it? There is one common answer to it:
Eager Load
Eager load is the process whereby a query for one type of entity also loads related entities as part of the query[1]. In Laravel, we can eager load relevant model data by using with(..) method. In our example, we should adjust our code with following changes:
And, finally we can reduce our queries to be just 2 queries:
We can also create a hasOne relationship with relevant query to fetch first article of an user:
Then we can eager load it when we load our users:
Here is the result:
Finally, we can reduce our queries and solve N+1 query problem. But, have we done well improving our page performance? It could be no! It is true that we have reduce our queries and solving the N+1 query problem, but actually we introduce new harmful problem. As you can see, we do reduce the queries from 11 to 2, but we also do increase the loaded models from 20 to 10010. It means: to show 10 user’s name and 10 user’s first article title, we loaded 10010 Eloquent model instances to the memory. If you don’t have limited memory resource, it won’t be a problem. But if you do, you can bring your app down.
Dynamic relationships eager load
There should be two goals when we building a web app:
- We keep our app’s database queries to a minimum
- We keep our app’s memory usage to a minimum
In our case above, we are failed to keep our memory usage to a minimum while we are succeed to reduce our database queries to a minimum. In many cases developers are also good at maintaining the first goal and failed to keep the second goal to success. In this case, we can use dynamic relationship eager load via subqueries[2] technique to reach both goals to success.
To implement dynamic relationship, we directly use relationship table id instead of its foreign key id. We should also use subquery on the reference table to fetch its relevant id. The subquery will be placed on select, with querying based on filtered reference table data.
Here is an example to fetch users data with its relevant first article id within subquery:
We can get the query by adding select clause with subquery in our query builder. By using Eloquent, it can be written as follows:
Code above will produce same query with previous query I have mentioned before. Afterwards, we can utilize first_article_id with belongs_to relationship to fetch user’s first articles. To make our code clearer, we can also utilize Eloquent query scope to pack our query and eager load for the first article. Thus, we should add code below to our User model:
And finally, let’s modify our action and view to know the result. We should use scope in our action to eager load the first article. And then we can directly access first_article attribute in the view.
Here is the result:
Now, our page just has 2 queries and 20 models loaded. And we have achieve our goals optimizing database queries and memory usage to a minimum.
Dynamic relationship lazy load
Our dynamic relationship will not work well automatically. We need to load the subquery first to enable the relationship. So, what if we want to lazy load the first article in one place and have dynamic eager load of first article in other place?
We need a little bit hack to our code. by adding first article accessor:
Actually, we do not really lazy load the dynamic relationship. We just assign the result of query to fetch the first article of an user. Yet, it should work well for both access of first_article without eager loading or eager load the first article first.
Dynamic relationship at Laravel 5.X
Unfortunately, our solution above is just applied to Laravel 6 and above. There is different implementation of addSelect between Laravel 6 and its previous version. So, we need to modify our code a little bit for Laravel version of 5.X and below. We will use selectSub to produce the subquery and put additional code to add selected attribute to the query:
[1]https://www.tutorialspoint.com/entity_framework/entity_framework_eager_loading.htm
[2]https://reinink.ca/articles/dynamic-relationships-in-laravel-using-subqueries
