How to write a rule to validate that a combination of multiple columns must be unique in Laravel 6, PHP?

Question

Let's say you have a users table with the column email and email must be unique per user, you can use the following validation rule in Laravel.

[
    'email' => 'unique:users,email,' . $user->id,
]

In my case, my users table looks like following

id
email
type

For my use case, the combination of email and type must be unique, which means that email can exist more than one time, and so does type. But the combination of email and type must be unique. Desired result looks like following

id       email                type
 1       A@mail.com           1    // OK! unique
 2       A@mail.com           2    // OK! unique
 3       B@mail.com           1    // OK! unique
 4       B@mail.com           2    // OK! unique
 5       A@mail.com           1    // NOT OK! already exists with id = 1.
 6       B@mail.com           2    // NOT OK! already exists with id = 4.

How to write a rule to validate that a combination of email and type must be unique in Laravel, PHP, to prevent record with id 5 and 6 from being inserted into database?

Possible duplicate of [Laravel unique validation on multiple columns](https://stackoverflow.com/questions/50349775/laravel-unique-validation-on-multiple-columns) — Salim Djerbouh, Oct 01 '19 at 14:06
I'd handle this at the database level, personally, and catch unique key errors. — ceejayoz, Oct 01 '19 at 14:54

score 0 · Answer 1 · answered Oct 01 '19 at 14:24

The Laravel's unique validation rule does not handle well field combinations as unique. So the best option for you is to implement a custom validation method or class with a custom query. Probably the closure option would be the simplest to implement (but you need the modified user instance):

function ($attribute, $value, $fail) use ($user) {
  $exists = User::where('users.email', $value)
    ->where('users.type', $user->type)
    ->where('users.id', '!=', $user->id)
    ->count();

  if ($exists) {
    $fail($attribute . ' is invalid.');
  }
}

If you receive both fields in the request (both type and e-mail), you can look at the solution linked by Caddy DZ in the comments, but I think that's not your case.

score 0 · Answer 2 · answered Oct 01 '19 at 14:49

Have found some compact solution. This works for me for both create and update.

[
    'column_1' => 'required|unique:TableName,column_1,' . $this->id . ',id,colum_2,' . $this->column_2
]

Note: tested in Laravel 6.

I did put this answer in another question (Laravel unique validation on multiple columns) of someone else.

How to write a rule to validate that a combination of multiple columns must be unique in Laravel 6, PHP?

2 Answers2