PHP
Efficiently Upserting Multiple Records
Learn how to use Laravel Eloquent's `upsert` method to efficiently insert new records or update existing ones in a single database query, optimizing data synchronization.
// Assuming a 'products' table with 'name' and 'price' and 'id' as primary key
$products = [
['id' => 1, 'name' => 'Laptop', 'price' => 1200],
['id' => 2, 'name' => 'Mouse', 'price' => 25],
['id' => 3, 'name' => 'Keyboard', 'price' => 75],
];
// Update existing products by 'id' or insert new ones.
// 'id' and 'name' are columns to check for uniqueness.
// 'price' is the column to update if a record matches.
App\Models\Product::upsert(
$products,
['id'], // Columns to check for uniqueness (if match, update)
['name', 'price'] // Columns to update if a record matches
);
// Example with a different unique constraint (e.g., only 'name' is unique):
// App\Models\Product::upsert(
// $products,
// ['name'], // Check only 'name' for uniqueness
// ['price']
// );
// The 'updated_at' timestamp is automatically managed by default with upsert.
How it works: The `upsert` method allows you to insert records that do not exist or update records that already exist. It takes an array of values, an array of columns that should be used to determine if a record already exists (the unique identifier), and an array of columns that should be updated if a matching record is found. This significantly reduces database queries compared to checking for existence and then performing separate inserts or updates, making it ideal for bulk data synchronization. The `updated_at` timestamp is automatically handled by default.