如何编写Eloquent多表关联查询以统计各物料交易重量总和

我有4张数据表：materials、advertisements、offerrequests、transactions，需要编写Eloquent查询语句，统计每类参与交易的物料对应的交易重量总和。我尝试的查询代码如下：

Transaction::select(DB::raw("IF(transactions.is_sell = '1', advertisements.weight, offerrequests.weight) as weight"),DB::raw("IF(transactions.is_sell = '1', advertisements.material_id, offerrequests.material_id) as material_id"))
            ->leftJoin('advertisements', function ($join) {
                $join->on('advertisements.id', '=', 'transactions.post_id');
            })
            ->leftJoin('offerrequests', function ($join) {
                $join->on('offerrequests.id', '=', 'transactions.post_id');
            })
            ->leftJoin('materials', function ($join) {
                $join->on('materials.id', '=', 'material_id');
            })
           ->groupBy('material_id')->get();

数据表结构及数据 #

Transactions表 #

Advertisements表 #

Offerrequests表 #

Materials表 #

期望查询结果 #

修正后的查询代码 #

原查询存在未对重量求和、多表左连接易产生笛卡尔积、关联物料表时字段歧义等问题，以下是修正后的Eloquent查询：

$transactionStats = Transaction::select(
    DB::raw('SUM(CASE WHEN transactions.is_sell = 1 THEN advertisements.weight ELSE offerrequests.weight END) AS total_weight'),
    DB::raw('UPPER(LEFT(materials.name, 1)) || SUBSTRING(materials.name, 2) AS material')
)
->leftJoin('advertisements', function ($join) {
    $join->on('advertisements.id', '=', 'transactions.post_id')
         ->where('transactions.is_sell', 1);
})
->leftJoin('offerrequests', function ($join) {
    $join->on('offerrequests.id', '=', 'transactions.post_id')
         ->where('transactions.is_sell', 0);
})
->join('materials', function ($join) {
    $join->on('materials.id', '=', DB::raw('CASE WHEN transactions.is_sell = 1 THEN advertisements.material_id ELSE offerrequests.material_id END'));
})
->groupBy('materials.id', 'materials.name')
->get();

关键调整说明 #

1. 用SUM()聚合函数计算每类物料的总交易重量
2. 根据is_sell值分别关联对应表，避免无效连接
3. 明确物料ID的来源，消除字段歧义
4. 可选：通过字符串函数将物料名称首字母大写，匹配期望结果格式

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