How to Implement a Subquery with WHERE IN Clause in Laravel Eloquent?

Subquery with WHERE IN in Laravel

In Laravel, you can perform complex queries using subqueries. A common use case is a subquery with a WHERE IN clause. This article demonstrates how to effectively implement this query type.

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

Using Laravel's Eloquent ORM, you can achieve this query with 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();

In this example, we start by creating a query on the 'Products' model ('Products::whereIn(...)'). The 'whereIn' clause specifies that we want to filter the 'Products' based on the 'id' field, which should be included in the results returned by the subquery.

The subquery itself is defined using an anonymous function ('$query => ...'). This subquery selects the 'paper_type_id' field from the 'product_category' table, which is assumed to be available through a relationship with the 'ProductCategory' model. The table name is obtained using the 'with(...)->getTable()' method.

Within the subquery, we add conditions to filter results based on the 'category_id' field and ensure that the 'active' field is set to 1. These conditions help narrow down the product categories to include in the final results.

By using this approach, you can efficiently retrieve products that meet the desired criteria without having to perform an expensive join.

The above is the detailed content of How to Implement a Subquery with WHERE IN Clause in Laravel Eloquent?. For more information, please follow other related articles on the PHP Chinese website!