如何用Laravel Query Builder实现含子查询、筛选、统计的复杂SQL？

Got it, let's break down how to translate your complex aggregate SQL into clean Laravel Query Builder code—including support for whereIn without having to mess with manual collection stitching.

First, your original query relies on a subquery to calculate aggregated totals, then uses those totals to compute average order values. We can replicate this structure entirely with Query Builder methods, no raw string hacks needed.

Step 1: Build the Subquery (Aggregate Calculations) #

This is where you'll define all your total amounts and order counts. If you need whereIn, just add it directly here—Query Builder handles the rest seamlessly.

// Define your filter values if using whereIn (replace with your actual data)
$targetIds = [5, 12, 17];

$subquery = DB::table('orders')
    // Add your whereIn filter here if needed
    ->whereIn('store_id', $targetIds)
    ->select([
        // Calculate totals for the "current" period (adjust date logic to match your needs)
        DB::raw('SUM(CASE WHEN created_at >= CURRENT_TIMESTAMP THEN amount ELSE 0 END) AS total_amount_until_now'),
        DB::raw('COUNT(CASE WHEN created_at >= CURRENT_TIMESTAMP THEN id ELSE NULL END) AS total_orders_until_now'),
        // Calculate totals for the "one month ago" period
        DB::raw('SUM(CASE WHEN created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) AND created_at < CURRENT_TIMESTAMP THEN amount ELSE 0 END) AS total_amount_until_a_month_ago'),
        DB::raw('COUNT(CASE WHEN created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 1 MONTH) AND created_at < CURRENT_TIMESTAMP THEN id ELSE NULL END) AS total_orders_until_a_month_ago'),
        // Uncomment below and add groupBy if you need to group results (e.g., by user or store)
        // 'store_id'
    ])
    // ->groupBy('store_id') // Uncomment if grouping by a column
;

Step 2: Build the Outer Query (Calculate Averages) #

Now we'll reference the subquery to compute the average order values. We use NULLIF to avoid division-by-zero errors—critical for edge cases where there might be zero orders in a period.

$averageOrderValues = DB::table($subquery, 'aggregates')
    ->select([
        DB::raw('total_amount_until_now / NULLIF(total_orders_until_now, 0) AS avg_order_value_now'),
        DB::raw('total_amount_until_a_month_ago / NULLIF(total_orders_until_a_month_ago, 0) AS avg_order_value_until_a_month_ago'),
        // Include the grouped column here if you added one in the subquery
        // 'store_id'
    ])
    ->get();

Key Tips: #

• whereIn Integration: Adding ->whereIn() to the subquery works exactly like any other Query Builder call—no manual string concatenation required. Laravel handles parameter binding automatically, making this safer than raw SQL.
• Division-by-Zero Protection: The NULLIF function ensures that if total_orders_until_now is 0, the result becomes NULL instead of throwing an error. If you prefer to return 0 instead, wrap the calculation in IFNULL(..., 0).
• Grouping: If your original query groups by a column (like store ID), just add the column to the subquery's select and uncomment the groupBy line. The outer query can then include that column to keep results grouped logically.

This approach keeps your code maintainable, leverages Laravel's Query Builder features, and avoids the hassle of manual collection handling with raw DB queries.

内容的提问来源于stack exchange，提问作者SNaRe