PHP
Efficiently Inserting or Updating Records with Eloquent Upsert
Use Laravel Eloquent's `upsert` method to efficiently insert new records or update existing ones based on a unique key, reducing boilerplate code.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class ProductInventory extends Model
{
protected $fillable = ['product_id', 'location', 'stock_count'];
// Assuming 'product_id' and 'location' together form a unique key
}
// Example Usage:
// Data to upsert
$inventories = [
['product_id' => 1, 'location' => 'Warehouse A', 'stock_count' => 100],
['product_id' => 2, 'location' => 'Warehouse B', 'stock_count' => 50],
['product_id' => 1, 'location' => 'Warehouse B', 'stock_count' => 75], // This will be inserted
];
// The 'stock_count' will be updated if 'product_id' and 'location' match
$updatedOrCreated = ProductInventory::upsert(
$inventories,
['product_id', 'location'], // Columns that make the record unique
['stock_count'] // Columns to update if a match is found
);
// Example: Update existing records and insert new ones
$updateData = [
['product_id' => 1, 'location' => 'Warehouse A', 'stock_count' => 120],
['product_id' => 3, 'location' => 'Warehouse C', 'stock_count' => 200] // New record
];
ProductInventory::upsert(
$updateData,
['product_id', 'location'],
['stock_count']
);
How it works: The `upsert` method in Eloquent provides a highly efficient way to either insert a new record or update an existing one based on specified unique columns. It takes an array of data, an array of columns that should be considered unique, and an optional array of columns to update if a match is found. This method issues a single database query, making it much more performant than looping through data and performing individual `firstOrCreate` or `updateOrCreate` calls, especially for bulk operations.