The Laravel Eloquent ORM provides a powerful way to interact with your database concisely and expressively. One of its convenient features is the whereIn method, which allows you to filter your queries based on a list of values in a single column. This functionality is equivalent to the SQL IN operator.
What is whereIn?
The whereIn method allows you to add a “WHERE IN” clause to your database queries. This is useful when you want to retrieve records where a specific column’s value matches any value in a given array. For example, if you have a list of user IDs and you want to get all users with those IDs, you can use whereIn.
Benefits of Using WHEREIN Queries
There are several advantages to using whereIn queries in your Laravel applications:
- Clarity and Readability: By explicitly defining the list of allowed values, your code becomes easier to understand and maintain for yourself and other developers.
- Reduced Errors: whereIn helps prevent typos or syntax errors that can creep in when manually writing SQL statements. Laravel handles the underlying SQL generation, ensuring accuracy.
- Efficiency: whereIn queries can be performant, especially for smaller datasets.
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 of WHEREIN Query:
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 of WHEREIN Query:
whereIn() with Eloquent Models
$ids = [1,2,3,4,5]; $users = User::whereIn(‘id’,$ids)->get();
whereIn() with Query Builder
$names = [‘John’,’Peter’,’Anne’]; $users = DB::table(‘users’)->whereIn(‘name’,$names)->get();
Note: By using the third & fourth parameters of the whereIn() function we can use the functionality of other Laravel functions like whereNotIn(), orWhereIn(), orWhereNotIN().
Example – 1: If we use the third parameter ‘and’ & fourth parameter is true in whereIn() then it works as same as the whereNotIn() function.
$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.
Example – 2: If we use the third parameter ‘or’ & the fourth parameter is false in whereIn() then it works as same as the orWhereIn() function.
$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.
Example – 3: If we use the third parameter ‘or’ & the fourth parameter is true in whereIn() then it works as same as the orWhereNotIn() function.
$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.
Conclusion:
The whereIn method in Laravel Eloquent is a powerful tool for filtering records based on a set of values. Whether you’re dealing with simple queries or complex relationships, whereIn can help you retrieve the data you need efficiently. By understanding how to use whereIn effectively, you can write more expressive and performant database queries in your Laravel applications.
Feel free to experiment with different use cases and combine whereIn with other query methods to suit your needs.
Take help from Laravel Developers to handle queries efficiently for your Laravel application.
Happy Coding!