如何用Laravel查询构造器单查询批量更新现有数据

系统关联关系 #

Bin:
  has many lots

Lot:
  belongs to a bin
  belongs to many transactions

transaction_lot (pivot)
  - quantity

Transaction:
  belongs to many lots

当前实现 #

目前在Transaction模型中通过访问器计算交易价值：

public function getValueAttribute()
{
   return $this->lots->sum(fn (Lot $lot) => $lot->unit_value * abs($lot->pivot->quantity));
}

需求 #

为优化查询性能，希望将该值直接存储到transaction表的value字段中，而非每次查询都关联Lot数据。现有数百万条交易记录，需高效实现，已将逻辑纳入命令行工具以便后续维护。

示例数据 #

transaction_lot表：

transaction_id | lot_id | quantity 
1              | 1      | 5
1              | 2      | 3
2              | 2      | 2
3              | 3      | 3

lot表：

lot_id | unit_value
1      | 10
2      | 12
3      | 15

期望结果 #

transaction表：

transaction_id | value
1              | 86    // = 5*10 + 3*12
2              | 24    // = 2*12
3              | 45    // = 3*15

遇到的问题 #

尝试了以下SQL但SUM函数存在问题：

UPDATE transaction t, transaction_lot tl, lot l
SET t.value = SUM(l.unit_value * ABS(tl.quantity))
WHERE t.transaction_id = tl.transaction_id;
AND tl.lot_id = l.lot_id

关联写法也不行：

UPDATE transaction t
JOIN transaction_lot tl ON tl.transaction_id = t.transaction_id
JOIN lot l on l.lot_id = tl.lot_id
SET t.value = SUM(l.unit_value * ABS(tl.quantity))
WHERE t.transaction_id = tl.transaction_id;

请问：

1. 能否通过单条UPDATE语句实现？
2. 能否不使用DB::raw()，仅通过Laravel查询构造器方法实现？

单条UPDATE语句实现 #

之前的SQL报错是因为直接在UPDATE的SET子句中使用SUM，没有对交易ID进行分组聚合。正确的做法是先通过子查询计算出每个transaction的总价值，再关联transaction表进行更新：

UPDATE transaction t
JOIN (
    SELECT 
        tl.transaction_id,
        SUM(l.unit_value * ABS(tl.quantity)) AS total_value
    FROM transaction_lot tl
    JOIN lot l ON tl.lot_id = l.lot_id
    GROUP BY tl.transaction_id
) AS transaction_totals ON t.transaction_id = transaction_totals.transaction_id
SET t.value = transaction_totals.total_value;

这个语句先在子查询中按transaction_id分组，计算每条交易的总价值，再将结果与transaction表关联，批量更新value字段。对于百万级数据，这种方式比循环单条更新高效得多。

Laravel查询构造器实现（尽量避免DB::raw） #

可以通过Laravel的查询构造器实现，大部分逻辑用构造器方法完成，仅在计算总价值时需要用到表达式：

use Illuminate\Support\Facades\DB;

DB::table('transaction')
    ->joinSub(
        DB::table('transaction_lot')
            ->join('lot', 'transaction_lot.lot_id', '=', 'lot.lot_id')
            ->select('transaction_lot.transaction_id')
            ->selectRaw('SUM(lot.unit_value * ABS(transaction_lot.quantity)) as total_value')
            ->groupBy('transaction_lot.transaction_id'),
        'transaction_totals',
        function ($join) {
            $join->on('transaction.transaction_id', '=', 'transaction_totals.transaction_id');
        }
    )
    ->update([
        'value' => DB::raw('transaction_totals.total_value')
    ]);

如果想要完全避免selectRaw和DB::raw，在聚合计算场景中很难实现——这类复杂计算本身依赖原生SQL表达式。上面的写法已经最大程度利用了Laravel查询构造器的链式调用，同时保证了语句的可读性和性能。

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