The N+1 that only appeared in the API serialiser

The orders endpoint had eager loading in the controller, a query-count test, and a p95 of 2.1 seconds for one particular client. The test passed because it requested the default fields. That client requested three more.

The symptom

# the same endpoint, two callers
$ curl -s -o /dev/null -w '%{time_total}n' 
    '/api/orders?per_page=50'
0.184

$ curl -s -o /dev/null -w '%{time_total}n' 
    '/api/orders?per_page=50&fields=id,total,customer.name,shipment.status'
2.104

# the query log:
#   default fields:  4 queries
#   with fields:   154 queries

The controller eager-loads three relations and the client asked for a fourth, which the resource happily resolves one row at a time. Nothing is wrong with either half in isolation.

Why it happens

Eager loading is decided in the controller and relation access happens in the serialiser, which are different files written at different times by different people. The controller cannot know what the resource will touch, and the resource cannot load anything efficiently because it sees one model at a time.

Sparse fieldsets make the coupling dynamic as well as implicit: the set of relations needed is now a function of a query parameter, so no fixed list in the controller can be correct.

The fix

Making the failure loud in development

// AppServiceProvider::boot()
Model::preventLazyLoading(! app()->isProduction());

// and in production, report rather than throw
Model::handleLazyLoadingViolationUsing(
    function (Model $model, string $relation): void {
        Log::warning('orm.lazy_load', [
            'model'    => $model::class,
            'relation' => $relation,
            'route'    => request()->route()?->uri(),
        ]);
    }
);

Throwing in development and logging in production is the arrangement that finds these without causing an outage. The log entry carrying the route is what makes it actionable — a lazy load warning with no context is a needle in the log.

Turning this on across an existing application produces a large number of failures immediately, most of them in tests, and working through them is the actual project. It is worth doing on one endpoint at a time rather than globally.

Deriving the eager loads from the request

final class OrderQuery
{
    private const RELATION_MAP = [
        'customer' => 'customer',
        'shipment' => 'shipment.carrier',
        'lines'    => 'lines.variant.product',
    ];

    public function relationsFor(FieldSet $fields): array
    {
        return collect($fields->prefixes())
            ->intersectByKeys(self::RELATION_MAP)
            ->map(fn (string $k) => self::RELATION_MAP[$k])
            ->values()
            ->all();
    }
}

// Order::with($query->relationsFor($fields))->paginate();

The map is the important part and the intersection is what makes it safe: a relation name arriving from a query parameter and passed to with() unfiltered is an arbitrary method call on the model, which is a vulnerability rather than a performance problem.

Mapping a requested field prefix to a relation path also lets one field pull in a nested load — asking for shipment.status loads the carrier as well, because the status is derived from it. That indirection has to live somewhere and the map is a better home than a comment.

The resource that declares what it touches

final class OrderResource extends JsonResource
{
    public function toArray($request): array
    {
        return [
            'id'    => $this->id,
            'total' => $this->total->cents(),

            // resolves to a missing value if not loaded —
            // it does NOT trigger a query
            'customer' => new CustomerResource(
                $this->whenLoaded('customer')
            ),
            'shipment' => new ShipmentResource(
                $this->whenLoaded('shipment')
            ),
        ];
    }
}

whenLoaded omits the key entirely rather than loading the relation, which turns an N+1 into a missing field — a failure a client will report rather than one that quietly costs two seconds. Combined with the derived eager loads, the field is present exactly when it was asked for.

The omission is a behaviour change for any client that expected the key to always be there, which is why this needs the field map to be correct rather than being a safety net on its own. The two together are the fix; either alone is half of one.

Counting queries per field combination

/** @dataProvider fieldCombinations */
public function testOrderIndexQueryCount(string $fields, int $max): void
{
    Order::factory()->count(50)->hasLines(4)->create();

    DB::enableQueryLog();
    $this->getJson("/api/orders?per_page=50&fields={$fields}")->assertOk();

    $this->assertLessThan($max, count(DB::getQueryLog()));
}

public function fieldCombinations(): array
{
    return [
        'default'      => ['id,total', 6],
        'customer'     => ['id,total,customer.name', 8],
        'everything'   => ['id,total,customer.name,shipment.status,lines', 12],
    ];
}

Testing the combinations clients actually send requires knowing what they send, which means logging the fields parameter for a week. That log is more useful than the test — it revealed two combinations nobody had anticipated and one client requesting a field that did not exist.

Verifying it worked

$ curl -s -o /dev/null -w '%{time_total}n' 
    '/api/orders?per_page=50&fields=id,total,customer.name,shipment.status'
0.211        # was 2.104

$ vendor/bin/phpunit --filter QueryCount
Tests: 3 passed

# and the production log, a week later
$ grep -c 'orm.lazy_load' /var/log/app/*.log
0

# 154 queries → 7.

The zero lazy-load warnings across a week of production traffic is the assertion that the field map is complete, and it is stronger than any test because it covers the combinations nobody thought of. It is also the check that will detect the next regression, which is why the warning stays on permanently.

What this costs

A map between field prefixes and relation paths, which is a coupling somebody has to maintain and which will drift when a resource gains a field. The lazy-loading warning is what catches the drift, so the two are a pair — adopting the map without the warning produces a silent regression the first time somebody adds a relation to a resource.

The deeper cost is that sparse fieldsets are now genuinely load-bearing for performance rather than a convenience, which means the parameter has to be documented, validated and covered by tests. That is more API surface than most endpoints deserve, and the alternative — separate endpoints for separate shapes — is a different kind of duplication that is easier to reason about and harder to keep consistent.