DEV Community

Cover image for Boost your queries in Laravel
Reza Amini
Reza Amini

Posted on

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'));
Enter fullscreen mode Exit fullscreen mode

And in your Blade :

@foreach($articles as $article)
    <li>{{ $article->title }}</li>
    <li>{{ $article->author->name }}</li>
@endforeach
Enter fullscreen mode Exit fullscreen mode

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`
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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'));
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
}]);
Enter fullscreen mode Exit fullscreen mode

Discussion (1)

Collapse
fgpetter profile image
FILIPE PETTER

Very good! Now I gonna check my queries.