Posted • 3 min to read

When using such convenient tools as Eloquent ORM, it's easy to forget about the N+1 problem. Ignoring it may lead to serious server slowdowns at an unexpected time. In this article I'll explain what the problem is, how to solve it and detect it at an early stage before problems occur.
photo by Johannes Plenio (pexels.com)

What is the N+1 problem?

Suppose we have a query that retrieves all recipes from database:

$recipes = Recipe::all();

Recipe model has belongsTo relation to Category model:

public function category(): BelongsTo
{
    return $this->belongsTo(Category::class);
}

In a blade view we display the recipes with their category:

@foreach ($recipes as $recipe)
    <article>
        <h2>{{ $recipe->name }}</h2>
        <p>{{ $recipe->category->name }}</p>
    </article>
@endforeach

Let's use Laravel Telescope and see what queries are executed by displaying the above list:

SQL queries shown by Laravel Telescope

What happened? Displaying 10 recipes, generates 11 queries! An additional query is executed for each recipe to get the category name! Imagine what will happen when there are more records to display and more relations. It's a waste of resources.

The solution

Just avoid lazy loading and use eager loading. In Eloquent you can achieve this by using the with method. According to the example above, our query could look like this:

$recipes = Recipe::with('categories')->all();

or if you want to eager load multiple relationships at once:

$recipes = Recipe::with(['categories', 'tags', 'author'])->all();

This solution reduces the number of queries from 11 to 2:

SQL queries shown by Laravel Telescope

Useful tools

As you can see, the solution was simple. The hardest part is keeping an eye on whether we are creating unnecessary queries in the project. This can easily happen if several people are working on the same project, especially if front-end developer prepares the views and someone else if responsible for the backend.

There are packages like Laravel Telescope (which I have used above) or Laravel Debugbar where you can manually review the queries, but there are also tools and techniques that will instantly notify you about the N+1 problem.

Laravel N+1 Query Detector

Excellent and simple package that monitors your queries in real time during app development. Just install it with composer and you will get an alert when eager loading should be used.

Laravel N+1 query detector

Download it from GitHub: beyondcode/laravel-query-detector

Disable lazy loading globally

Laravel 8 gives you a nice feature for disabling lazy loading across your application. Add the following line to the boot method in the app\Providers\AppServiceProvider.php file:

<?php

namespace App\Providers;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\ServiceProvider;

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

This way every time you use lazy loading, an exception will be thrown, but only on non-production environment, so no need to worry about crashing your production if you miss something.

Lazy loading exception