Laravel form validation unique using 2 fields

Question

How can I have a unique validation rule on 2 fields?

a. The application should not allow two people to have the same identical first name and last name.

It is allowed that the users fills in only a first name or only a last name. Because the user may have only one of them.

b. But if the user enters only a first name (Glen), no other person in the table should have the same (first name = 'Glen' and last name = null). another 'Glen Smith' ok.

I tried the following rule. It works great when both fields (first and last name) are not null:

'firstName' => 'unique:people,firstName,NULL,id,lastName,' . $request->lastName

This rule fails on b. when only one field is present.

Any hint?

Answer 1

You can do it if the Validator class isn't required for you:

if(Model::query()->where([
            'column_1' => 'data_1',
            'column_2' => 'data_2'
        ])->exists())
{
    // some code..
}

Answer 2

in my case this works just fine (in controller):

$request->validate([
    'firstName' => 'required|min:3|max:255|unique:people,firstName,NULL,id,lastname,' . $request->input('lastname'),
], [
    'unique' => 'Some message for "unique" error',
]);

Answer 3

This is an extensive answer to this question and how to create Laravel custom validator generally, you can simply copy and paste, and try to understand later: Step 1: Create a provider app/Providers/CustomValidatorProvider.php

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Validator as ValidatorFacade;

/**
 * Provider for custom validators. Handles registration for custom validators.
 * 
 */
class CustomValidatorProvider extends ServiceProvider {

    /**
     * An array of fully qualified class names of the custom validators to be
     * registered.
     * 
     * @var array
     */
    protected $validators = [
        \App\Validators\MultipleUniqueValidator::class,
    ];

    /**
     * Bootstrap the application services.
     *
     * @return void
     * @throws \Exception
     */
    public function boot() {
        //register custom validators
        foreach ($this->validators as $class) {
            $customValidator = new $class();
            ValidatorFacade::extend($customValidator->getName(), function() use ($customValidator) {
                //set custom error messages on the validator
                func_get_args()[3]->setCustomMessages($customValidator->getCustomErrorMessages());
                return call_user_func_array([$customValidator, "validate"], func_get_args());
            });
            ValidatorFacade::replacer($customValidator->getName(), function() use ($customValidator) {
                return call_user_func_array([$customValidator, "replacer"], func_get_args());
            });
        }
    }

    /**
     * Register the application services.
     *
     * @return void
     */
    public function register() {
        //
    }

}

Step 2: Update your app.php in your config folder config/app.php to include your created provider in the provider array

App\Providers\CustomValidatorProvider::class,

Step 3: Create your custom validator, in my case, I am creating multiple unique fields validator app/Validators/MultipleUniqueValidator.php

<?php

namespace App\Validators;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Str;
use Illuminate\Validation\Validator;

/**
 * Multiple field uniqueness in laravel
 */
class MultipleUniqueValidator{

    /**
     * Name of the validator.
     *
     * @var string
     */
    protected $name = "multiple_unique";

    /**
     * Return the name of the validator. This is the name that is used to specify
     * that this validator be used.
     * 
     * @return string name of the validator
     */
    public function getName(): string {
        return $this->name;
    }

    /**
     *
     * @param string $message
     * @param string $attribute
     * @param string $rule
     * @param array $parameters
     * @return string
     */
    public function replacer(string $message, string $attribute, string $rule, array $parameters): string {
        unset($parameters[0]);
        $replacement = implode(", ", $parameters);
        $replacement = str_replace("_", " ", $replacement);
        $replacement = Str::replaceLast(", ", " & ", $replacement);
        $replacement = Str::title($replacement);
        return str_replace(":fields", "$replacement", $message);
    }

    /**
     * 
     * @param string $attribute
     * @param mixed $value
     * @param array $parameters
     * @param Validator $validator
     * @return bool
     * @throws \Exception
     */
    public function validate(string $attribute, $value, array $parameters, Validator $validator): bool {
        $model = new $parameters[0];
        if (!$model instanceof Model) {
            throw new \Exception($parameters[0] . " is not an Eloquent model");
        }
        unset($parameters[0]);
        $this->fields = $parameters;

        $query = $model->query();
        $request = app("request");
        foreach($parameters as $parameter){
            $query->where($parameter, $request->get($parameter));
        }

        return $query->count() == 0;
    }

    /**
     * Custom error messages
     * 
     * @return array
     */
    public function getCustomErrorMessages(): array {
        return [
            $this->getName() => ":fields fields should be unique"
        ];
    }

}

Now you can do this in your request

'ANY_FIELD_CAN_CARRY_IT' => 'required|numeric|multiple_unique:'.YOUR_MODEL_HERE::class.',FIELD_1,FIELD_2,FIELD_3...',

Answer 4

Laravel now allows you to add where clauses into the unique rule.

In your case you could do something like this:

 'firstName' => [
     Rule::unique('people', 'firstName')->where(function ($query) use ($lastName) {
         return $query->where('lastName', $lastName);
     })
 ],

Answer 5

I think you are looking for something like that:

 'unique:table_name,column1,null,null,column2,'.$request->column2.',column3,check3'

Answer 6

The built in unique validator wouldn't really support what you're trying to do. It's purpose is to ensure that a single valid is unique in the database, rather than a composite of two values. However, you can create a custom validator:

Validator::extend('uniqueFirstAndLastName', function ($attribute, $value, $parameters, $validator) {
    $count = DB::table('people')->where('firstName', $value)
                                ->where('lastName', $parameters[0])
                                ->count();

    return $count === 0;
});

You could then access this new rule with:

'firstName' => "uniqueFirstAndLastName:{$request->lastName}"

You'll probably find you might need to tweak your database query a little bit as it's untested.