Array inputs validation and the N+1 query problem in Laravel

The N+1 query problem can crop up in a variety of circumstances, one area that could be surprising to find this problem is when validating a form submission.

Below is a validation rule to ensure an id exists in a corresponding table.

$request->validate([
    'comments.*.status_id' => ['required', 'exists:statuses,id'],
]);

If this was a single status_id input that would be fine, no N+1 problem would exist, but looking closely we can see status_id belongs to an array of inputs as shown by the dot notation. As status_id is an input in the comments array Laravel will perform the same exists query for each element, which could mean an excessive amount of queries.

As always the solution is to do some eager loading and in this case it can be achieve by making use of Rule::in().

use Illuminate\Validation\Rule;

$statuses = Status::pluck('id')->toArray();

$request->validate([
    'comments.*.status_id' => ['required', Rule::in($statuses)],
]);

Laravel will still perform the same validation on each of the comments array inputs but now the statuses table is only queried once, no matter how many elements there are in comments.

Comments

[dydemin]

Would it be a problem if statuses table contains huge amount of records?

In this solution all statuses are selected each time we need to validate input.
It may be better to limit statuses query result with ids from request.

[Michael]

Great question, yes if you anticipate the statuses table containing lots of rows then some optimisation of the query will be beneficial. The same issue applies wherever the N+1 problem occurs, as even when using $model->with() or $model->load() if the tables being eager loaded have lots of rows or columns this could also lead to performance issues.

Your suggestion is a good one, an alternative approach might be to cache statuses and then refresh the cache whenever the statuses are changed, it all depends upon the table size and the use cases.