PHP
Performing Efficient Batch Upserts with Eloquent
Efficiently insert or update multiple records in a single query using Laravel Eloquent's `upsert` method, reducing database load and improving performance.
// app/Models/PageView.php
namespace App/Models;
use Illuminate/Database/Eloquent/Model;
class PageView extends Model
{
protected $fillable = ['url', 'views_count', 'last_viewed_at'];
public $timestamps = false; // No default timestamps for this example
}
// In a controller or service:
use App/Models/PageView;
use Carbon/Carbon;
$pageData = [
[
'url' => '/products/item-a',
'views_count' => 1,
'last_viewed_at' => Carbon::now()->toDateTimeString(),
],
[
'url' => '/about-us',
'views_count' => 1,
'last_viewed_at' => Carbon::now()->toDateTimeString(),
],
[
'url' => '/products/item-a', // This one will be updated if 'url' is the unique key
'views_count' => 1,
'last_viewed_at' => Carbon::now()->toDateTimeString(),
],
];
// Perform the upsert operation
PageView::upsert(
$pageData,
['url'], // Columns to use for unique identification (if match, update)
['views_count', 'last_viewed_at'] // Columns to update if a match is found
);
// To demonstrate incrementing specifically for the updated item (more advanced):
$pageDataForIncrement = [
['url' => '/products/item-b', 'views_count' => 1, 'last_viewed_at' => Carbon::now()->toDateTimeString()],
['url' => '/products/item-a', 'views_count' => 1, 'last_viewed_at' => Carbon::now()->toDateTimeString()],
];
PageView::upsert(
$pageDataForIncrement,
['url'],
[
'views_count' => \DB::raw('views_count + VALUES(views_count)'), // Increment by the new value
'last_viewed_at'
]
);
$productA_views = PageView::where('url', '/products/item-a')->first();
echo $productA_views->views_count; // Now will be 2 (original 1 + 1 from increment)
How it works: The `upsert` method in Eloquent allows you to efficiently insert records that do not exist, or update them if a matching record (based on unique columns) already exists. This is performed in a single database query, significantly improving performance for batch operations compared to iterating and performing individual `firstOrCreate` or `updateOrCreate` calls. You define the data array, the unique columns for identification, and the columns to update if a match is found. You can even use raw database expressions for update logic.