Laravel多hasMany关联表查询去重：计算产品总库存与总价值

问题背景 #

现有三张表：products（产品）、stocks（库存变动）、costs（单位成本记录），需要统计每个产品的总库存数量（库存变动的累加值）和总价值（总库存 × 最新日期的单位成本）。

表结构 #

products表

stocks表

costs表

原查询因直接关联两个hasMany表（一个产品对应多条库存、多条成本记录），产生笛卡尔积，导致统计结果错误：产品A总库存显示为2（实际应为1）、总价值40（实际应为20）；产品B总库存显示为2（实际应为1）、总价值80（实际应为40）。

原代码：

DB::statement("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");

$result = Product::query()
->leftJoin('stocks', 'stocks.product_id', '=', 'products.id')
->leftJoin('costs', function($join){
   $join->on('costs.product_id', '=', 'products.id')
   ->orderByDesc('date')
   ->limit(1);
})
->select([
   'products.name',
   DB::raw("SUM(stocks.qty) as total_qty"),
   DB::raw("SUM(stocks.qty) * costs.cost_per_unit as total_value"),
])
->groupBy('products.id')
->get();

解决方案 #

核心思路是分步计算聚合数据，再关联主表，避免多表直接关联导致的数据膨胀。

方法1：使用子查询构建聚合结果 #

$result = Product::query()
    // 预先计算每个产品的总库存
    ->leftJoinSub(
        DB::table('stocks')
            ->select('product_id', DB::raw('SUM(stock) as total_qty'))
            ->groupBy('product_id'),
        'stock_agg',
        'stock_agg.product_id',
        '=',
        'products.id'
    )
    // 预先获取每个产品的最新单位成本
    ->leftJoinSub(
        DB::table('costs')
            ->select('product_id', 'cost_per_unit')
            ->whereRaw('(product_id, date) IN (SELECT product_id, MAX(date) FROM costs GROUP BY product_id)'),
        'latest_cost',
        'latest_cost.product_id',
        '=',
        'products.id'
    )
    ->select([
        'products.name',
        // 处理无库存的产品，默认显示0
        DB::raw('COALESCE(stock_agg.total_qty, 0) as total_qty'),
        // 计算总价值，同时处理无成本的情况
        DB::raw('COALESCE(stock_agg.total_qty, 0) * COALESCE(latest_cost.cost_per_unit, 0) as total_value')
    ])
    ->get();

方法2：利用Eloquent关联分步处理 #

如果已在Product模型中定义了stocks和costs的hasMany关联，可以用关联查询分步获取数据：

// 预加载聚合后的库存和最新成本
$products = Product::with([
    'stocks' => function ($query) {
        $query->select('product_id', DB::raw('SUM(stock) as total_qty'))
            ->groupBy('product_id');
    },
    'costs' => function ($query) {
        $query->latest('date')->limit(1);
    }
])->get();

// 计算总价值并整理数据
$products->transform(function ($product) {
    $totalQty = $product->stocks->first()?->total_qty ?? 0;
    $latestCost = $product->costs->first()?->cost_per_unit ?? 0;
    
    $product->total_qty = $totalQty;
    $product->total_value = $totalQty * $latestCost;
    
    // 清理不需要的关联数据
    $product->unsetRelation('stocks');
    $product->unsetRelation('costs');
    
    return $product;
});

关键修正点 #

1. 字段名修正：原代码中SUM(stocks.qty)是错误的，stocks表的库存字段为stock，需改为SUM(stocks.stock)。
2. 避免笛卡尔积：通过子查询预先聚合库存、获取最新成本，再与产品表关联，不会出现数据重复计算的问题。
3. 空值处理：用COALESCE函数兜底无库存/无成本的场景，确保统计结果不为NULL。
4. 移除不规范设置：无需关闭ONLY_FULL_GROUP_BY，正确分组后符合SQL标准。

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