How to Use Laravel Eloquent WHEREIN Query (With Example)

When working with Laravel’s Eloquent ORM, one of the most useful features you’ll come across is the whereIn method. If you’ve ever needed to filter your database results based on a set of values, then whereIn is your go-to tool. It makes querying your database faster and easier, saving you from writing multiple where conditions.

In this blog, we’ll break down how Laravel development experts use the whereIn method, with clear examples to help you see exactly how to use it in your Laravel projects.

What is Laravel Eloquent WHEREIN Query?

The Laravel Eloquent whereIn method allows you to filter your database queries based on a list of values in a single column. It’s equal to the SQL IN operator. This functionality is helpful for retrieving records that match specific criteria from a defined set of options.

How Does Laravel Eloquent WHEREIN Query Work?

• Specify Column and Values: You provide the column name as per your preference for comparing and an array containing the list of values to check for. For example, whereIn(‘id’, [1, 2, 3]) would find records where the id column is equal to 1, 2, or 3.
• Optional Operator: The whereIn method accepts an optional third parameter for specifying the operator (‘and‘ or ‘or‘). By default, it uses ‘and‘, meaning the retrieved records must have values matching all the provided values in the array. Setting it to ‘or‘ would return records with values matching any of the values in the array.
• Database Agnostic: This method is database agnostic, it works consistently across different database management systems supported by Laravel.

The whereIn method in Eloquent provides a convenient way to filter and optimize Laravel database queries based on multiple values for a specific column. By understanding its usage and optional parameters, you can create more precise Eloquent queries in your Laravel applications.

How to Use Laravel Eloquent WHEREIN Query?

The whereIn method in Laravel Eloquent provides a straightforward approach to filter your database queries based on a list of values for a particular column.

Understanding Syntax of whereIn

whereIn(string $column, mixed $values, string $boolean = 'and', bool $not = false)

• First parameter: Column name (string).
• Second parameter: Array of values (mixed).
• Third parameter (optional): Operator, either ‘and’ or ‘or’ (default is ‘and’).
• Fourth parameter (optional): Boolean for strict comparison, where true mimics the behavior of whereNotIn().

Step 1: Import the Model

To use the whereIn method in your Laravel Eloquent queries, you first need to import the model class. It represents the database table you want to work with. This step ensures that the model’s methods and properties are available for building your query. Here’s how to import the model:

1. Use the use Statement. Employ the use statement at the beginning of your code file (a controller or a model itself) to import the desired model class.

<?php
use App\Models\User; // Assuming your model is named User

2. Specify the Model Path. Within the use statement, provide the full namespace path to your model class. This typically follows the convention App\Models\<ModelName>, where <ModelName> is the actual name of your model (e.g., User, Product, Post).

3. Model Class Availability. Once you’ve imported the model using the use statement, you can use the model class name directly within your code to interact with the database table it represents.

<?php
use App\Models\User;
$user = User::find(1); // Assuming you have a User model
// You can now use the User model's methods and properties

By following these steps, you successfully import the model and lay the foundation for constructing your Eloquent query with the whereIn method.

Step 2: Build the Query

After importing the model, it’s time to create the Eloquent query object that will be used to filter your database records. Here’s how to build the query:

1. Incorporate the Model Query. Start by creating a new instance of the query builder associated with your imported model class. You can achieve this using the static method query() on the model class itself.

$users = User::query();

This line creates a query object designed to interact with the users table represented by the User model.

2. Chaining Methods. Laravel Eloquent leverages a method chaining approach for building queries. You can chain various methods onto the query object to progressively refine your filtering criteria.

3. Optional Starting Point. While not essential for whereIn, you can optionally use other Eloquent methods. It could be where or select before using whereIn to further restrict or specify the columns to retrieve in your query.

Example:

$activeUsers = User::query()
                   ->where('active', true); // Filter by active users (optional)
                   ->whereIn('id', [1, 3, 5]); // Filter by specific IDs

In this example, we first filter for active users (optional) and then use whereIn to filter the results further based on specific IDs.

Once these steps are covered, you’ll be done building the initial query object using Laravel’s Eloquent syntax, ready for further improvement with the whereIn method.

Step 3: Specify the Column and Values

This step focuses on utilizing the whereIn method within your Eloquent query to define the filtering criteria. Here’s how to specify the column and values:

1. whereIn Method. The core of this step involves using the whereIn method on the query object we built in Step 2. It takes three arguments to define the filtering logic.

$users = $users->whereIn(/* arguments */);

2. Column Name (1st Argument). The first argument is a string representing the name of the column in your database table that you want to filter by. Ensure it matches the actual column name in the table.

$users->whereIn('id', /* ... */); // Filter by the 'id' column

3. Value Array (2nd Argument). The second argument is an important part of the whereIn clause. It’s an array containing the list of values you want to match against the specified column.

$userIDs = [1, 3, 5];
$users->whereIn('id', $userIDs); // Find users with ID 1, 3, or 5

Optional Arguments:

4. Operator (3rd Argument). The third argument allows you to specify the operator for comparison within the IN clause. The default is ‘and’, meaning a record must match all values in the array. You can change it to ‘or’ for at least one value match.

5. Strict Comparison (4th Argument). The fourth argument enables strict comparison. By default, non-strict comparison is used. Setting this to true ensures type-safe comparisons (recommended).

$userIDs = [1, 3, 5]; // Array of IDs to match
// Find users with ID 1, 3, or 5 (default 'and' operator)
$users = $users->whereIn('id', $userIDs);
// Find users with ID 1 or 3 (using 'or' operator)
$users = $users->whereIn('id', $userIDs, 'or');
// Find users with ID 1 as an integer (strict comparison)
$users = $users->whereIn('id', [1], 'and', true);

With the completion of these arguments and following the steps, you can configure the whereIn method to filter your Eloquent queries based on specific values within a chosen column.

Step 4: Execute the Query

The final step involves executing the Eloquent query you’ve created with the whereIn method. This retrieves the filtered data from the database.

• Execution Methods. After defining your filtering criteria with whereIn (and other methods), you can execute the query using various methods depending on your desired outcome. These methods are called on the query object. Common Execution Methods:
  • get(). Retrieves all matching records as a collection of model instances. This is ideal for fetching all filtered data.
  • first(). Fetches the first matching record as a model instance. Use this when you only need the first record that meets the criteria.
  • count(). Returns the total number of matching records. This is useful for counting filtered results without retrieving the actual data.

Other methods like paginate() or custom methods provided by your model can also be used for specific scenarios.

Example:

$users = User::query()
           ->whereIn('id', [1, 3, 5])
           ->get(); // Fetch all users with ID 1, 3, or 5
$firstActiveUser = User::query()
                    ->where('active', true)
                    ->whereIn('id', [2, 4, 6])
                    ->first(); // Get the first active user with ID 2, 4, or 6
$totalInactiveUsers = User::query()
                       ->where('active', false)
                       ->count(); // Count the number of inactive users

Ensure you choose the appropriate execution method based on your needs. Retrieving all results using get() for very large datasets might not be optimal due to memory considerations.

Understanding the available execution methods, you can retrieve the filtered data resulting from your Eloquent query with whereIn.

How to Optimize Laravel Eloquent WHEREIN Query?

While whereIn offers a convenient way to filter data, there are situations where you might want to optimize your queries for better performance. Here are some strategies to consider:

1. Index the Target Column

The most important optimization is to ensure the column you’re filtering by (specified in the first argument of whereIn) has an appropriate database index. The indexes improve query execution speed when filtering based on specific column values.

2. Limit the Number of Values in the Array

The size of the value array (second argument in whereIn) can impact performance. If possible, try to keep the number of values within a reasonable limit. For very large datasets, consider alternative approaches.

3. Consider Alternatives for Large Datasets

For large datasets and a significant number of values in the IN clause, whereIn might not be the most efficient approach. Explore options like database-specific functions or chunking the query to process data in smaller batches. Consulting a Laravel development company can be helpful for determining the best strategy for your specific use case.

4. Use whereIntegerInRaw

If you’re filtering by integer values (INT column type), Laravel provides the whereIntegerInRaw method. This method can sometimes generate more efficient SQL compared to whereIn, especially for large datasets.

5. Reduce Returned Columns

If you only need a specific subset of columns from the filtered results, use the select method on your query object before whereIn to specify the desired columns. This reduces the amount of data transferred from the database, improving performance, especially for large datasets.

Additional Tips:

• Use caching mechanisms (if applicable) to store frequently used query results and reduce database load.
• Profile your queries using Laravel’s debugging tools to identify bottlenecks and optimize accordingly.

Remember, the optimal approach depends on the size of your dataset, number of values in the IN clause, and the specific database engine you’re using. Consider these techniques and consult with experienced Laravel developers if you need further assistance. They can help you in fine-tuning your Eloquent queries for maximum efficiency.

FAQs About Laravel Eloquent WHEREIN Query

Conclusion

The whereIN query in Laravel Eloquent is a handy tool that makes it easy to filter records based on multiple values in a single column. It’s especially useful when you need to retrieve a set of records quickly without writing multiple queries or complex conditions.

Using whereIN can not only improve the readability of your code but also help in boosting query performance. Whether you’re dealing with a small dataset or thousands of records, this method keeps your database interactions efficient and your code neat.

Need help building or optimizing your Laravel applications? Our Laravel development experts can help you ensure your applications function at their best.