PHP
Performing Batch Inserts and Updates with Eloquent Upsert
Optimize database operations by using Laravel Eloquent's `upsert` method for efficient batch inserts or updates of multiple records in a single query.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
// Assuming you have a 'products' table with 'name' (unique) and 'price' columns
class Product extends Model
{
protected $fillable = ['name', 'price', 'description'];
}
// In a controller or service:
$productsToUpdateOrCreate = [
[
'name' => 'Laptop Pro X',
'price' => 1200.00,
'description' => 'Powerful professional laptop.'
],
[
'name' => 'Gaming Mouse G500',
'price' => 75.00,
'description' => 'Ergonomic gaming mouse.'
],
[
'name' => 'Wireless Keyboard',
'price' => 100.00,
'description' => 'Compact wireless keyboard.'
],
];
// Upsert products: if 'name' exists, update 'price' and 'description'; otherwise, insert.
Product::upsert(
$productsToUpdateOrCreate,
['name'], // Columns that should be used to uniquely identify records.
['price', 'description'] // Columns that should be updated if a matching record already exists.
);
// Alternatively, for a single record 'updateOrCreate':
// $product = Product::updateOrCreate(
// ['name' => 'Smartwatch X'], // Attributes to find by
// ['price' => 299.99, 'description' => 'Latest generation smartwatch.'] // Attributes to update or create
// );
How it works: This snippet demonstrates how to use Eloquent's powerful `upsert` method for efficient batch operations. The `upsert` method can insert records that do not exist or update records that do exist based on a given unique key (or keys). It takes an array of data, an array of columns to uniquely identify records, and an array of columns to update if a match is found. This significantly reduces the number of database queries compared to iterating and performing individual `updateOrCreate` calls, leading to better performance for large datasets. A single `updateOrCreate` example is also provided for reference for single record operations.