Skip to content
Tyrone Software
PHP Coding

Catching N+1 queries in Laravel before production does

Abstract illustration for the article Catching N+1 queries in Laravel before production does

A listing page shows fifty orders with their customer names. It loads in 30 ms on a developer's laptop, and in production, with a busier database and network latency, it takes a second. Fifty-one queries are running where two would do.

The pattern

$orders = Order::all();

foreach ($orders as $order) {
    echo $order->customer->name;   // one extra query per order
}

The relationship customer is loaded lazily the first time it is accessed, once per order. The fix is eager loading, which fetches all customers in one extra query:

$orders = Order::with('customer')->get();

Make it impossible to miss

Laravel can refuse lazy loading. Put this in AppServiceProvider::boot():

Laravel service provider code enabling preventLazyLoading outside production
Violations throw exceptions in development and tests, and go unpunished in production unless you log them.

With preventLazyLoading, accessing a relationship that was not eager loaded throws a LazyLoadingViolationException. The method exists since Laravel 8.43. The ! isProduction() condition means development and test runs fail loudly, while production only logs, because you do not want a missed relation to take down a live page. In production, you can register a handler with Model::handleLazyLoadingViolationUsing() to log violations and find the ones your tests did not cover.

Newer versions also offer Model::shouldBeStrict(), which enables lazy loading prevention together with protection against silently discarded attributes and against accessing attributes that were not loaded. Check that your Laravel version has it before relying on it.

Related ways to load less

  • Count without loading. withCount('items') adds an items_count column using a subquery, instead of loading every item to call count() on the collection.
  • Select only the columns you need. with('customer:id,name') loads a subset. Include the key columns that the relation needs.
  • Constrain the eager load. with(['items' => fn ($q) => $q->where('status', 'open')]) limits what is fetched.
  • Load later when needed. $orders->load('customer') eager loads on an existing collection, and loadMissing skips relations that are already loaded.
  • Nested relations. with('items.product') loads two levels in three queries in total.

Large result sets

For batch jobs, chunkById(1000, ...) processes rows in slices and is safe when you modify the rows as you go. lazy() streams results through a generator using chunked queries. cursor() fetches one row at a time in the loop, but with the MySQL driver's default buffered queries the entire result set is still transferred into PHP memory, so it may not reduce memory as much as you expect. If memory matters, prefer chunkById or lazy, and check with memory_get_peak_usage(true).

How to see the queries

Laravel Debugbar and Telescope show every query of a request, and DB::listen() gives you a hook for logging. A test that asserts a page runs at most a fixed number of queries (with DB::enableQueryLog(), or a package for query counting) stops the problem from returning after a refactor.

API resources hide the loop from you

The N+1 pattern above is easy to spot in a foreach you wrote yourself. It is much easier to miss inside a Laravel API Resource collection, where the loop is implicit: an OrderResource::collection($orders) that references $this->customer->name inside toArray() runs exactly the same lazy load once per order, but the iteration happens inside the framework's resource-collection machinery rather than in a block of code you are looking at when reviewing the controller. Model::preventLazyLoading() still catches it, since the violation happens at the same $order->customer access regardless of what called it, which is one more reason to enable it broadly rather than only in code paths that visibly contain a loop; the controller action that returns OrderResource::collection($orders) gives no visual hint that a relation is about to be lazily loaded fifty times.

Keep reading

Technologies

  • Python
  • PHP
  • Laravel
  • Django
  • PostgreSQL
  • MySQL
  • HTML5
  • CSS3
  • Bootstrap