updateOrCreate only updating one record

Question

I have a list of records where I want to update the quantities with new values, but when I use the update or Create method, it only updates only one record.

$data = DB::table('inventory_items')->select('inventory_items.*')
    ->join('sorder_parts', 'inventory_items.id', 'sorder_parts.inventory_id')
    ->where('sorder_parts.sorder_id', '=', $id)
    ->selectraw('inventory_items.quantity - sorder_parts.quantity AS new_quantity')
    ->get();


foreach ($data as $product_item) {
    $reduce_quantity = InventoryItem::updateOrCreate(['id' => $product_item->id],
        ['quantity' => $product_item->new_quantity]);
    dd($data, $reduce_quantity);
}

Answer 1

If you're looping over records to update them, use Eloquent instead as it's better suited for these operations:

$inventory_items = InventoryItem::with('sorderParts')->get();

foreach ($inventory_items as $item) {
  $item->quantity = $item->solderParts->new_quantity;
  $item->save();
}

This requires you to define the relationship in your InventoryItem model.

public function sorderParts()
{
  return $this->hasOne(SorderPart::class, 'inventory_id');
}

I've made some assumptions about your codebase with these examples, you'll have to configure it to match what you're working on, but the basic idea is we're using Eloquent to query tuples of InventoryItem and we're eager loading the SorderPart relationship so we don't execute a new query each time we access the relationship which would be an +1 query problem. We then loop over those results and save the updated values.