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 ???? 🤐"

+"It's not possible"
-It is possible :))

N + 1 Problem

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?

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>
@endforeach

But 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.

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:

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 1

Per each article's author one query will be executed.

With() is your saver

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();

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();
}]);

Comments

[FILIPE PETTER]

Very good! Now I gonna check my queries.