Laravel WhereIn()
How to Make Laravel Eloquent “WHEREIN” Query?
How to Make Laravel Eloquent “WHEREIN” Query?
Laravel WhereIn()
Many times we have array or a list of data to compare with single or multiple 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 how 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(‘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.