Optimizing Laravel Subquery Where In

Laravel’s Eloquent ORM ( Object Relational mapping ) is a powerful tool for working with databases in our Laravel applications. It provides an intuitive interface for defining relationships between models and querying related data. When working with Laravel and complex database queries, it’s crucial to optimize our code for speed and efficiency.

In this blog post, we will explore how to transform a raw SQL subquery into an optimized Laravel query, and we will look at some additional examples as well for our solid understanding.

Raw SQL Subquery

Let’s Start with one of the following raw SQL query and then we will see how we can transform that using Laravel Eloquent,

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

This above query retrieves specific product details for active products that belong to certain categories. While it works, converting this to Laravel’s query builder can offer better readability and potential performance improvements.

The Laravel Solution: Optimized Query

The Above Raw SQL Query, can be written in laravel using Two Approaches,

Approach 1, ( using JOINS )

$products = DB::table('products as p')
      ->select('p.id', 'p.name', 'p.img', 'p.safe_name', 'p.sku', 'p.status_id') 
      ->join('product_category as pc', 'p.id', '=', 'pc.product_id') 
      ->whereIn('pc.category_id', [223, 15])
      ->where('p.active', 1) 
      ->distinct() 
      ->get();

Let’s Break down the above query

1. Join Instead of Subquery: We replaced the subquery with a join, which is generally more efficient.
2. Use of Aliases: We used aliases (p and pc) to make the query more readable.
3. Distinct: We added distinct() to ensure we don’t get duplicate products if a product belongs to multiple selected categories.
4. Direct Comparison: We used where(‘p.active’, 1) instead of where(‘p.active’, ‘=’, 1) for a slight performance boost.

Performance Considerations

1. Indexing: Ensure that product_id and category_id in the product_category table, and id and active in the products table are properly indexed.
2. Chunking: If dealing with large datasets, consider using chunking:

DB::table('products as p')
    ->select(/* ... */)
    ->join('product_category as pc', 'p.id', '=', 'pc.product_id')
    ->whereIn('pc.category_id', [223, 15])
    ->where('p.active', 1)
    ->distinct()
    ->chunk(1000, function ($products) {
        foreach ($products as $product) {
            // Process each product
        }
    });

3. Eager Loading: If you’re working with Eloquent models and need to access related data, use eager loading to prevent N+1 query problems.

Approach 2, ( Without using JOIN)

$products = DB::table('products as p') 
      ->select('p.id', 'p.name', 'p.img', 'p.safe_name', 'p.sku', 'p.status_id) 
      ->whereIn('p.id', function($query) { 
            $query->select('product_id') 
                  ->from('product_category') 
                  ->whereIn('category_id', [223, 15]); 
      }) 
      ->where('p.active', 1) 
      ->get();

Let’s Break down the above query.

1. 1. We start with the main table products and alias it as p.

1. We select the specific columns we need from the products table.
2. Instead of using a JOIN, we use a whereIn clause with a subquery:
   -> The subquery selects product_id from the product_category table.
   -> It filters for the specific category IDs (223 and 15).
3. We add the condition for active products with where(‘p.active’, 1).
4. Finally, we execute the query with get().

Performance Considerations:

1. Indexing is crucial here. Ensure that id and active in the products table, and product_id and category_id in the product_category table are properly indexed.
2. In some cases, this subquery approach might be less efficient than a JOIN, especially for larger datasets. It’s always a good idea to compare the performance of different query structures with specific data.

Some Additional Examples

Let’s explore some more couple of examples to solidify the concept.

Example 1: Filtering Orders by Customer and Date Range
Raw SQL:

SELECT `o`.`id`, `o`.`order_date`, `o`.`total_amount` FROM `orders` o
WHERE `o`.`customer_id` 
IN ( 
    SELECT `id` FROM `customers` 
    WHERE `country` = 'USA' 
) 
AND `o`.`order_date` BETWEEN '2023-01-01' AND '2023-12-31'

 
Optimized Laravel Eloquent Query.

$orders = DB::table('orders as o') 
           ->select('o.id', 'o.order_date', 'o.total_amount') 
           ->join('customers as c', 'o.customer_id', '=', 'c.id') 
            ->where('c.country', 'USA') 
            ->whereBetween('o.order_date', ['2023-01-01', '2023-12-31']) 
            ->get();

Optimizing database queries in Laravel involves more than just translating raw SQL. By leveraging Laravel’s query builder, using joins instead of subqueries, and applying performance techniques like indexing and chunking, we can significantly improve our application’s database performance.