How to Optimize Laravel Queries Using Subqueries in WHERE IN Clauses?

Subquery in Laravel WHERE IN Clause

In Laravel, creating a query that retrieves data where the ID field is included in a subquery can be achieved effectively. While a JOIN operation would also suffice, this article focuses on optimizing performance by employing a subquery within the WHERE IN clause.

Consider the following 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

To replicate this query in Laravel, utilize the following code:

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

By incorporating a subquery within the WHERE IN clause, this statement efficiently executes the required search and retrieval operation. This approach is particularly beneficial for performance-sensitive applications where minimizing database calls is crucial.

The above is the detailed content of How to Optimize Laravel Queries Using Subqueries in WHERE IN Clauses?. For more information, please follow other related articles on the PHP Chinese website!