Laravel WhereIn() and WhereNotIn() with Subquery: A Practical Guide

Laravel's Eloquent ORM and Query Builder make database operations a breeze. Two powerful methods, whereIn() and whereNotIn(), allow you to filter records based on specific conditions. But did you know you can supercharge these methods by using subqueries? Let’s dive in!

What are whereIn() and whereNotIn()?

• whereIn() filters records where a column’s value is in a given list.
• whereNotIn() excludes records where a column’s value is in a given list.

Using Subqueries with whereIn()

Subqueries let you dynamically generate the list of values for filtering. For example, imagine you want to fetch users who have placed orders:

$users = DB::table('users')
    ->whereIn('id', function ($query) {
        $query->select('user_id')
              ->from('orders')
              ->where('status', 'completed');
    })
    ->get();

This query retrieves users whose IDs match those in the orders table with a completed status.

Using Subqueries with whereNotIn()

Similarly, you can exclude records. For instance, fetch users who haven’t placed any orders:

$users = DB::table('users')
    ->whereNotIn('id', function ($query) {
        $query->select('user_id')
              ->from('orders');
    })
    ->get();

This query excludes users who have entries in the orders table.

Why Use Subqueries?

• Dynamic Filtering: Subqueries adapt to changing data.
• Efficiency: Reduce multiple queries into a single, optimized one.
• Flexibility: Handle complex conditions with ease.

By combining whereIn() and whereNotIn() with subqueries, you can create powerful, dynamic queries in Laravel. Whether you’re filtering or excluding records, these techniques help you write cleaner, more efficient code. Start using them today to level up your Laravel skills!