32
loading...
Boost your queries in Laravel
There are lots situations which you may think that your query is super duper optimize and your project works like a Rocket!
But when we monitor your queries which has been executed, you may be shocked and say : "Oh man, what's going on ? 31 query for just getting a bunch of articles ???? 🤐"
But when we monitor your queries which has been executed, you may be shocked and say : "Oh man, what's going on ? 31 query for just getting a bunch of articles ???? 🤐"
+"It's not possible"
-It is possible :))
-It is possible :))
First of all let me tell you about N + 1 problem, a big problem which you may not be noticed about.
What is N + 1 Problem?
What is N + 1 Problem?
Imagine you have about 30 articles and each article has a author and you want to show these articles with their author in a table.
$articles = Article::all();
return view('articles', compact('articles'));And in your Blade :
@foreach($articles as $article)
<li>{{ $article->title }}</li>
<li>{{ $article->author->name }}</li>
@endforeachBut if we check the queries which has been executed, we will see that over 31 queries have been executed !
+Damn, whyyyyyyy?
-Let me tell you why.
-Let me tell you why.
When you are getting articles just one query will be executed but where are these other 30 queries ?
If you remove the
<li>{{ $article->author->name }}</li> you will back to the safe zone with just 1 executed query.This one line per each article's author run a query to get the data of author... (Oh mannnn, I got it.)
Let's check the queries :
The first query is this:
The first query is this:
SELECT * FROM `articles`And the other 30 queries are like this:
SELECT * FROM `authors` WHERE `id` = 1 LIMIT 1
SELECT * FROM `authors` WHERE `id` = 2 LIMIT 1
SELECT * FROM `authors` WHERE `id` = 3 LIMIT 1
--.....
SELECT * FROM `authors` WHERE `id` = 30 LIMIT 1Per each article's author one query will be executed.
Now
with method will be appears and save your project's life.To solve the problem you can use the
with method in your controller and write this code:$articles = Article::with('user')->get();
return view('articles', compact('articles'));That's it, nothing else needs to do :)
With is solution of the N + 1 problem and it solves that with eager loading.
So what is eager loading and how does it work?
First lets check the queries, we had 31 queries on top and now if we check the queries we can see that just 2 queries has been executed!
How just 2 queries?
When we are using
eager loading instead of lazy load of Laravel to get authors data just one query will be executed.SELECT * FROM `authors` WHERE `id` in (1, 2, 3, ..., 30)And literally one query is super faster than 30 query :))
Also you can get a bunch of relationship with with():
$articles = Article::with(['author', 'publisher'])->get();And you can get a nested relation:
$articles = Article::with('author.profile')->get();
$articles = Article::with('author.profile')->get();
Wait, it isn't done, you can also filter the data which you want to return with QueryBuilder
$articles = Article::with(['author.addresses' => function($query){
$query->where('active', true)->latest();
}]);32