Laravel update multiple rows with different values

Using the upsert method:

When it comes to managing and updating data in your Laravel application, there are times when you need to update multiple rows in a database table with different values. Whether you’re implementing a bulk update feature, processing a batch job, or simply need to modify several records at once, Laravel provides a powerful and efficient way to accomplish this task.

As of Laravel 8, there is a helpful upsert method that does exactly that. The method accepts 3 parameters, the first being a multidimensional array of data, the second is another array where the values are columns of the first multidimensional array parameter to try and find those existing records and lastly the third is a last array of the columns that will be updated.

Before diving into the code, let’s set the stage with a common use case. Let’s imagine that we have many users that have attended a meeting. Only after the meeting has finished, a request is made to update when the users had last attended the meeting and the time is recorded against when a user has left the meeting.

<?php

use App\Models\User;

$users = [
    [
        'email' => 'chris@test.com',
        'last_meeting_at' => '2023-09-12 09:07:55',
        'last_meeting_id' => 10,
    ],
    [
        'email' => 'james@test.com',
        'last_meeting_at' => '2023-09-12 09:10:03',
        'last_meeting_id' => 10,
    ],
    [
        'email' => 'emily@test.com',
        'last_meeting_at' => '2023-09-12 09:10:03',
        'last_meeting_id' => 10,
    ],
];

User::upsert($users, ['email'], ['last_meeting_at', 'last_meeting_id']);

Older versions of Laravel:

Another alternative approach if using an older version of Laravel might be to group update users by the same column value i.e.

<?php

use App\Models\User;

$meetingId = 10;
$users = collect([
    [
        'email' => 'chris@test.com',
        'last_meeting_at' => '2023-09-12 09:07:55',
        'last_meeting_id' => 10,
    ],
    [
        'email' => 'james@test.com',
        'last_meeting_at' => '2023-09-12 09:10:03',
        'last_meeting_id' => 10,
    ],
    [
        'email' => 'emily@test.com',
        'last_meeting_at' => '2023-09-12 09:10:03',
        'last_meeting_id' => 10,
    ],
]);

$users->groupBy('last_meeting_at')
    ->each(function ($users, $lastMeetingAt) use ($meetingId) {
        User::query()
            ->whereIn('email', $users->pluck('email'))
            ->update([
                'last_meeting_at' => $lastMeetingAt,
                'last_meeting_id' => $meetingId,
            ]);
    });

Performance notes:

Lastly, an alternative approach would be to simply loop through each user and update each row individually which is certainly not ideal and causes the N+1 issue.

foreach ($users as $user) {
    User::query()
        ->where('email', $user['email'])
        ->update([
            'last_meeting_at' => '2023-09-12 09:10:03',
            'last_meeting_id' => 10,
        ]);
}

To best handle this, it might be an idea to run this through a command or on a queued job.