Bulk update models with differents values in Laravel

Using Laravel query builder or Eloquent, you may need to execute a bulk update on a table, it's easy:

Character::query()
    ->whereKey([45, 42, 53])
    ->update('life' => 100);

But what if you want to update each row with a different value? In such case, many of you will say "it's only possible with a loop".
Like so:

$newLifeValues = [
    45 => 100,
    42 => 59,
    53 => 25,
    // ...
];

foreach ($newLifeValues as $key => $newLifeValue) {
    Character::query()
        ->whereKey($key)
        ->update('life' => $newLifeValue);

}

Reading the documentation, this is the only way. But we can imagine a way to do by ourselves, using an upsert statement.
The upsert takes three arguments:

a two dimensional array representing each row values
a "uniqueBy" rule
the list of columns to be updated

In our case, we want to update the character's life and check the unique rules by the model primary key, so we can execute this upsert statement:

$newLifeValues = [
    ['id' => 45, 'life' => 100],
    ['id' => 42, 'life' => 59],
    ['id' => 53, 'life' => 25],
    // ...
];

Character::query()->upsert(
        $newLifeValues, // The row description, containing the primary key and new life value
        ['id'], // The uniqueBy rule
        ['life'] // The columns to be updated
    );

Using this upsert statement, only one query will be executed in database, which is way more efficient when you need to update many rows in a table.

Caution using this trick

With great power comes great responsibility.

— Benjamin Parker

Warning 1: don't insert unwanted data

The "upsert" term is called like so as the concatenation of "update or insert" terms. This means that if the uniqueBy rule doesn't create a conflict in the table, the DBMS will try to insert the row.

Using our case as an example, if there is no model identified by the 53 id key, it will be inserted!

To prevent from this, you can prepend the upsert statement by a select statement:

$newLifeValues = [
    ['id' => 45, 'life' => 100],
    ['id' => 42, 'life' => 59],
    ['id' => 53, 'life' => 25],
    // ...
];

// Check in the database for existing rows by the unique rule
$existingCharacters =
    Character::query()
        ->whereKey(array_column($newLifeValues, 'id'))
        ->get('id');

$newLifeValues = array_filter(
    $newLifeValues,
    fn ($row) => in_array($row['id'], $existingCharacters->pluck('id')
);

Character::query()->upsert($newLifeValues, ['id'], ['life']);

This is still way more efficient to do than some hundreds or thousands update statements using a loop!

Warning 2: provides more data than needed

Because the upsert (or "insert on conflict") SQL statement may insert a new fresh row, it means that all the table needed column (eg: not null) should be part of the query.

Let's imagine this table:

               characters table                
| id | life(default: 100) | pseudo (not null) |
| 42 |         50         |         Maz       |
| 45 |         75         |         Neo       |
| 53 |         98         |         Lau       |

You won't be able to execute the previous queries, because you don't provide any pseudo data in your query!
To prevent from this, you can give more information than you need to update.

$newLifeValues = [
    ['id' => 45, 'life' => 100],
    ['id' => 42, 'life' => 59],
    ['id' => 53, 'life' => 25],
    // ...
];

$upsertData =
    Character::query()
        // Retrieve the rows we want to update, including the pseudos!
        ->whereKey(array_column($newLifeValues, 'id'))
        ->get()

        // Modify the just selected data to fit the update values
        ->map(function (Character $character) {
            // Retrieve the row from the updated array
            $newCharacterLifeRow = Arr::first($newLifeValues, fn ($row) => $row['id'] === $character->id);

            $character->life = $newCharacterLifeRow['life'];

            // Here we return the whole Character's attributes
            return $character->getAttributes();
        })

        // Convert to an array to be used directly in the upsert method
        ->toArray();

    Character::query()->upsert($upsertData, ['id'], ['life']);

Again: one select and one upsert query, instead of hundreds or thoushands queries!

Bulk update models with differents values in Laravel

Caution using this trick

Warning 1: don't insert unwanted data

Warning 2: provides more data than needed

More reads

Comments

Add a comment