Eloquent N+1 at Scale: Eager Loading Strategies, Subquery Selects, and Lazy Eager Loading

Eloquent N+1 at Scale: Beyond with()

Every Laravel developer knows with(). Fewer know when it becomes the wrong tool. At scale — thousands of rows, deeply nested relations, or paginated admin panels — naive eager loading trades N+1 for a single monstrous IN (...) clause that can be just as slow. Here is how to think more precisely.

The Classic Problem, Briefly

// Fires 1 + N queries — one per post
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->author->name;
}

The fix everyone knows:

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

This works until your IN clause contains 50,000 IDs and MySQL's optimizer gives up.

Subquery Selects: Collapse the Relation Into a Column

When you only need a single scalar from a relation — a count, a latest date, a status flag — a subquery select is almost always faster than eager loading the full relation.

use Illuminate\Database\Query\Builder;

$posts = Post::query()
    ->addSelect([
        'latest_comment_at' => Comment::select('created_at')
            ->whereColumn('post_id', 'posts.id')
            ->latest()
            ->limit(1),
    ])
    ->withCasts(['latest_comment_at' => 'datetime'])
    ->paginate(50);

One query. No relation hydration. The subquery runs correlated per row, but the database can use an index on (post_id, created_at) and the planner often chooses a nested-loop index scan that outperforms a large IN.

The same pattern replaces withCount for filtered counts:

$posts = Post::query()
    ->addSelect([
        'approved_comment_count' => Comment::selectRaw('COUNT(*)')
            ->whereColumn('post_id', 'posts.id')
            ->where('approved', true),
    ])
    ->get();

withCount vs. loadCount vs. Subquery — When to Use Each

| Scenario | Recommended approach | |---|---| | Always need the count | withCount on the base query | | Conditionally need it after load | loadCount on the collection | | Filtered count or latest scalar | Subquery select | | Multiple aggregates, same table | Single raw subquery with CASE |

Lazy Eager Loading Without the N+1

load() fires a second query after the collection is already in memory. It is not lazy in the PHP sense — it is deferred eager loading. Use it when the decision to load a relation depends on runtime logic:

$posts = Post::paginate(100);

if ($request->boolean('include_authors')) {
    $posts->load('author');
}

For truly lazy loading with N+1 prevention, enable Model::preventLazyLoading() in AppServiceProvider:

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

This throws a LazyLoadingViolationException in local and CI environments, surfacing every unguarded relation access before it reaches production.

Chunked Processing and Eager Loading Together

chunk() and cursor() break large datasets into manageable pieces, but they do not automatically eager-load relations. Use chunkById with a manual load() call:

Post::chunkById(500, function ($posts) {
    $posts->load('author', 'tags');

    foreach ($posts as $post) {
        // author and tags already hydrated
        dispatch(new SyncPostToSearch($post));
    }
});

Avoid cursor() when you need relations — it yields one model at a time and cannot batch-load anything.

Detecting N+1 in CI with Clockwork or a Custom Listener

DB::listen(function ($query) {
    if (app()->runningUnitTests()) {
        // Fail fast if a single test fires more than 10 queries
        static $count = 0;
        if (++$count > 10) {
            throw new \RuntimeException('Possible N+1: query count exceeded threshold.');
        }
    }
});

This is crude but effective as a canary. For production, Telescope's query panel grouped by endpoint is the fastest way to spot regressions.

Key Takeaways

• with() is correct for full relation hydration; subquery selects are better for single scalars or filtered aggregates.
• Model::preventLazyLoading() in non-production environments eliminates entire classes of N+1 bugs before deployment.
• chunkById + load() is the correct pattern for large batch jobs that need relations.
• A correlated subquery with a good covering index often outperforms a large IN (...) eager load.
• Measure with EXPLAIN ANALYZE before assuming eager loading is always the right fix.