Laravel Eloquent: ‘IN’ and ‘NOT IN’ operators

Overview

Laravel, as one of the most prominent PHP frameworks, provides a robust ORM called Eloquent for interacting with databases. Eloquent allows developers to write database queries in a clean, fluent API while maintaining readability and simplicity. A common requirement when querying databases is to filter results where a column’s value is within a set of values, or not within that set. This is where the ‘IN’ and ‘NOT IN’ SQL operators come into play. In this tutorial, we will explore how to use these operators in Eloquent queries with various examples.

Understanding ‘IN’ and ‘NOT IN’ SQL Operators

Before diving into Eloquent specifics, let’s briefly review the ‘IN’ and ‘NOT IN’ operators in SQL. The ‘IN’ operator allows you to specify multiple values in a WHERE clause. It is shorthand for multiple OR conditions. Conversely, the ‘NOT IN’ operator is used to exclude values.

SELECT * FROM table_name WHERE column_name IN (value1, value2, ...);
SELECT * FROM table_name WHERE column_name NOT IN (value1, value2, ...);

Basics of ‘IN’ in Eloquent

Laravel’s Eloquent provides the whereIn method to query using the ‘IN’ operator. The first argument is the name of the column, and the second is an array of values you want to include in the results.

// Assume you have a 'Post' model for your 'posts' table.
$posts = Post::whereIn('status', ['published', 'draft'])->get();

This translates to “SELECT * FROM posts WHERE status IN (‘published’, ‘draft’)”. The get method then retrieves the matched records.

Working with ‘NOT IN’ in Eloquent

The counterpart for the ‘IN’ query in Eloquent is the whereNotIn method, which filters out records where the specified column’s value is in the given array.

$users = User::whereNotIn('id', [1, 2, 3])->get();

This will get all users except those with an ID of 1, 2, or 3.

Advanced Usage

Now that we’ve covered the basics, let’s look at some advanced usage examples. Eloquent queries can be chained, allowing for complex conditions.

Combining ‘IN’ and ‘NOT IN’ with Other Conditions

Eloquent allows combining multiple conditions, which lets you create precise queries.

$posts = Post::whereIn('status', ['published', 'archived'])
           ->whereNotIn('author_id', [10, 20])
           ->where('created_at', '>', now()->subYear())
           ->get();

Using ‘IN’ and ‘NOT IN’ with Relations

Eloquent relationships can also take advantage of the ‘IN’ and ‘NOT IN’ clauses. For instance:

$users = User::with(['posts' => function ($query) {
    $query->whereIn('status', ['published']);
}])->get();

Dynamic ‘whereIn’ Using Eloquent’s when() Method

Eloquent has a when method that executes queries conditionally. This is useful when you have dynamic conditions based on user input.

$status = request('status'); // Assume this comes from user input.
$posts = Post::when($status, function ($query, $status) {
             return $query->whereIn('status', $status);
         })->get();

Subqueries and ‘IN’/ ‘NOT IN’ Operations

More complex scenarios might require subqueries combined with ‘IN’ or ‘NOT IN’ clauses. Here’s how you can use subqueries with these operators in Laravel Eloquent:

$activeUserIds = User::select('id')->where('active', 1);
$usersWithActivePosts = User::whereExists(function ($query) use ($activeUserIds) {
                           $query->select(DB::raw(1))
                                 ->from('posts')
                                 ->whereRaw('posts.user_id = users.id')
                                 ->whereIn('posts.user_id', $activeUserIds);
                        })->get();

Use cases

Now, let’s apply what we’ve learned to some real-world situations:

Filtering a List of Products

Imagine we have an e-commerce application where we need to display products based on category selection. We can use the ‘IN’ operator to filter these products:

$categoryIds = [1, 2, 3]; // Categories selected by the user
$products = Product::whereIn('category_id', $categoryIds)->get();

Excluding Certain Users from a Newsletter List

When sending out newsletters, we might want to exclude users who have opted out.

$excludedUserIds = User::where('newsletter_opt_out', true)->pluck('id');
$subscribers = User::whereNotIn('id', $excludedUserIds)->get();

Conclusion

In this tutorial, we’ve explored how to use the ‘IN’ and ‘NOT IN’ operators in Laravel Eloquent, from basic to more complex and dynamic queries. We’ve seen how these operators can be easily utilized to create flexible and efficient database queries that keep our applications running smoothly. Whether filtering results or excluding specific records, ‘IN’ and ‘NOT IN’ are indispensable tools in an Eloquent developer’s arsenal.