Laravel多表关联求和异常：关联查询导致求和结果重复计算

问题背景 #

需求：获取用户ID关联的contracts表price字段总和、transactions表sum字段总和，同时添加WHERE条件筛选总和满足contracts_sum < transactions_sum的用户。

现有查询代码：

$query = User::where([
    ['contracts_sum', '<', 'transactions_sum'],
])
->join('contracts', 'contracts.user_id', '=', 'users.id')
->join('transactions', 'users.id', '=', 'transactions.user_id')
->groupBy(['users.id'])
->select(
    'users.id', 'users.name',
    DB::raw('SUM(contracts.price) AS contracts_sum'),
    DB::raw('SUM(transactions.sum) AS transactions_sum'),
)
->get();

测试数据 #

contracts表数据：

[
  {
    "id": 1,
    "user_id":97,
    "price":"100"
  },
  {
    "id": 2,
    "user_id":97,
    "price":"200"
  },
  {
    "id": 3,
    "user_id":97,
    "price":"300"
  }
]

transactions表数据：

[
  {
    "id": 1,
    "user_id":97,
    "sum":"100"
  },
  {
    "id": 2,
    "user_id":97,
    "sum":"200"
  },
  {
    "id": 3,
    "user_id":97,
    "sum":"300"
  }
]

错误结果与期望结果 #

错误结果（求和被重复计算）：

[
  {
    "id":97,
    "name":"JOHN",
    "contracts_sum":"1800",
    "transactions_sum":"1800"
  }
]

期望结果：

[
  {
    "id":97,
    "name":"JOHN",
    "contracts_sum":"600",
    "transactions_sum":"600"
  }
]

错误原因 #

同时关联contracts和transactions两张表时，会生成笛卡尔积：用户97有3条合同记录和3条交易记录，关联后会产生3×3=9条临时记录。每条合同的price会被重复计算3次，每条交易的sum也会被重复计算3次，最终总和变成正确值的3倍（600×3=1800）。

修正方案 #

方案1：子查询直接计算用户总和 #

通过子查询分别计算每个用户的合同总和与交易总和，避免笛卡尔积问题：

$query = User::select(
    'users.id', 
    'users.name',
    // 子查询计算当前用户的合同总价
    DB::raw('(SELECT SUM(price) FROM contracts WHERE contracts.user_id = users.id) AS contracts_sum'),
    // 子查询计算当前用户的交易总价
    DB::raw('(SELECT SUM(sum) FROM transactions WHERE transactions.user_id = users.id) AS transactions_sum')
)
// 使用whereRaw引用子查询结果作为筛选条件
->whereRaw('(SELECT SUM(price) FROM contracts WHERE contracts.user_id = users.id) < (SELECT SUM(sum) FROM transactions WHERE transactions.user_id = users.id)')
->get();

方案2：先聚合子表再关联 #

先对contracts和transactions分别按用户分组求和，再将聚合结果与用户表关联：

// 预计算每个用户的合同总价
$contractsAgg = DB::table('contracts')
    ->select('user_id', DB::raw('SUM(price) AS contracts_sum'))
    ->groupBy('user_id');

// 预计算每个用户的交易总价
$transactionsAgg = DB::table('transactions')
    ->select('user_id', DB::raw('SUM(sum) AS transactions_sum'))
    ->groupBy('user_id');

// 关联用户表与两个聚合结果表
$query = User::joinSub($contractsAgg, 'contracts_agg', function ($join) {
    $join->on('users.id', '=', 'contracts_agg.user_id');
})
->joinSub($transactionsAgg, 'transactions_agg', function ($join) {
    $join->on('users.id', '=', 'transactions_agg.user_id');
})
// 直接引用聚合后的字段作为筛选条件
->where('contracts_agg.contracts_sum', '<', 'transactions_agg.transactions_sum')
->select('users.id', 'users.name', 'contracts_agg.contracts_sum', 'transactions_agg.transactions_sum')
->get();

WHERE条件的正确用法 #

• 不能直接引用SELECT中的别名：原代码中where(['contracts_sum', '<', 'transactions_sum'])无效，因为SQL执行顺序是先执行WHERE子句，再执行SELECT生成别名，此时contracts_sum和transactions_sum还未存在。
• 两种替代方式：
  • 使用whereRaw直接编写包含子查询的条件（如方案1）。
  • 先通过子查询/子表完成聚合，再引用聚合表的字段作为条件（如方案2）。
• GROUP BY后筛选用HAVING：如果是对分组后的聚合结果筛选，可使用having子句，但having是对分组结果过滤，性能和适用场景与WHERE不同。

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