Hello Dev,

"Laravel, being one of the most popular PHP frameworks today, emphasizes efficient database interactions. One crucial aspect is utilizing subqueries with the `whereIn` and `whereNotIn` clauses. These are powerful tools for filtering data based on sets of values.

There are instances where we need to employ a select query with `whereIn` or `whereNotIn` clauses in Laravel versions 6 through 11. While it's straightforward to write such queries in MySQL, translating them into Laravel's query builder might not be immediately obvious. In this example, I'll demonstrate how to convert a MySQL query into Laravel's query builder syntax, specifically for `whereIn` with a subquery.

Let's start by understanding the tables involved in this example:"

It seems like your question got cut off before listing the tables. However, I can still provide a general guide on how to use subqueries with whereIn and whereNotIn clauses in Laravel, which applies to Laravel versions 6 through 11. Let's assume you have two tables, orders and customers, and you want to filter orders based on whether their IDs exist in a list of customer IDs.

Assuming you have the following tables:

id
customer_id
order_date

id
name
email

Let's say you have a MySQL query that selects orders where the customer_id exists in a list of customer IDs:

SELECT * FROM orders WHERE customer_id IN (1, 2, 3);

And another query that selects orders where the customer_id does not exist in a list of customer IDs:

SELECT * FROM orders WHERE customer_id NOT IN (1, 2, 3);

To achieve the equivalent of the first SQL query in Laravel, you can use the whereIn method along with a subquery:

 
$customerIdList = [1, 2, 3]; // List of customer IDs

$orders = DB::table('orders')
    ->whereIn('customer_id', function ($query) use ($customerIdList) {
        $query->select('id')->from('customers')->whereIn('id', $customerIdList);
    })
    ->get();

Similarly, for the second SQL query, you can use the whereNotIn method:

 
$customerIdList = [1, 2, 3]; // List of customer IDs

$orders = DB::table('orders')
    ->whereNotIn('customer_id', function ($query) use ($customerIdList) {
        $query->select('id')->from('customers')->whereIn('id', $customerIdList);
    })
    ->get();

Subquery: The anonymous function passed to whereIn or whereNotIn acts as a subquery. It specifies the condition for selecting IDs from the customers table.
Using Variables: The use keyword is used to import variables from the outer scope into the closure's scope. This allows you to pass the $customerIdList array to the subquery.
Retrieving Results: Finally, calling get() executes the query and retrieves the results.

These examples demonstrate how to translate MySQL queries involving IN and NOT IN with subqueries into Laravel's query builder syntax, providing a powerful tool for filtering data based on conditions defined within subqueries.

Thank you for your encouragement! If you have any questions or need further assistance, feel free to ask. I'm here to help!