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

Hebetude

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

Descend

That seemed to work for the email being unique, but then how do I chain on the required + email validation I had on before? – Hebetude

@tallent123 you can use separtely 'email' => 'required' and then this – Descend

states there is a duplicate array key if I do this – Hebetude

@tallent123 You can pass closure and array of validations too. Let me update he answer – Descend

i get this now Method Illuminate\Validation\Validator::validateRequired|email does not exist. – Hebetude

@tallent123 which version you are using? – Descend

I'm using Lumen (5.6.1) (Laravel Components 5.6.*) – Hebetude

Let us continue this discussion in chat. – Descend

Rule::unique('App\Models\User') add your table name like Model. laravel.com/api/9.x/Illuminate/Validation/… – Ankylose

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

Karrykarst

awesome, simple and works! – Lyophobic

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
}

Synchronic

How does it know what it is the company_id I am wishing to exclude though? – Hebetude

Even if I change the id of the company it says it's already being taken. Seems like that's doing a global search the email? – Hebetude

it knows that the ID belongs to the table given in the rule in your case its company_users , so what it does it selects all the email from that table and excludes the row that has the ID equals to what u wanted to exclude then it counts how many rows – Synchronic

The structure is | id | company_id | email_address | password | this doesn;'t seem to work – Hebetude

are those the columns of the company_users? – Synchronic

they are the columns yeah – Hebetude

okay I guess I got what you want to do, you're not making it based on the ID but rather on the company_id which is not a primary key, to make it work you should use a query rather than a rule, I will edit my question to show u how – Synchronic

Answer 4

If you want to use same method inside the FormRequest class for the request, then you can use it as follows,

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

Sudoriferous

Answer 5

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 . "'"

Mammary

Unknown column '6' in 'where clause' (SQL: select count(*) as aggregate from company_users` where email_address = [email protected] and 6 <> company_id` – Hebetude