PHP
Efficient Batch Insert or Update with Eloquent upsert
Master the `upsert` method in Laravel Eloquent for highly efficient batch insertion or updating of multiple records, avoiding duplicate entries with a single database query.
<?php
namespace App\Http\Controllers;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Support\Carbon;
class ProductController extends Controller
{
public function storeMultiple(Request $request)
{
$products = [
['name' => 'Laptop Pro', 'sku' => 'LAP001', 'price' => 1200.00, 'stock' => 50, 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()],
['name' => 'Gaming Mouse', 'sku' => 'MOU001', 'price' => 75.50, 'stock' => 200, 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()],
['name' => 'Wireless Keyboard', 'sku' => 'KEY001', 'price' => 120.00, 'stock' => 100, 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()],
// Imagine 'Laptop Pro' already exists with sku 'LAP001' but with a different price/stock
['name' => 'Laptop Pro Updated', 'sku' => 'LAP001', 'price' => 1250.00, 'stock' => 45, 'created_at' => Carbon::now(), 'updated_at' => Carbon::now()],
];
Product::upsert(
$products,
['sku'], // Columns to use for unique identification (e.g., primary key or unique index)
['name', 'price', 'stock', 'updated_at'] // Columns to update if a matching record is found
);
return response()->json(['message' => 'Products upserted successfully.']);
}
}
How it works: The `upsert` method in Eloquent provides a highly efficient way to insert records that do not exist, or update records that already exist, using a single database query. It takes three arguments: an array of values to insert or update, a set of columns that uniquely identify records (e.g., a primary key or unique index), and an array of columns that should be updated if a matching record is found. This method is particularly useful for synchronizing large datasets and significantly improves performance over individual `updateOrCreate` calls.