How Can I Efficiently Use Subqueries with `WHERE IN` in Laravel to Filter Data?

Subqueries with the 'WHERE IN' Clause in Laravel

In Laravel, creating a subquery within the 'WHERE IN' clause is an efficient way to filter results based on data from a related table. Let's examine a specific scenario:

You're tasked with fetching products based on their categories. The query should include columns such as ID, name, and status, and the products should belong to specific categories with IDs '223' or '15'.

Original Query:

SELECT 
    `p`.`id`,
    `p`.`name`, 
    `p`.`img`, 
    `p`.`safe_name`, 
    `p`.`sku`, 
    `p`.`productstatusid` 
FROM `products` p 
WHERE `p`.`id` IN (
    SELECT 
        `product_id` 
    FROM `product_category`
    WHERE `category_id` IN ('223', '15')
)
AND `p`.`active`=1

Laravel Equivalent Using Closure:

Products::whereIn('id', function($query){
    $query->select('paper_type_id')
    ->from(with(new ProductCategory)->getTable())
    ->whereIn('category_id', ['223', '15'])
    ->where('active', 1);
})
->get();

Explanation:

The 'whereIn' function takes a closure as an argument, allowing you to define the subquery within.

• from(): Selects the table from which the subquery will retrieve results.
• select(): Specifies the column to be returned from the subquery.
• whereIn() and where(): Filters the results from the subquery based on the specified criteria.

Benefits of Using Closure:

• Improved readability and maintainability of code.
• Allows for more complex subqueries involving multiple conditions or joins.
• Enhances performance by reducing the number of database calls compared to using multiple joins.

The above is the detailed content of How Can I Efficiently Use Subqueries with `WHERE IN` in Laravel to Filter Data?. For more information, please follow other related articles on the PHP Chinese website!