PHP
Efficient Batch Inserts and Updates with Eloquent `upsert`
Discover how to perform high-performance batch operations in Laravel using Eloquent's `upsert` method for simultaneous inserts and updates.
<?php
use App\Models\Product;
$productsToUpdate = [
['id' => 1, 'name' => 'Laptop Pro', 'price' => 1200.00, 'stock' => 50],
['id' => 2, 'name' => 'Wireless Mouse X', 'price' => 25.00, 'stock' => 200],
['id' => 3, 'name' => 'External SSD 1TB', 'price' => 99.99, 'stock' => 150],
['id' => 4, 'name' => 'Gaming Keyboard RGB', 'price' => 75.00, 'stock' => 100], // New product
];
// The 'upsert' method takes three arguments:
// 1. An array of values to insert or update.
// 2. The column(s) that uniquely identify records (e.g., 'id' for primary key).
// 3. The column(s) that should be updated if a matching record already exists.
Product::upsert($productsToUpdate, ['id'], ['name', 'price', 'stock']);
echo "Products batch updated/inserted successfully.
";
// Example: upserting based on a unique slug instead of ID
$newProducts = [
['slug' => 'gaming-headset-pro', 'name' => 'Gaming Headset Pro', 'price' => 80.00, 'stock' => 30],
['slug' => 'wireless-mouse-x', 'name' => 'Wireless Mouse X V2', 'price' => 27.50, 'stock' => 180],
];
Product::upsert($newProducts, ['slug'], ['name', 'price', 'stock']);
echo "Products batch updated/inserted by slug successfully.
";
How it works: The Eloquent `upsert` method provides a highly efficient way to insert records that don't exist and update records that do, all within a single database query. This is particularly useful for synchronizing data, importing large datasets, or handling bulk operations. It takes an array of data, a list of columns that should be used to uniquely identify records (like a primary key or unique index), and a list of columns that should be updated if a match is found. This significantly reduces the number of queries compared to fetching, checking, and then inserting/updating each record individually.