How to Use Subqueries with Laravel's `whereIn` Clause?

Incorporating Subqueries into Laravel Queries: A Subquery WHERE IN Example

When dealing with complex database queries, subqueries offer a powerful way to retrieve data from multiple tables or filter results based on calculations. Laravel, a popular PHP framework, provides an easy-to-use interface for working with subqueries.

Problem:

You need to craft a query in Laravel that extracts data from the "products" table using a subquery to filter based on values in the "product_category" table. Specifically, you want to include rows that satisfy the following criteria:

• "id" from the "products" table must appear in the "product_id" column of the "product_category" table.
• Category ID values are '223' or '15' in the "product_category" table.
• "active" column in the "products" table is set to 1.

Solution:

To achieve this, Laravel provides a flexible way to incorporate subqueries into your queries using closures:

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

1. Subquery Definition (in Closure):

   • function($query): A closure that defines the subquery.
   • $query->select(...): Specifies which column from the subquery you want to retrieve. In this case, it's "product_id".
   • from(with(new ProductCategory)->getTable()): Determines the table from which to fetch data for the subquery. It references the "product_category" table.
   • ->whereIn('category_id', ['223', '15']): Sets the filter condition for the subquery, restricting results to rows where "category_id" is either '223' or '15'.
   • ->where('active', 1): Adds an additional filter to ensure "active" is set to 1 in the "product_category" table.

2. Main Query:

   • whereIn('id', ...): This is the main query that makes use of the subquery defined in the closure. It matches the "id" column in the "products" table with "product_id" in the subquery's results.

The above is the detailed content of How to Use Subqueries with Laravel's `whereIn` Clause?. For more information, please follow other related articles on the PHP Chinese website!