What is N+1 and how do you fix it in Eloquent?

Intermediate5 min read·eng-10-003

interviewsql

Concept

N+1 in Laravel — already covered in eng-06-013, but here's the interview-focused answer with Eloquent specifics.

What is N+1: 1 query to load N models, then N additional queries — one per model — to load a relationship. Worst case: User::all() (1 query) + $user->posts for each user (N queries) = N+1.

Root cause in Eloquent: Accessing a relationship on a model instance triggers a lazy load — a new query is executed only for that model. When done in a loop: N queries.

Fixes:

with() — eager loading. User::with('posts'). Executes 2 queries: one for users, one for all related posts using WHERE user_id IN (...).
withCount() — count without loading. User::withCount('posts') adds posts_count attribute. 1 extra query.
load() — eager load on already-fetched collection. $users->load('posts').
loadMissing() — only loads if not already loaded.

preventLazyLoading() (Laravel 8.4+): Model::preventLazyLoading(!app()->isProduction()) — throws LazyLoadingViolationException whenever a lazy load would execute. Forces you to use eager loading in development. Best practice: enable it everywhere.

Sub-select pattern (for aggregate data without N+1):

php

User::addSelect(['last_order_date' => Order::select('created_at')->whereColumn('user_id', 'users.id')->latest()->limit(1)])

cursor() vs N+1: cursor() loads models one at a time (generator), but still triggers lazy loads if you access relationships. Add with() before cursor().

Code Example

php

<?php
// ❌ N+1 — 1 + N queries for N users
$users = User::all();
foreach ($users as $user) {
    echo $user->latestOrder->total; // SELECT * FROM orders WHERE user_id = ? LIMIT 1
}

// ✅ Eager load with() — 2 queries total
$users = User::with('latestOrder')->get();
foreach ($users as $user) {
    echo $user->latestOrder?->total; // no query — already loaded
}

// ✅ withCount — add count without loading all related models
$users = User::withCount('orders')->get();
foreach ($users as $user) {
    echo $user->orders_count; // comes from SQL: (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS orders_count
}

// ✅ Nested eager loading — posts AND their comments AND comment authors
$users = User::with('posts.comments.author')->get();
// 4 queries: users, posts (IN users), comments (IN posts), users again (IN comments)

// ✅ Conditional eager loading — only approved comments
$posts = Post::with(['comments' => fn($q) => $q->where('approved', true)->orderBy('created_at')])->get();

// ✅ load() — when you already have the collection
$users = cache()->remember('users', 3600, fn() => User::all());
$users->load('posts'); // one extra query to load all posts for all users

// ✅ Sub-select pattern — attach computed columns without N+1
$users = User::addSelect([
    'last_order_at' => Order::select('created_at')
        ->whereColumn('user_id', 'users.id')
        ->latest()
        ->limit(1),
])->get();
foreach ($users as $user) {
    echo $user->last_order_at; // a column value, not a relationship — no extra query
}

// Prevention during development
// In AppServiceProvider::boot():
\Illuminate\Database\Eloquent\Model::preventLazyLoading(!app()->isProduction());
// Now accessing $user->posts on a model WITHOUT with() throws:
// Illuminate\Database\LazyLoadingViolationException
// Attempted to lazy load [posts] on model [App\Models\User]

// Detecting N+1 manually
\DB::enableQueryLog();
// ... your code ...
$log = \DB::getQueryLog();
// Count queries with similar patterns — many similar SELECTs = N+1

PreviousHow does the service container resolve dependencies?Senior Interview Prep — Laravel NextHow would you scale a Laravel application?Senior Interview Prep — Laravel