CODEWITHSUNDEEP
phpmysqllaravellaravel-5query-builder
Juliver Galleto
Juliver Galleto

Reputation: 9047

query builder using 'whereNotIn' throws error

I'm trying to fetch records with an array of exceptions, here's what I tried (refer below)

$users_nowishlist = DB::table('employee')
    ->join('users', 'users.employee_id', '=', 'employee.employee_id')
    ->where('has_wishlist', '=', "0")
    ->whereNotIn('employee_id', ['MMMFLB003', 'guest_01', 'guest_02', 'guest_03'])
    ->where('employment_status', '=', 'ACTIVE')
    ->get();

so in this line was my records filter, means only records that does not equal to any of those 'employee_id' from the exceptions array will be return (refer below)

->whereNotIn('employee_id', ['MMMFLB003', 'guest_01', 'guest_02', 'guest_03'])

but instead I got this error (refer below):

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'employee_id' in where clause is ambiguous (SQL: select * from employee inner join users on users.employee_id = employee.employee_id where has_wishlist = 0 and employee_id not in (MMMFLB003, guest_01, guest_02, guest_03) and employment_status = ACTIVE)

any ideas, help please?

Upvotes: 1

Views: 70

Answers (2)

user2099451
user2099451

Reputation: 647

->whereNotIn('employee.employee_id', ['MMMFLB003', 'guest_01', 'guest_02'])

when using join , these errors are expected if you have two fields have the same name in the tables you join between, so always try to fetch them like this 

table_name.field_name

Upvotes: 2

Fabio Antunes
Fabio Antunes

Reputation: 22882

This happens because when you are doing the join there are two columns with the same name.

That's why on your join you prefix the employee_id with users. and employee.

Now on your whereNotIn you also have to prefix it, so the query engine knows which table column you are trying to reference. So you only have to add the prefix in your whereNotIn clause:

->whereNotIn('employee.employee_id', ['MMMFLB003', 'guest_01', 'guest_02', 'guest_03'])

Upvotes: 2

Related Questions