Exists Rule to Avoid N+1 Query Issue in Array Validation

[Jagadish056]

I'm trying to validate an array of id values in a Laravel form request, but I want the exists check to happen only after all other validation rules have passed. This way, I can ensure that database queries for the exists validation are executed last, which should help improve efficiency.

Here’s my current rules() method:

public function rules(): array
{
    return [
        'id' => ['nullable', 'array'],
        'id.*' => ['required', 'integer', 'exists:users,id']
    ];
}

The problem with this approach is that it creates an N+1 query issue, as each id in the array triggers a separate exists query. To optimize this, I want to defer the exists check to the end of the validation process, so I can perform a single database query for all id values at once.

My idea is to initially validate each id as an integer, then perform the exists check at the end, like this:

public function rules(): array
{
    return [
        'id' => ['nullable', 'array'],
        'id.*' => ['required', 'integer'],
        'id' => ['exists:users,id']
    ];
}

To handle this, I’m considering using an after() hook in the form request to check all id values together, ensuring a single database query only if all other rules are pass.

public function after(): array
{
    return [
        function (Validator $validator) {

            $values = array_unique($validator->validated()['id'] ?? []);

            $query = User::query()->whereIn('id', $values);

            if ($values && $query->count() !== count($values)) {
                $validator->errors()->add('id', 'One or more ID are invalid.');

                Flash::validationException($validator)->push();
            }
        }
    ];
}

Is there a way in Laravel to delay the exists validation rule like this?

Or, is there a more efficient approach to avoid the N+1 query issue?

[macropay-solutions]

Have you tried creating a custom rule?

[Jagadish056]

Yes, exactly! @henzeb, you’ve got the idea.

[bulletproof-coding]

@Jagadish056 bail and stopOnFirstFailure does not work for this scenario? https://laravel.com/docs/11.x/validation#rule-bail

[Jagadish056]

@marius-mcp Nope! Why? Try this 53417 on validation.

[bulletproof-coding]

@Jagadish056 to avoid autocasting strings to ints, you must use integer rule before the query happens.

[henzeb]

Yes, but he wants to check if all id's are integers before he checks if all id's are in the database... That is why bail and stop on first failure won't work.

[bulletproof-coding]

@Jagadish056 Just as @macropay-solutions suggested, you can create a custom object rule that will do what you want in the laravel way.

<?php
 
namespace App\Rules;
 
use Closure;
use Illuminate\Contracts\Validation\ValidationRule;
 
class ExistUserId implements ValidationRule
{
    /**
     * Run the validation rule.
     */
    public function validate(string $attribute, mixed $value, Closure $fail): void
    {
        if (null === $value)) {
            return;
        }

        if (!is_array($value) || [] === $value) {
            $fail('The :attribute must be array not empty.');
            return; // I am not sure if fail closure will throw or not.
        }

        if ($value !== \array_filter($value, fn (mixed $value): bool => is_int($value))) {
            $fail('The :attribute array must contains only integers.');
            return; // I am not sure if fail closure will throw or not.
        }

        if (Users::query()->whereIn('id', $values)->count() !== count(array_unique($values))) {
            $fail('The :attribute array must contains only valid user ids.');
        }
    }
}


public function rules(): array
{
    return [
        'id' => [new ExistUserId()],
    ];
}

https://laravel.com/docs/11.x/validation#using-rule-objects

The above is valid for L >=9

For 8 https://laravel.com/docs/8.x/validation#using-rule-objects you would need 'bail' + as many rules as messages.

[dazza-dev]

I faced the same problem, and since there wasn't a solution available, I decided to create a package: https://github.com/dazza-dev/Laravel-Batch-Validation 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.