DEV Community

Michael
Michael

Posted on

3 1

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'],
]);
Enter fullscreen mode Exit fullscreen mode

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)],
]);
Enter fullscreen mode Exit fullscreen mode

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.

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (3)

Collapse
 
dydemin profile image
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.

Collapse
 
michaelvickersuk profile image
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.

Collapse
 
dazza-dev profile image
Andres Daza

I faced the same problem, and since there wasn't a solution available, I decided to create a package: github.com/dazza-dev/Laravel-Batch... that solves it. This is the first version, so it may have some bugs, but it would be great if you could try it out and help improve it further. It works well for my use case, but I hope it can be helpful to the Laravel community.

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay