PHP
Efficiently Insert or Update Records with Eloquent's upsert Method
Master the `upsert` method in Laravel Eloquent to perform highly efficient batch inserts or updates, minimizing database calls and improving application performance for large datasets.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{
protected $fillable = ['name', 'price', 'quantity'];
// ...
}
// In a controller or service...
// Imagine $newProducts is an array of data from an external source or form
$newProducts = [
[
'name' => 'Laptop Pro',
'price' => 1200.00,
'quantity' => 100,
'created_at' => now(), // Important for timestamps
'updated_at' => now(), // Important for timestamps
],
[
'name' => 'Wireless Mouse',
'price' => 25.00,
'quantity' => 500,
'created_at' => now(),
'updated_at' => now(),
],
[
'name' => 'Laptop Pro', // This entry will be updated based on 'name'
'price' => 1250.00, // New price
'quantity' => 120, // New quantity
'created_at' => now(),
'updated_at' => now(),
],
];
Product::upsert(
$newProducts, // Array of records to insert/update
['name'], // Columns to use for unique identification (e.g., unique key/index)
['price', 'quantity', 'updated_at'] // Columns to update if a match is found
);
// This will:
// - Insert 'Wireless Mouse' (if 'name' doesn't exist)
// - Update 'Laptop Pro' with new price and quantity (if 'name' exists)
How it works: The `upsert` method efficiently inserts records that do not exist or updates them if a matching record already exists in the database. It takes an array of data, a set of columns that should be used to uniquely identify records (e.g., a unique index), and an array of columns that should be updated if a match is found. This method significantly reduces the number of database queries compared to individual `firstOrCreate` or `updateOrCreate` calls for batch operations.