How to Use Laravel Eloquent WHEREIN

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,

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.