What is the N+1 query problem?

In short, it's when Laravel code runs too many database queries. It happens because Eloquent allows developers to write a readable syntax with models, without digging deeper into what "magic" is happening under the hood.

This is not only an Eloquent, or even Laravel, problem: it's well-known in the dev industry. Why is it called "N+1"? Because, in the Eloquent case, it queries ONE row from the database, and then performs one more query for EACH related record. So, N queries, plus the record itself, total N+1.

To solve it, we need to query the related records upfront, and Eloquent allows us to do that easily, with so-called eager loading. But before we get to the solutions, let's discuss the problems.

Case 1. "Regular" N+1 query.

This one can be taken directly from the official Laravel documentation:

// app/Models/Book.php:
class Book extends Model
{
    public function author()
    {
        return $this->belongsTo(Author::class);
    }
}
 
// Then, in some Controller:
$books = Book::all();
 
foreach ($books as $book) {
    echo $book->author->name;
}

What happens here? The $book->author part will perform one extra DB query for every book, to get its author.

I've created a small demo project to simulate this and seeded 20 fake books with their authors. Look at the number of queries.

As you can see, for 20 books, there are 21 queries, exactly N+1, where N = 20.

And yes, you get it right: if you have 100 books on the list, you will have 101 queries to the DB. Awful performance, although the code seemed "innocent", right.

The fix is to load the relationship upfront, immediately in the Controller, with the eager loading that I mentioned earlier:

// Instead of:
$books = Book::all();
 
// You should do:
$books = Book::with('author')->get();

The result is much better - only 2 queries:

When you use eager loading, Eloquent gets all the records into the array and launches ONE query to the related DB table, passing those IDs from that array. And then, whenever you call $book->author, it loads the result from the variable that is already in memory, no need to query the database again.

Now, wait, you wonder what is this tool to show queries?

Always use Debugbar. And seed fake data.

This bottom bar is a package Laravel Debugbar. All you need to do to use it is install it:

composer require barryvdh/laravel-debugbar --dev

And that's it, it will show the bottom bar on all the pages. You just need to enable debugging with the .env variable APP_DEBUG=true, which is a default value for local environments.

Security notice: make sure that when your project goes live, you have APP_DEBUG=false on that server, otherwise regular users of your website will see the debugbar and your database queries, which is a huge security issue.

Of course, I advise you to use Laravel Debugbar on all your projects. But this tool by itself will not show the obvious problems until you have more data on the pages. So, using Debugbar is only one part of the advice.

In addition, I also recommend having seeder classes that would generate some fake data. Preferably, a lot of data, so you would see how your project performs "in real life" if you imagine it growing successfully in the future months or years.

Use Factory classes and then generate 10,000+ records for books/authors and other models:

class BookSeeder extends Seeder
{
    public function run()
    {
        Book::factory(10000)->create();
    }
}

Then, browse through the website and look at what Debugbar shows you.

Case 2. Two important symbols.

Let's say that you have the same hasMany relationship between authors and books, and you need to list the authors with the number of books for each of them.

Controller code could be:

public function index()
{
    $authors = Author::with('books')->get();
 
    return view('authors.index', compact('authors'));
}

And then, in the Blade file, you do a foreach loop for the table:

@foreach($authors as $author)
    <tr>
        <td>{{ $author->name }}</td>
        <td>{{ $author->books()->count() }}</td>
    </tr>
@endforeach

Looks legit, right? And it works. But look at the Debugbar data below.

But wait, you would say that we are using eager loading, Author::with('books'), so why there are so many queries happening?

Because, in Blade, $author->books()->count() doesn't actually load that relationship from the memory.

  • $author->books() means the METHOD of relation
  • $author->books means the DATA eager loaded into memory

So, the method of relation would query the database for each author. But if you load the data, without () symbols, it will successfully use the eager loaded data:

So, watch out for what you're exactly using - the relationship method or the data.

Notice that in this particular example there's an even better solution. If you need only the calculated aggregated data of the relationship, without the full models, then you should load only the aggregates, like withCount:

// Controller:
$authors = Author::withCount('books')->get();
 
// Blade:
{{ $author->books_count }}

As a result, there will be only ONE query to the database, not even two queries. And also the memory will not be "polluted" with relationship data, so some RAM saved as well.

Case 3. "Hidden" relationship in accessor.

Let's take a similar example: a list of authors, with the column of whether the author is active: "Yes" or "No". That activity is defined by whether the author has at least one book, and it is calculated as an accessor inside of the Author model.

Controller code could be:

public function index()
{
    $authors = Author::all();
 
    return view('authors.index', compact('authors'));
}

Blade file:

@foreach($authors as $author)
    <tr>
        <td>{{ $author->name }}</td>
        <td>{{ $author->is_active ? 'Yes' : 'No' }}</td>
    </tr>
@endforeach

That "is_active" is defined in the Eloquent model:

use Illuminate\Database\Eloquent\Casts\Attribute;
 
class Author extends Model
{
    public function isActive(): Attribute
    {
        return Attribute::make(
            get: fn () => $this->books->count() > 0,
        );
    }
}

Notice: this is a new syntax of Laravel accessors, adopted in Laravel 9. You can also use the "older" syntax of defining the method getIsActiveAttribute(), it will also work in the latest Laravel version.

So, we have the list of authors loaded, and again, look what Debugbar shows:

Yes, we can solve it by eager loading the books in the Controller. But in this case, my overall advice is avoid using relationships in accessors. Because an accessor is usually used when showing the data, and in the future, someone else may use this accessor in some other Blade file, and you will not be in control of what that Controller looks like.

In other words, Accessor is supposed to be a reusable method for formatting data, so you're not in control of when/how it will be reused. In your current case, you may avoid the N+1 query, but in the future, someone else may not think about it.

The built-in solution against N+1 query.

Now, after we've covered examples, I will give you the last tip: since Laravel 8.43, the framework has a built-in N+1 query detector!

In addition to the Laravel Debugbar for inspection, you can add a code to the prevention of this problem.

You need to add two lines of code to app/Providers/AppServiceProvider.php:

use Illuminate\Database\Eloquent\Model;
 
class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        Model::preventLazyLoading(! app()->isProduction());
    }
}

Now, if you launch any page that contains an N+1 query problem, you will see an error page, something like this:

This will show you the exact "dangerous" code that you may want to fix and optimize.

Note that this code should be executed only on your local machine or testing/staging servers, live users on production servers should not see this message, cause that would be a security issue. That's why you need to add a condition like ! app()->isProduction(), which means that your APP_ENV value in the .env file is not "production".