Eloquent Query Optimization: Slaying N+1 Problems at Scale

The N+1 Problem Is Not Just a Beginner Mistake

Every Laravel developer learns about with() early on, but N+1 queries keep appearing in production codebases — often in places that look perfectly reasonable at first glance. The real danger is not the obvious loop; it is the subtle one hiding inside a resource transformer, a Blade component, or a policy check.

Reproducing the Classic Case

// Fetches 1 query for posts, then 1 per post for the author — classic N+1
$posts = Post::all();

foreach ($posts as $post) {
    echo $post->author->name; // lazy-loads author every iteration
}

The fix is well-known:

$posts = Post::with('author')->get();

But what about conditional relationships, or relationships accessed three layers deep inside a resource class?

Detecting Queries You Did Not Know Were There

Install Laravel Telescope or use the DB::listen trick in a test:

$queries = [];
DB::listen(fn ($q) => $queries[] = $q->sql);

$resource = new PostCollection(Post::with('author')->paginate(20));
$resource->toArray(request());

dump(count($queries)); // should be 2, not 21

For CI, the assertQueryCount helper from pestphp/pest-plugin-laravel is invaluable:

it('loads posts without N+1', function () {
    Post::factory(20)->for(User::factory(), 'author')->create();

    $count = 0;
    DB::listen(fn () => $count++);

    Post::with('author')->get()->each(fn ($p) => $p->author->name);

    expect($count)->toBe(2);
});

Subquery Selects: Pulling Aggregates Without Extra Queries

A common pattern is showing the latest comment date on a post list. The naive approach eager-loads all comments just to grab max(created_at). Use a subquery select instead:

$posts = Post::select('posts.*')
    ->addSelect([
        'latest_comment_at' => Comment::select('created_at')
            ->whereColumn('post_id', 'posts.id')
            ->latest()
            ->limit(1),
    ])
    ->get();

// Access as a plain attribute — zero extra queries
echo $posts->first()->latest_comment_at;

This emits a single SQL query with a correlated subquery. On indexed columns it is extremely efficient and far cleaner than withCount + withMax chains.

Lazy Eager Loading vs. load() vs. loadMissing()

When you receive a model that may or may not have a relationship already loaded, reach for loadMissing() rather than load():

// load() always fires the query, even if already loaded
$post->load('tags');

// loadMissing() skips the query if the relation is cached
$post->loadMissing('tags');

This matters inside service classes that can be called from both a controller (where with() was used) and a queued job (where it was not).

Preventing Lazy Loading Globally in Development

Laravel ships with Model::preventLazyLoading(). Enable it in AppServiceProvider for non-production environments:

public function boot(): void
{
    Model::preventLazyLoading(! app()->isProduction());
}

This throws a LazyLoadingViolationException the moment any relationship is lazy-loaded, turning silent performance bugs into loud, catchable errors during development and CI.

Chunking and Cursor Iteration for Large Result Sets

When processing thousands of rows, get() loads everything into memory. Prefer cursor() for read-only iteration or chunkById() for write operations:

// cursor() uses a PHP generator — one model in memory at a time
Post::with('author')->cursor()->each(function (Post $post) {
    // process
});

// chunkById() is safe when rows are deleted/updated mid-process
Post::chunkById(500, function ($posts) {
    $posts->each(fn ($p) => $p->update(['processed' => true]));
});

Note: cursor() does not support eager loading via with() — the generator fetches one row at a time from the PDO cursor, so relationships will lazy-load. For large sets with relationships, chunkById + loadMissing is the correct pattern.

Key Takeaways

• Use Model::preventLazyLoading() in development to surface N+1 bugs immediately.
• Subquery selects replace eager-loaded aggregates with a single efficient query.
• Prefer loadMissing() over load() in reusable service methods.
• cursor() saves memory but does not support eager loading — use chunkById() when you need both scale and relationships.
• Assert query counts in Pest tests to prevent regressions.