Laravel 9关联3表查询销量Top10商品的实现问题

需要筛选销量最高的Top10商品并展示，现有三张表：

• products：存储商品列表，包含id、name字段
• invoice_items：存储一类订单商品记录，包含name、item_quantity、product_id字段
• saleorder_items：存储另一类订单商品记录，包含name、item_quantity、product_id字段

订单会存入上述两张订单表中的一张，需要合并相同product_id的item_quantity统计总销量，但直接关联两张订单表后，要么只显示同时存在于两张表的商品，要么销量统计错误。

原错误关联两张表的代码：

$products = DB::table('products')
    ->leftJoin('invoice_items', 'products.id', '=', 'invoice_items.product_id')
    ->leftJoin('saleorder_items', 'products.id', '=', 'saleorder_items.product_id')
    ->selectRaw('products.*, COALESCE(sum(invoice_items.item_quantity + saleorder_items.item_quantity),0) sold')
    ->groupBy('products.id')
    ->orderBy('sold','desc')
    ->take(6)
    ->get();

仅关联单张订单表的可用代码：

$products = DB::table('products')
    ->leftJoin('invoice_items', 'products.id', '=', 'invoice_items.product_id')
    ->select(DB::raw('products.name, sum(invoice_items.item_quantity) as invoice'))
    ->groupBy('products.id')
    ->orderBy('invoice', 'desc')
    ->take(10)
    ->get();

错误原因 #

1. 笛卡尔积导致销量重复计算：同时left join两张订单表时，若某商品在invoice_items有n条记录、saleorder_items有m条记录，会生成n*m条关联结果，sum时会重复累加数据，导致总销量远高于实际值。
2. Null值处理错误：当商品仅在其中一张订单表有记录时，另一张表的item_quantity为Null，invoice_items.item_quantity + saleorder_items.item_quantity结果为Null，sum后仍为Null，最终被COALESCE转为0，无法正确统计实际销量。

解决方案 #

方案一：先分别统计两张表的销量，再合并计算

通过子查询先对两张订单表按product_id分组求和，再关联商品表合并总销量：

$products = DB::table('products')
    // 统计invoice_items的商品销量
    ->leftJoin(
        DB::raw('(SELECT product_id, SUM(item_quantity) as invoice_sold FROM invoice_items GROUP BY product_id) as invoice_summary'),
        'products.id', '=', 'invoice_summary.product_id'
    )
    // 统计saleorder_items的商品销量
    ->leftJoin(
        DB::raw('(SELECT product_id, SUM(item_quantity) as sale_sold FROM saleorder_items GROUP BY product_id) as sale_summary'),
        'products.id', '=', 'sale_summary.product_id'
    )
    // 计算总销量，用COALESCE将Null转为0
    ->selectRaw('products.id, products.name, COALESCE(invoice_summary.invoice_sold, 0) + COALESCE(sale_summary.sale_sold, 0) as total_sold')
    ->groupBy('products.id', 'products.name')
    ->orderBy('total_sold', 'desc')
    ->take(10)
    ->get();

方案二：合并两张订单表后统一统计

用UNION ALL合并两张订单表的所有记录，再按商品分组求和：

$products = DB::table('products')
    ->leftJoin(
        DB::raw('(
            SELECT product_id, item_quantity FROM invoice_items
            UNION ALL
            SELECT product_id, item_quantity FROM saleorder_items
        ) as all_order_items'),
        'products.id', '=', 'all_order_items.product_id'
    )
    ->selectRaw('products.id, products.name, COALESCE(SUM(all_order_items.item_quantity), 0) as total_sold')
    ->groupBy('products.id', 'products.name')
    ->orderBy('total_sold', 'desc')
    ->take(10)
    ->get();

这两种方案都能避免笛卡尔积问题，同时正确处理仅在单张订单表有记录的商品，准确统计总销量并取出Top10。

内容的提问来源于stack exchange，提问作者Alper Atabey Özürk