Question 215812 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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())?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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);
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search