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

FULL STACK DEV · Accepted Answer · 2018-03-10T17:54:12.250

31

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

edited Mar 10 '18 at 17:54

answered Mar 10 '18 at 17:45

FULL STACK DEV

15,207
5
46
66

That seemed to work for the email being unique, but then how do I chain on the required + email validation I had on before? – Mar 10 '18 at 17:47
@tallent123 you can use separtely 'email' => 'required' and then this – FULL STACK DEV Mar 10 '18 at 17:50
states there is a duplicate array key if I do this – Mar 10 '18 at 17:51
@tallent123 You can pass closure and array of validations too. Let me update he answer – FULL STACK DEV Mar 10 '18 at 17:52
i get this now `Method Illuminate\Validation\Validator::validateRequired|email does not exist.` – Mar 10 '18 at 17:57
@tallent123 which version you are using? – FULL STACK DEV Mar 10 '18 at 17:58
I'm using `Lumen (5.6.1) (Laravel Components 5.6.*)` – Mar 10 '18 at 17:59
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/166595/discussion-between-laravel-levaral-and-tallent123). – FULL STACK DEV Mar 10 '18 at 17:59
Rule::unique('App\Models\User') add your table name like Model. https://laravel.com/api/9.x/Illuminate/Validation/Rule.html#method_unique – Tamil Apr 28 '23 at 05:26

Wreigh · Answer 2 · 2018-03-10T17:58:18.870

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 abc@gmail.com 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

awesome, simple and works! – Alauddin Afif Cassandra Feb 16 '22 at 03:37 — Alauddin Afif Cassandra, Feb 16 '22 at 03:37

Sletheren · Answer 3 · 2018-03-10T17:47:53.750

6

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
}

edited Mar 10 '18 at 17:47

answered Mar 10 '18 at 17:36

Sletheren

2,435
11
25

How does it know what it is the company_id I am wishing to exclude though? – Mar 10 '18 at 17:37
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? – Mar 10 '18 at 17:38
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 – Sletheren Mar 10 '18 at 17:38
The structure is `| id | company_id | email_address | password |` this doesn;'t seem to work – Mar 10 '18 at 17:39
are those the columns of the `company_users`? – Sletheren Mar 10 '18 at 17:40
they are the columns yeah – Mar 10 '18 at 17:42
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 – Sletheren Mar 10 '18 at 17:44

Marwelln · Answer 4 · 2018-03-10T17:45:51.600

0

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

edited Mar 10 '18 at 17:45

answered Mar 10 '18 at 17:40

Marwelln

28,492
21
93
117

`Unknown column '`6`' in 'where clause' (SQL: select count(*) as aggregate from `company_users` where `email_address` = email@email.com and ```6``` <> company_id` – Mar 10 '18 at 17:43

score 0 · Answer 5 · edited May 09 '23 at 17:12

0

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);
})],

edited May 09 '23 at 17:12

Adrian Mole

49,934
160
51
83

answered May 09 '23 at 13:50

Mitesh Tandel

3
4

Laravel | Unique validation where clause

5 Answers5

Linked