Laravel Eloquent：查询指定店铺商品并统计关联订单项的总收入与近30天收入

Got it, let's break down how to solve this. You need to pull all products for a specific shop, and for each product calculate two key metrics: total lifetime revenue (sum of sell_price * sold_quantity across all order items) and revenue from the last 30 days. Here's how to do this cleanly with Laravel:

Step 1: Ensure Your Model Relationship is Set Up #

First, make sure your Product model has the correct one-to-many relationship with OrderItem:

// app/Models/Product.php
public function orderItems()
{
    return $this->hasMany(OrderItem::class);
}

Step 2: Use withSum for Clean Aggregation (Laravel 8.37+) #

If you're on Laravel 8.37 or newer, the withSum method supports custom expressions and aliases, which makes this straightforward. We'll use it to calculate both revenue metrics in a single query:

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

$products = Product::where('shop_id', $shop->id)
    ->withSum([
        // Calculate total lifetime revenue
        'orderItems as total_revenue' => function ($query) {
            $query->select(DB::raw('COALESCE(SUM(sell_price * sold_quantity), 0)'));
        },
        // Calculate revenue from the last 30 days
        'orderItems as last_30_days_revenue' => function ($query) {
            $query->select(DB::raw('COALESCE(SUM(sell_price * sold_quantity), 0)'))
                ->where('sold_date', '>=', Carbon::now()->subDays(30));
        }
    ])
    ->get();

Key Details About This Query: #

• COALESCE: This ensures products with no order items return 0 instead of null, so you don't have to handle null values in your views or business logic.
• Aliases: The as total_revenue and as last_30_days_revenue let you access these metrics directly on each Product instance (e.g., $product->total_revenue).
• Date Filter: The where('sold_date', '>=', Carbon::now()->subDays(30)) clause restricts the 30-day revenue calculation to only recent order items.

Step 3: Compatibility for Older Laravel Versions (Pre 8.37) #

If you're using an older Laravel version that doesn't support the advanced withSum syntax, use a left join and raw select statements instead. Just make sure to include all columns from the products table in the groupBy clause to comply with strict SQL modes:

use Illuminate\Support\Facades\DB;
use Carbon\Carbon;

$products = Product::where('shop_id', $shop->id)
    ->leftJoin('order_items', 'products.id', '=', 'order_items.product_id')
    ->select([
        'products.*',
        DB::raw('COALESCE(SUM(order_items.sell_price * order_items.sold_quantity), 0) as total_revenue'),
        DB::raw('COALESCE(SUM(CASE WHEN order_items.sold_date >= ? THEN order_items.sell_price * order_items.sold_quantity ELSE 0 END), 0) as last_30_days_revenue')
    ])
    ->setBindings([Carbon::now()->subDays(30)])
    ->groupBy('products.id', 'products.title') // Add all product columns here if you have more than id/title
    ->get();

How to Use the Results #

Once you run either query, each $product in the collection will have the two new metrics available:

• $product->total_revenue: The total money earned from the product across all orders.
• $product->last_30_days_revenue: The product's revenue over the past 30 days.

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