in this tutorial, We’ll explore different ways to implement Eloquent whereIn in the Laravel application. This method allows you to filter query results based on a set of conditions.
Laravel’s Eloquent ORM (Object-Relational Mapping) is a powerful tool for interacting with databases conveniently.
The whereIn
method is helpful to get records out of an array or collection where a given column’s value matches filter criteria.
What’s whereIn
The whereIn method in Laravel Eloquent provides a convenient way to filter query results based on a set of values within a specified column. The WHEREIN is designed to match a column’s value against an array of conditions.
Syntax:
$emps = Employee::whereIn('column_name', [value1, value2, …])->get();
Parameters are:
Employee: This is the eloquent model name.
column_name: The column name where the value matches any of the values in the provided array.
Return: This method returns records from the employee table where the specified column’s value matches any values in the provided array.
Simple Example
Let’s take a simple example to filter records:
$emps = Employee::whereIn('id', [1, 2, 3])->get();
We are querying the “employees” table, and filtering records where the “id” column matches any of the values in the array [1, 2, 3].
Dynamic WHEREIN Conditions
We can filter multiple values based on condition using whereIn.
$depts = [1, 2]; $emps = Employee::whereIn('dept_id', $depts)->get();
It retrieves employees that belong to any of the selected departments.
WHEREIN for String Values
The WHEREIN is also allows to passing array of string values. Let’s retrieve records of users with specific roles:
$roles = ['admin, 'user]; $emps = Employee::whereIn('role', $roles)->get();
in the above code, we are querying for employees whose “role” column matches any of the values in the $roles
array.
WHEREIN with Subqueries
The whereIn method allows you to dynamically build queries based on user inputs.
$status = [ true ]; $emps = Employee::whereIn('status', $status); if ($request->has('emp_id')) { $emps->where('emp_id', $request->input('emp_id')); } $result = $posts->get();
Mass Updates or Deletions:
You can also do mass updates and deletions using the whereIn method. You can pass multiple ids
to update or delete records from the table.
$emp_ids = [5, 8, 12]; Employee::whereIn('emp_id', $emp_ids)->update(['status' => false]);
in the above code, we are updating bulk records for column 'status'
.
Chaining whereIn with Other Conditions
You can also do the method chaining with the whereIn. You can create more complex queries to filter data.
$dept_ids = [1, 2, 3]; $salary = 5000; $emps = Employee::whereIn('dept_id', $dept_ids) ->where('salary', '>', $salary) ->get();
WHEREIN with Closure Constraints
Laravel Eloquent allows developers to use Closure constraints within the WHEREIN clause. The simple example is:
$users = Employee::whereIn('emp_id', function ($query) { $query->select('emp_id') ->from('dept') ->where('dept_id', '=', 2); })->get();
Also checkout other Laravel 10 tutorials,
- Notification Example Using Laravel 10
- How to use AJAX in Laravel 10 Using Vue.js
- Database Queries with Laravel’s whereNull
Conclusion:
We have explored different scenarios to implement whereIn in your Laravel application. You can use it with dynamic conditions, subqueries, or Closure constraints. This method empowers developers to craft efficient and dynamic database queries.