Laravel | Unique validation where clause

Question

I am trying to validate the input of a email address that exists but only when the company_id is the same as the company_id which is passed in with the request.

I am getting this error...

SQLSTATE[42S22]: Column not found: 1054 Unknown column '1' in 'where clause' (SQL: select count(*) as aggregate from company_users where email_address = myemail.com and 1 <> company_id)

I have read online and the way to do it is to associate the table and the column inside of the validation which is what I am doing.

This is my current code...

required|email|unique:company_users,email_address,company_id,' . $request->company_id

Answer 1

Here is a rough idea with this you can achieve what you

You can use Rule class to customize the validation rule for you.

'email' => ['required', 'string', 'email', 'max:191',Rule::unique('users')->where(function ($query) use ($request) {
    return $query->where('company_id', $request->company_id);
})],

Hope this helps

Answer 2

This should answer your question.

'required|email|unique:company_users,email_address,NULL,id,company_id,' . $request->company_id

This means, the email must be unique while ignoring a row with an id of NULL and where its company_id is the same as $request->company_id.

The except parameter ignores rows to compare against the validation. E.g., an email address of [email protected] has already been taken by user 1 but you did this:

'unique:company_users,email_address,1'

It will ignore user with id of 1. So inputting the same email will still pass since the existing user with the same email has been ignored.

Which is not the same in your case, because you just dont want to ignore a special row, but you want to ignore a row based on a condition. So that is how this answer helps you, by adding more field validations after the ignore parameter.

You might want to take a look at this: laravel 4: validation unique (database) multiple where clauses

Answer 3

It has to be like:

required|email|unique:company_users,email_address,' . $request->company_id

The format is:

unique:table,column,'id to exclude'

Or if you want to validate based on a column you can do a separate query:

$exists = Model::where('company_id','!=',$request->company_id)->where('email',$request->email)->count()
if($exists) {
  // return an error or a validation error
}

Answer 4

Make $request->company_id a string by adding ' single quotes around your variable. Otherwise MySQL will think is a column in your table.

required|email|unique:company_users,email_address,company_id,"'" . (int) $request->company_id . "'"