Laravel中向两张表插入记录并同步更新store_inventories表库存的问题

在Laravel中提交数据时同步完成两个操作：向store_inventory_entries表插入入库记录，同时更新store_inventories表对应商品的stock库存字段，两张表通过inventory_id关联。
表结构参考：

控制器实现代码 #

public function saveInventoryEntry(Request $request)
{
    $message = null; 
   
    $data = $request->all();
    $ids= [];

    try {
        foreach ($data as $key => $inventory) {
            
            $inventoryEntry = new StoreInventoryEntry();
            $inventoryEntry->inventory_id = $inventory['id'];
            $inventoryEntry->code = $inventory['code'];
            $inventoryEntry->quantity = $inventory['quantity'];
            $inventoryEntry->save();

            $ids[] = $inventory['id']; 
           
            StoreInventory::whereIn('id',$dt)->update(['stock' => $inventory['quantity']]);
                           
        }
        
        $message = $this->sendResponse($inventoryEntry, 'Aggregated data');

    } catch (\Exception $e) {
        $message = $this->sendError($e->getMessage(), ['Entries could not be registered'], 500);
    }

    return $message;

}

提交数据格式 #

[
 {
    "id" : 1,
    "code" : "010101",       
    "quantity" : 10 
 },
 {
    "id" : 2,
    "code" : "23232",       
    "quantity" : 7 
 }
]

迁移文件代码 #

// store_inventories 表迁移
public function up()
{
    Schema::create('store_inventories', function (Blueprint $table) {
        $table->id();
        $table->string('code');
        $table->string('reference');
        $table->string('line');
        $table->integer('cost');
        $table->integer('stock');
        $table->integer('min_stock');
        $table->string('location')->default('Almacén');
        $table->boolean('status')->default(1);
        $table->timestamps();
    });
}

// store_inventory_entries 表迁移
public function up()
{
    Schema::create('store_inventory_entries', function (Blueprint $table) {
        $table->id();
        $table->unsignedBigInteger('inventory_id');
        $table->string('code');
        $table->integer('quantity');
        $table->foreign('inventory_id')->references('id')->on('store_inventories');
        $table->timestamps();
    });
}

原有代码存在两个核心问题：

• 代码中StoreInventory::whereIn('id',$dt)里的$dt变量未定义，会直接触发报错
• 即使给$dt赋值，每次循环用whereIn批量更新库存，会导致所有已遍历过的商品库存都被覆盖为当前循环的quantity值，逻辑完全错误

正确实现代码 #

建议开启数据库事务，保证入库记录插入和库存更新两个操作的原子性，避免出现数据不一致的情况。

use Illuminate\Support\Facades\DB;

public function saveInventoryEntry(Request $request)
{
    try {
        $data = $request->all();
        $lastEntry = null;

        DB::transaction(function () use ($data, &$lastEntry) {
            foreach ($data as $inventory) {
                // 插入入库记录
                $lastEntry = StoreInventoryEntry::create([
                    'inventory_id' => $inventory['id'],
                    'code' => $inventory['code'],
                    'quantity' => $inventory['quantity'],
                ]);

                // 更新对应商品库存，二选一即可
                // 场景1：直接覆盖库存为提交的quantity值
                StoreInventory::where('id', $inventory['id'])
                    ->update(['stock' => $inventory['quantity']]);
                
                // 场景2：入库累加库存（入库业务推荐使用，自带行锁避免并发更新问题）
                // StoreInventory::where('id', $inventory['id'])
                //     ->increment('stock', $inventory['quantity']);
            }
        });

        return $this->sendResponse($lastEntry, '库存录入成功');
    } catch (\Exception $e) {
        return $this->sendError($e->getMessage(), ['库存录入失败'], 500);
    }
}

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