Eager Loading and the N+1 Problem

A loop that reads a relationship runs one query per row; with() and load() fetch them all in one extra query instead.

SyntaxPost::with('author')->get(); $posts->load('author');

An N+1 is one query for a list, plus one more for every row in it. Twenty posts whose author name you print in a loop is twenty-one queries. Each one is fast. The page is not.

It happens because Eloquent relationships are lazy. Reading $post->author on a post that has no loaded author runs a query at that moment, and a loop hides how often that happens.

Loading the relationship up front

with() adds the related rows to the same piece of work. Eloquent runs the list query, then one query like select * from users where id in (1, 2, 3), then matches each author to its post in PHP. Two queries for any number of posts. load() does the same for models you already hold, and loadMissing() skips what is loaded already.

Dot notation and the array form are the same call: with('comments.author') is with(['comments' => ['author']]).

Making it fail loudly

Model::preventLazyLoading() turns a lazy load into an Illuminate\Database\LazyLoadingViolationException that names the model and the relationship. Enable it everywhere but production, in AppServiceProvider::boot(). It stays quiet for a model that does not exist in the database yet or was just created.

For a set too large for memory, lazy() streams it in chunks and can still eager load. cursor() cannot, because it holds only one model at a time.

Example

Example · php
<?php

// app/Http/Controllers/PostController.php

declare(strict_types=1);

namespace App\Http\Controllers;

use App\Models\Post;
use Illuminate\View\View;

class PostController extends Controller
{
    public function index(): View
    {
        // Without with() and withCount(): one query for the page of posts,
        // then one per post for its author and one per post to count its
        // comments. With them: three queries, whatever the page size -
        // the pagination count, the posts (comments counted by subquery),
        // and the authors.
        $posts = Post::query()
            ->with('author')
            ->withCount('comments')
            ->latest()
            ->paginate(20);

        return view('posts.index', ['posts' => $posts]);
    }
}

When to use it

  • An index page that lists posts with their author and a comment count.
  • A JSON endpoint that nests each order's line items.
  • Finding why a page that renders 50 rows issues 150 queries.
  • Streaming a large export with lazy() while still loading each row's relationship.

More examples

Turn lazy loads into errors outside production

This is the cheapest N+1 detector: the next lazy load throws, and the stack trace points at the line that did it. If you register Model::handleLazyLoadingViolationUsing(fn (Model $model, string $relation) => ...) the callback runs instead of the exception being thrown, which suits staging - log the violation rather than returning a 500. Keep it off in production, where a missing with() should degrade rather than fail.

Example · php
<?php

// app/Providers/AppServiceProvider.php

declare(strict_types=1);

namespace App\Providers;

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

class AppServiceProvider extends ServiceProvider
{
    public function boot(): void
    {
        // True unless APP_ENV is production.
        Model::preventLazyLoading(! $this->app->isProduction());
    }
}

Loading after the models are already in hand

Reach for load() when the decision to need a relationship is made after the query - in a controller that already fetched the models, or in a view composer. loadMissing() is the safe default inside shared code, because calling load() twice would run the query twice. A constrained eager load still costs one query; the closure receives the query builder for the related table.

Example · php
<?php

use App\Models\Post;
use Illuminate\Contracts\Database\Eloquent\Builder;

$posts = Post::latest()->take(5)->get();

// One extra query for the five authors, not five queries.
$posts->load('author');

// Loads comments; leaves author alone because it is already there.
$posts->loadMissing(['author', 'comments']);

// A constrained eager load. The closure receives the relation, which
// implements the Builder contract - so type-hint the contract, not
// Illuminate\Database\Eloquent\Builder, or the call throws a TypeError.
// The arrow function is the short form of
// function (Builder $query) { return $query->where('approved', true); }
$posts->load([
    'comments' => fn (Builder $query) => $query->where('approved', true),
]);

What the second query buys you

Eager loading is not a join. Eloquent fetches the parents, collects their foreign keys, fetches the related rows with one `where in`, keys them, and attaches them in PHP - which is what this plain collection pipeline does. Two authors serve three posts, so the second query returns fewer rows than the first. Knowing this explains why with() needs the foreign key column present on the parent rows.

Example · php
<?php

// The matching that with('author') does in PHP after its second query.
$posts = collect([
    ['id' => 1, 'user_id' => 7, 'title' => 'Indexes'],
    ['id' => 2, 'user_id' => 9, 'title' => 'Queues'],
    ['id' => 3, 'user_id' => 7, 'title' => 'Caching'],
]);

$authors = collect([
    ['id' => 7, 'name' => 'Ada'],
    ['id' => 9, 'name' => 'Grace'],
])->keyBy('id');

$joined = $posts->map(fn (array $post): array => $post + [
    'author' => $authors->get($post['user_id'])['name'],
]);

echo $joined->pluck('author')->implode(', ').PHP_EOL;

// Output:
// Ada, Grace, Ada

Related

laravel adv eager loadingeloquent withlaravel eloquent relationshipslaravel query performancelaravel debugbar telescope

Discussion

  • Be the first to comment on this lesson.