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

Solution 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

Solution 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

Solution 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
}

Comments

[Admin]

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

[Admin]

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?

[Sletheren]

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

[Admin]

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

[Sletheren]

are those the columns of the company_users?

[Admin]

they are the columns yeah

[Admin]

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

[Sletheren]

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

[Admin]

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

[PULL STACK DEV]

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

[Admin]

states there is a duplicate array key if I do this

[PULL STACK DEV]

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

[Admin]

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

[PULL STACK DEV]

@tallent123 which version you are using?

[Admin]

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

[PULL STACK DEV]

Let us continue this discussion in chat.

[Alauddin Afif Cassandra]

awesome, simple and works!