The order listing page ran 1,412 queries. It showed forty orders. Nobody had written a loop containing a query, and every one of those queries came from a line that looks like a property access.
The symptom
GET /admin/orders 1412 queries, 3.81s
select * from orders limit 40 1x
select * from customers where id = ? 40x
select * from addresses where customer_id = ? 40x
select * from order_lines where order_id = ? 40x
select * from products where id = ? 1291xFast with ten test orders, unusable with forty real ones, and catastrophic on the customer with three hundred lines. The scaling is what makes this class of bug survive review: it is invisible at development data volumes.
Why it happens
Lazy loading is a good default and an invisible one. $order->customer is a property access in the template and a database query at runtime, and nothing at the call site distinguishes it from reading a column that was already fetched.
@foreach ($orders as $order)
{{ $order->customer->name }} {{-- query --}}
{{ $order->customer->address->city }} {{-- query --}}
@foreach ($order->lines as $line) {{-- query --}}
{{ $line->product->sku }} {{-- query, per line --}}
@endforeach
@endforeachThe template is readable, correct and the source of every one of the 1,411 extra queries.
The fix
Eager load what the view touches
$orders = Order::with([
'customer.address',
'lines.product',
])->latest()->paginate(40);
// 1412 queries becomes 5:
// orders, customers, addresses, order_lines, productsOne query per relation regardless of row count — the relations are fetched with a single WHERE id IN (...) and matched up in memory. Nested relations use dot notation and are loaded in the same way.
A constraint can be attached, which is the form most people write as a filter on the parent by mistake:
// filters the LINES, keeps every order
Order::with(['lines' => function ($q) { $q->where('refunded', false); }])->get();
// filters the ORDERS — completely different query
Order::whereHas('lines', function ($q) { $q->where('refunded', false); })->get();with() at query time, load() afterwards
When the collection arrives from somewhere that does not accept a with() — a repository, a cache, a paginator built elsewhere — the relations can still be loaded in one query rather than N.
$orders = $this->repository->recentForCustomer($id); // no control here
$orders->load('lines.product'); // one query per relation
$orders->loadMissing('customer'); // and only if not already loadedloadMissing() is the one to reach for in a view composer or a presenter, where the collection may or may not already have been eager loaded and reloading would be wasteful.
The accessor no eager load can fix
The remaining 91 queries after eager loading came from a place that no with() can reach: an accessor on the model that queries.
class Order extends Model
{
// runs a query every time the property is read
public function getIsFirstOrderAttribute()
{
return Order::where('customer_id', $this->customer_id)
->where('id', '<', $this->id)
->doesntExist();
}
}It is not a relation, so eager loading cannot see it. The options are to compute it for the whole set in one query and hand it to the view, or to denormalise it onto the row at write time. Both are more code than the accessor; the accessor is the reason the page was slow.
// one query for the whole page, instead of one per row
$firstOrderIds = Order::whereIn('customer_id', $orders->pluck('customer_id'))
->selectRaw('MIN(id) as id')
->groupBy('customer_id')
->pluck('id')
->flip();Verifying it worked
The count is the assertion, and it belongs in the test suite — otherwise the next person adding a field to the template reintroduces the problem and nothing notices.
public function testOrderListingDoesNotDegrade()
{
DB::enableQueryLog();
$this->actingAs($this->admin())->get('/admin/orders')->assertStatus(200);
$this->assertLessThan(10, count(DB::getQueryLog()));
}GET /admin/orders 6 queries, 0.09sA ceiling rather than an exact number, so an extra legitimate query does not fail the build while a regression to eighty does. Seed the test with enough rows that an N+1 would exceed it — with three orders, 1,412 queries becomes fourteen and the assertion passes.
Finding the rest of them before a customer does
Fixing one page fixes one page. The same pattern was on six others and none of them was slow enough to have been reported yet, which is the worst state for this class of bug — it gets discovered by growth rather than by testing.
// AppServiceProvider::boot(), staging only
if (app()->environment('staging')) {
$queries = 0;
DB::listen(function () use (&$queries) { $queries++; });
app()->terminating(function () use (&$queries) {
if ($queries > 30) {
Log::warning('query count', array(
'path' => request()->path(),
'n' => $queries,
));
}
});
}warning: query count {"path":"admin/orders","n":1412}
warning: query count {"path":"admin/customers","n":604}
warning: query count {"path":"admin/products","n":388}
warning: query count {"path":"basket","n":91}Four pages, ranked by severity, from a listener that took ten minutes to write. The threshold is arbitrary and that is fine — it is a filter for attention rather than an assertion. Anything that genuinely needs forty queries goes on an allow-list once, which is a better outcome than lowering the threshold until the log is quiet.
What this costs
Eager loading fetches everything named whether the view uses it or not, so a with() copied between controllers loads relations nobody renders — the opposite problem, and a quieter one because it is fast enough not to be noticed. The list belongs next to the view that needs it, not on the model as a default.
There is also a memory ceiling that the query count hides: eager loading forty orders with three hundred lines each is twelve thousand hydrated models in one request. Below a certain size the N+1 is the cheaper problem, which is worth remembering before applying this reflexively to a page that shows five rows.