使用'whereNotIn'的查询生成器抛出错误

Question

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?

Answer 1

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'])