Solving the N+1 Problem in Eloquent

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.

What is the N+1 problem?

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

$recipes = Recipe::all();

Recipe model has a 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 when displaying the above list:

What happened? Displaying 10 recipes generates 11 queries! An additional query is executed for each recipe to get the category name. Imagine what happens 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 with the with method. According to the example above, our query could look like this:

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

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

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

This reduces the number of queries from 11 to 2:

Useful tools

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

Tools like Laravel Telescope or Laravel Debugbar let you manually review queries, but there are also packages that will instantly notify you about the N+1 problem.

Laravel N+1 Query Detector

An excellent and simple package that monitors your queries in real time during app development. Just install it with Composer and you’ll get an alert whenever eager loading should be used.

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

Disable lazy loading globally

Laravel 8 introduced a built-in way to disable lazy loading across your entire application. Add the following line to the boot method in app/Providers/AppServiceProvider.php:

<?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 lazy loading is used an exception will be thrown - but only in non-production environments, so no need to worry about crashing production if you miss something.