Laravel Unique Validation for Multiple Columns

Laravel Validation already has a Unique rule to check if some record exists in the database. But what if you want a combination of TWO fields to be unique? Let me demonstrate.

The unique rule is commonly used for a single field like this:

// ...
 
public function rules(): array
{
return [
'user_id' => ['required', 'integer'],
'date' => [
'required',
'unique:expenses,date'
],
'amount' => ['required', 'numeric'],
];
}
 
// ...

And it works to validate a single field:

But what if you must validate the Date and User ID to be unique together? In other words, a user can have only one record on a particular date.

I see many developers don't use Validation rules for this and write this check manually:

app/Http/Controllers/ExpenseController.php

use App\Http\Requests\StoreExpenseRequest;
use App\Models\Expense;
 
// ...
 
public function store(StoreExpenseRequest $request)
{
$hasExpenseSavedForUser = Expense::query()
->where('date', $request->input('date'))
->where('user_id', $request->input('user_id'))
->exists();
 
if ($hasExpenseSavedForUser) {
return back()->withErrors([
'date' => 'Expense already saved for this user on this date'
]);
}
 
Expense::create($request->validated());
 
return to_route('expenses.index');
}

It works as expected, and Validation will be triggered if the Expense already exists for the User on the same Date:

But what if I told you that you could use Laravel Validation to extend the unique rule?

You can use Rule::unique() and add ->where() condition, making it work for multiple fields:

app/Http/Requests/StoreExpenseRequest.php

use Illuminate\Validation\Rule;
 
// ...
 
public function rules(): array
{
return [
'user_id' => ['required', 'integer'],
'date' => [
'required',
'unique:expenses,date',
Rule::unique('expenses', 'date')->where('user_id', $this->input('user_id')),
],
'amount' => ['required', 'numeric'],
];
}
 
public function messages()
{
return [
'date.unique' => 'Expense for selected user on selected date already exists.',
];
}

With that rule in place, we can remove the code from our Controller and let the Form Request Validation handle it:

app/Http/Controllers/ExpenseController.php

use App\Http\Requests\StoreExpenseRequest;
use App\Models\Expense;
 
// ...
 
public function store(StoreExpenseRequest $request)
{
$hasExpenseSavedForUser = Expense::query()
->where('date', $request->input('date'))
->where('user_id', $request->input('user_id'))
->exists();
 
if ($hasExpenseSavedForUser) {
return back()->withErrors([
'date' => 'Expense already saved for this user on this date'
]);
}
 
Expense::create($request->validated());
 
return to_route('expenses.index');
}
 
// ...

It will result in the same validation message displayed to the User:

The Rule::unique() class can accept multiple conditions using the where() method. You can read more about it in the Laravel Docs.

avatar

Now that this possible, I think I need to try the same Models for relationships.

Forexample OrderedService table records belongsTo LabSevices and OtherServices

avatar

Nice tutorial! In an MVC framwork made by miself I create a kind of specific rule for a combined validation, called unique_with. But talking about laravel I have a problem about this rule: how to seed database with a unique combined foreing_key?

I have a table like this:

    $table->foreignIdFor(User::class)->constrained();
    $table->smallInteger('year');
    $table->tinyInteger('member_type');
    $table->char('card_type', 1);
    $table->string('card_number', 20);
    $table->integer('cost');
    $table->timestamps();
    $table->unique(['user_id', 'year', 'card_type']);
avatar

Not sure what would be inside of your seeders, but I would make sure that on the seeder level there would not be unique values for your combination.

DB/Validation checks are just additional "fallback" checks but if you are in control to improve your data beforehand, I would do exactly that.

avatar

Thank you Povilas for the reply. I agree with you: my problem is exactly how to seed the table respecting the composite unique key. I don't know how to build the factory/seed

avatar

How would I approach this validation in Filament? for a composite index?

avatar

To do this in Filament - you can follow this documentation section:

https://filamentphp.com/docs/3.x/forms/validation#unique

It's the same as in our article, except you have to set it on the field itself and not on the request

Like our articles?

Become a Premium Member for $129/year or $29/month
What else you will get:
  • 52 courses (943 lessons, total 46 h 42 min)
  • 75 long-form tutorials (one new every week)
  • access to project repositories
  • access to private Discord

Recent Premium Tutorials