Laravel WhereIn()

Many times we have a list of data or array to compare with columns in a single query. So, in SQL we can use the IN() function.

Example in SQL:-

Select * from users where id IN (1,2,3);

But what can we do in Laravel?

Therefore, laravel has introduced the whereIn() function to compare multiple values ​​with columns. It matches a list of data with a column and returns the result if the column contains those values.

Syntax:-

whereIn (string column_name ,mixed $values ,string $boolean = ‘and’ ,bool $not = false)

As you can see in the syntax

• The first parameter accepts a string value that is the name of the column you want to compare values to.
• The second parameter accepts mixed values that can be integer,string etc.
• The third parameter is optional. It accepts string values. Default value is ‘and’. It only accepts ‘or’ & ‘and’ string.
• The fourth parameter is optional. It accepts boolean values. Default value is false.

Example:- whereIn() with Eloquent Models.

$ids = [1,2,3,4,5];

$users = User::whereIn(‘id’,$ids)->get();

Example:- whereIn() with Query Builder.

$names = [‘John’,’Peter’,’Anne’];

$users = DB::table(‘users’)->whereIn(‘name’,$names)->get();

Note:- By using third & fourth parameter of whereIn() function we can use functionality of other laravel functions like whereNotIn(), orWhereIn(), orWhereNotIN().

1. If we use third parameter is ‘and’ & fourth parameter is true in whereIn() then its work as same as whereNotIn() function.
Example:-

$users = User::whereIn(‘id’,[1,2,3],’and’,true)->get();
$users = User::whereNotIn(‘id’,[1,2,3])->get();

Above queries return users that ID is not one,two and three.

2. If we use third parameter is ‘or’ & fourth parameter is false in whereIn() then its work as same as orWhereIn() function.
Example:-

$users = User::whereIn(‘name’,[‘John’,’Peter’])->whereIn(‘id’,[1,5],’or’,false)->get();
$users = User::whereIn(‘name’,[‘John’,’Peter’])->orWhereIn(‘id’,[1,5])->get();

Above queries return users whose Name is John and Peter OR whose ID is one and five.

3. If we use third parameter is ‘or’ & fourth parameter is true in whereIn() then its work as same as orWhereNotIn() function.
Example:-

$users = User::whereIn(‘name’,[‘John’,’Peter’])->whereIn(‘city’,[‘New York’,’Alabama’],’or’,true)->get();
$users = User::whereIn(‘name’,[‘John’,’Peter’])->orWhereNotIn(‘city',[‘New York’,’Alabama’])->get();

Above queries return users whose Name is ‘John’ and ’Peter’ OR whose City is not New York and Alabama.