Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 67 ยท Eloquent ORM

Eager Loading and the N+1 Problem

Learn what the N+1 query problem is, how with(), load() and constrained eager loads fix it, and how preventLazyLoading catches it, with a query-counting demo.

Relationships make code pleasant to read, and that is exactly the danger. $post->author->name looks like a property access, yet the first time you read author Eloquent runs a SQL query. Put that line inside a loop over 50 posts and you have quietly written 51 queries: one to fetch the posts and one more for each author. This is the N+1 problem, the most common performance bug in Laravel applications.

Nothing looks wrong on your laptop with five rows. In production, with real traffic and a database on another machine, the same page can go from 5 ms to 2 seconds. The fix is called eager loading: tell Eloquent up front which relationships you need, so it fetches them together in a fixed, small number of queries. In this lesson you will see the problem happen, fix it with with(), shape the eager load with constraints, and turn on a safety net that makes lazy loading throw an error during development. It builds on relationships; the runnable code is a PHP stand-in on SQLite, while the Laravel snippets are read-only.

Seeing the problem

This is the classic N+1 code. The controller loads posts, and the Blade view prints the author of each:

PHP
// Controller
$posts = Post::latest()->take(50)->get();          // query 1

// View
@foreach ($posts as $post)
    <li>{{ $post->title }} by {{ $post->author->name }}</li>   {{-- query 2..51 --}}
@endforeach

The SQL it produces looks like this:

SQL
select * from posts order by created_at desc limit 50;
select * from users where id = 1;
select * from users where id = 2;
select * from users where id = 1;   -- the same user again
-- ... and so on, one per post

Fifty posts, perhaps ten distinct authors, and still fifty-one queries. Eloquent caches the relation per model instance, not across the loop, so two posts by the same author do not share the lookup.

sequenceDiagram
    participant C as Controller
    participant DB as Database
    C->>DB: select * from posts
    loop for each post (N times)
        C->>DB: select * from users where id = ?
    end
    Note over C,DB: 1 + N round trips

Lazy loading: one query for the list, then one more per row.

The fix: with()

with() loads the relationship for all the parent models at once:

PHP
$posts = Post::with('author')->latest()->take(50)->get();

Eloquent now runs exactly two queries, no matter how many posts there are:

SQL
select * from posts order by created_at desc limit 50;
select * from users where id in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

It collects the distinct foreign keys, fetches all matching users with a single IN (...), then attaches each user to the right post in PHP. The loop in the view now finds author already loaded and runs no queries.

You can load several relations, and nested ones with dot notation:

PHP
Post::with(['author', 'tags', 'comments.user'])->get();

This is one query for posts, one for authors, one for tags (through the pivot), one for comments and one for the comment authors: still a handful, not hundreds. Each added relation costs one query, not one per row.

Here is the problem and the fix side by side. The stand-in counts every query it sends:

PHP Counting queries: lazy vs eager

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The lazy version uses 21 queries for 20 posts; the eager version uses 2, and would still use 2 for 20,000 posts. The matching step, keying the users by id and looking each one up, is essentially what Eloquent does.

Constraining and trimming eager loads

Eager loading is a query, so you can shape it. Pass an array with a closure to add constraints, order or limit the relation, or select fewer columns:

PHP
$posts = Post::with([
    'comments' => fn ($query) => $query->where('approved', true)->latest(),
    'author:id,name',                       // only these columns (include the key!)
])->get();

Each post's comments now holds only approved comments, newest first. In the column form, always include the primary key (id) and any foreign key Eloquent needs to match rows, otherwise the relation silently comes back empty.

One limitation catches people out: ->limit(3) inside a constrained eager load limits the whole eager query, not each parent's rows. "The latest 3 comments per post" needs a different tool, such as latestOfMany() for one row, or a window function. Be skeptical of a constraint that looks per-parent.

If a relation is needed on nearly every use of a model, you can set protected $with = ['author']; on it. Use this sparingly: it loads the relation even when a page does not need it, and you can opt out with without('author').

load(), loadMissing() and counts

Sometimes you already hold a model or collection and decide later that you need a relation. load() does the same eager query afterwards:

PHP
$posts = Post::all();
if ($wantsAuthors) {
    $posts->load('author');           // 1 extra query for all posts
}

$post->loadMissing('comments');       // only if not loaded already
$posts->loadCount('comments');        // comments_count, as with withCount()

load() is the answer when route model binding hands you a single Post and the view needs its comments. loadMissing() is safe to call from code that does not know whether the caller already loaded the relation.

Making N+1 impossible to ignore

You cannot fix what you do not notice. Laravel can forbid lazy loading outside of production, so a missing with() throws an exception in development and tests instead of silently slowing things down:

PHP
// app/Providers/AppServiceProvider.php
use Illuminate\Database\Eloquent\Model;

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

Now touching an unloaded relation raises a LazyLoadingViolationException naming the model and the relation. In production you usually do not want a user-facing crash, so the flag is off there; instead you can call Model::handleLazyLoadingViolationUsing() to log the violation. Model::shouldBeStrict() bundles this check with two others (silent discarding of non-fillable attributes, and access to missing attributes). Newer Laravel versions can also load relations automatically when they are first touched in a loop, but explicit with() stays the clearest and most predictable tool.

Other ways to spot the problem: the query count shown by tools such as Laravel Debugbar or Telescope, DB::listen() to log each query, and assertions in tests (for instance with DB::enableQueryLog() and a count). A test that says "this page runs at most 6 queries" protects you from regressions.

This stand-in adds a guard and a constrained eager load:

PHP A lazy-loading guard and constrained eager loads

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The first access throws because nothing was loaded and the guard is on; after the constrained load, both posts expose only their approved comments, using a single query. The "Spam" comment never reached PHP.

A checklist before you ship a page

  • For every loop in a view or resource, ask which relations are read inside it, then name them in with().
  • Use withCount() or withSum() when you only need a number.
  • Trim columns on big tables, and keep keys in the select list.
  • Turn on preventLazyLoading in local development and in your test suite.
  • Check the real query count with a debugging tool before launching a heavy page.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A page lists 100 posts and prints each post's author through lazy loading. How many queries run?

    One query loads the posts and one more query per post loads its author, so 1 + N, which is 101.
  2. 2. What does Post::with('author')->get() run for 100 posts?

    Eager loading fetches the posts, then one query for all needed authors, and matches them up in PHP.
  3. 3. You already have a Post from route model binding and later need its comments. Which method fits?

    load() performs the eager query on models you already hold. with() is for building a query before it runs.
  4. 4. What does Model::preventLazyLoading(! app()->isProduction()) do?

    The check turns silent N+1 problems into visible exceptions during development and testing, while production keeps serving pages.
  5. 5. In a constrained eager load, what happens to ->limit(3) on a hasMany relation?

    Eager loading is one query for all parents, so a plain limit applies to that whole query and per-parent limits need another technique.

Summary

  • Reading a relationship inside a loop without loading it first runs a query per row: 1 + N queries in total.
  • with() eager loads relations in a fixed number of queries, using IN (...) and matching in PHP; nested relations use dot notation.
  • Constrain eager loads with closures, trim columns with author:id,name, and keep the keys.
  • load() and loadMissing() eager load on models you already have; loadCount() and withCount() fetch counts only.
  • Model::preventLazyLoading() makes forgotten eager loads fail loudly outside production.
  • Measure query counts with debugging tools and tests, not by guessing.

Next: Accessors, Mutators and Casts