Updating multiple records in a single query from existing data using the Laravel query builder

Question

I have an inventory system with bins, lots, and transactions.

Bin:
  has many lots

Lot:
  belongs to a bin
  belongs to many transactions

transaction_lot (pivot)
  - quantity

Transaction:
  belongs to many lots

Right now I have an attribute on Transaction that calculates the transaction value using the pivot data:

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

As a query optimization I want to store this directly on the transaction instead of having to pull the lots each time. I can iterate over all existing transactions, but there's millions of them and I'm hoping to get it done more efficiently. I'm building this logic into a command in case I need to do future maintenance on the tables.

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

The end result should be:

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

Can this be done in a single UPDATE query? I stumbled across similar questions but 1) my SQL isn't great, 2) not sure if it's even valid SQL, and 3) the query builder doesn't seem to support this kind of operation without resorting to raw statements (maybe because of 2?). I want to do something like this but the SUM is giving me issues:

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

Alternatively with joins (?):

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;

Lastly, can this be done using the query builder methods (apart from using DB::raw())?

score 0 · Answer 1 · answered Jul 25 '23 at 17:06

This can be done in the query builder by joining a subquery containing the calculated transaction values, though not without a few DB::raw()s. First create the subquery to calculate the transaction values:

$transaction_lot_values = DB::table('transaction_lot')
    ->join('lot', 'lot.lot_id', '=', 'transaction_lot.lot_id')
    ->select([
        'transaction_lot.transaction_id',
        DB::raw('SUM(lot.unit_value * ABS(transaction_lot.quantity)) as value'),
    ])
    ->groupBy('transaction_lot.transaction_id');

Next we can pass this subquery to an update query using joinSub:

DB::table('transaction')
    ->joinSub($transaction_lot_values, 'transaction_lot', fn (JoinClause $join) => $join
        ->on('transaction_lot.transaction_id', '=', 'transaction.transaction_id')
    )
    ->update(['transaction.value' => DB::raw('transaction_lot.value')]);

Disclosure: An earlier AI-generated answer (since deleted) led me to this solution.

I have also tested this code to ensure it results in the desired end state. — Erich, Jul 25 '23 at 17:23

score 0 · Answer 2 · answered Jul 25 '23 at 18:22

You can do it by using sql cases

In Mysql

UPDATE db.transactions
SET PRICE = CASE  
          WHEN id = 3 THEN 500
          WHEN id = 4 THEN 300
       END 
WHERE ID IN (3, 4)

In Laravel

$vat = 0.20;
$transactions = Transaction::get();

$cases = []; 
$ids = [];
$params = [];

foreach ($transactions as $transaction) {
 $cases[] = "WHEN {$transaction->id} then ?";
 $params[] = $transaction->profit * $vat;
 $ids[] = $transaction->id;
}

$ids = implode(',', $ids);
$cases = implode(' ', $cases);
if (!empty($ids)) {
  \DB::update("UPDATE transactions SET `price` = CASE `id` {$cases} END WHERE `id` in ({$ids})", $params);
}

This solution is fine if you only have a handful of rows, but doesn't scale very well. Running this with only _dozens_ of rows will produce a very long SQL statement. I shudder to think what the generated CASE statement for _thousands_ or even _millions_ of rows would look like. — Erich, Jul 25 '23 at 20:31

Updating multiple records in a single query from existing data using the Laravel query builder

2 Answers2