如何统计商品不同变体与规格的销量（含现有代码优化需求）

Hey there! Let's tackle this sales grouping problem you're working on. You need to count total sales for each unique combination of product ID, SKU, variant, and size—here are the best ways to achieve this in Laravel, both efficiently at the database level and using collection methods if you already have the data in memory.

1. Database-Level Query (Recommended) #

Doing the grouping and counting directly in your database is far more efficient, especially with large datasets. Here's how to adjust your Laravel query to get exactly what you need:

$salesSummary = DB::table('orders')
    ->select(
        'ProductID as id',
        'SKU as sku',
        'Variant as variant',
        'Size as size',
        DB::raw('COUNT(*) as count')
    )
    ->whereBetween('created_at', [$date['start'], $date['end']])
    // Optional: Uncomment below to only count completed/valid orders
    // ->where('Status', '=', 'completed')
    ->groupBy('ProductID', 'SKU', 'Variant', 'Size')
    ->get();

What this does: #

• select(): Picks the fields you care about and aliases them to match your desired output format.
• DB::raw('COUNT(*) as count'): Calculates the total number of orders for each unique product variant/size group.
• groupBy(): Groups records by the full combination of product ID, SKU, variant, and size—ensuring each result row represents one unique product configuration.
• Optional status filter: If you don't want to include canceled/pending orders, add the where('Status') line to narrow down valid transactions.

You can convert the result to an array with ->toArray() if needed, and it will match your expected structure perfectly.

2. Collection-Level Processing (If You Already Have the Data) #

If you've already fetched the full order dataset into a Laravel Collection (e.g., from your original get() call), use Laravel's built-in collection methods to group and transform the data cleanly:

// Fetch all orders with your original date filter
$orders = DB::table('orders')
    ->whereBetween('created_at', [$date['start'], $date['end']])
    ->get();

// Group and transform the collection into your desired format
$salesSummary = $orders->groupBy(function ($order) {
    // Create a unique key for each product configuration
    return $order->ProductID . '|' . $order->SKU . '|' . $order->Variant . '|' . $order->Size;
})->map(function ($group) {
    $firstItem = $group->first();
    return [
        'id' => $firstItem->ProductID,
        'sku' => $firstItem->SKU,
        'variant' => $firstItem->Variant,
        'size' => $firstItem->Size,
        'count' => $group->count()
    ];
})->values()->toArray();

Why this works: #

• groupBy() with a callback: Creates a unique key for each product variant/size combination, grouping all matching orders together.
• map(): Transforms each group into your desired structure, using the first item's product details and the group's total order count.
• values(): Resets the array keys to be sequential (instead of using the unique group keys).

Why Your Original Code Didn't Work #

Your initial PHP loop was only checking for duplicate product IDs, but it didn't account for variant and size—so it was counting all instances of the same product ID regardless of its configuration. The solutions above focus on grouping by the full set of fields that define a unique product variant, which is exactly what you need.

Example Output #

Either method will produce a result matching your expected format, like this:

Array (
    [0] => Array ( [id] => 1089 [sku] => 123 [variant] => A [size] => 1KG [count] => 2 )
    [1] => Array ( [id] => 1089 [sku] => 123 [variant] => B [size] => 250gm [count] => 1 )
    [2] => Array ( [id] => 1089 [sku] => 123 [variant] => B [size] => 1KG [count] => 1 )
    [3] => Array ( [id] => 1089 [sku] => 123 [variant] => C [size] => 250gm [count] => 1 )
    [4] => Array ( [id] => 1090 [sku] => 124 [variant] => C [size] => 1KG [count] => 3 )
)

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