Updating multiple records using Laravel — explaining laravelBatch package

Sooner or later you are going to find yourself in situation where you need to update multiple database records with different values. Laravel supports mass update when there is only one possibility of value for given column. In the documentation we can find this example:

Flight::where('active', 1)
      ->where('destination', 'San Diego')
      ->update(['delayed' => 1]);

This is going to set attribute delayed to true for all flights that are active and have a destination San Diego. In this case, this is perfectly fine, it is going to use only one query and it should be pretty fast. But what if we want to update multiple records with different values ? For example, let’s imagine we have ‘users’ table and we need to update their ‘points’ column. Some users got 10 points, some 20 etc. There would be one query for every user we want to update.
If we had to update 5000 records we would need to use foreach loop and make 5000 queries. I tried to simulate this situation and compare how much time it would take to do the job. So I created database with 100 000 users, prepared data to update and run this inside foreachloop.

// $ids = array of random user ids
// $data = array of random numbers for points
for ($i = 0; $i <= 5000; $i++) {
    User::where(['id' => $ids[$i]])
       ->update(['points' => $data[$i]]);
}

On average it took 5.47676 seconds to update data.

We could wrap this inside transaction and it would have a big impact on performance

DB::transaction(function() use($ids, $data) {
    for ($i = 0; $i <= 5000; $i++) {
        User::where(['id' => $ids[$i]])
          ->update(['points' => $data[$i]]);
    }
});

We are talking about 1.68872 seconds on average. This is much better but can we improve this even further ?

Let’s try to do same job using batch update package for Laravel.

use Mavinoo\Batch\Batch;
use App\Models\User;
/* $updateData is array with  length of 5000 and structure like this:
 $updateData = [
  [’id' => 100, 'points' => 200],
  [’id' => 500, 'points' => 300] ...
]
*/
Batch::update(new User, $updateData, 'id');

First parameter is the model we want to update, the second is data and the third one is column that uniquely identifies records within the associated table.
This took only 1.0531 seconds on average and a single query. This was done using MySql’s CASE function.
Now let’s inspect this package and see how does update method works by focusing on the most important parts of the code.

public function update(Model $table, array $values, string $index = null, bool $raw = false)
$final = [];
$ids = [];
foreach ($values as $key => $val) {
    $ids[] = $val[$index];
foreach (array_keys($val) as $field) {
        if ($field !== $index) {

Let’s imagine that the first element of $updateData was this array:

[‘id' => 100, ‘points' => 230]
We have two nested foreach loops. First one is going to iterate over our $updateData which is array of 5000 arrays. It it going to extract value of ‘id’ from the first array and push it inside $idsarray.
$val[$index] would be equal to 100.

$finalField = $raw ? Common::mysql_escape($val[$field]):  "'" . Common::mysql_escape($val[$field]) . "'";

$value = (is_null($val[$field]) ? 'NULL' : 
$final[$field][] = 'WHEN `' . $index . '` = \'' . $val[$index] . '\' THEN ' . $value . ' ';
                    

Next loop is going to extract fields that need to be updated. In our case value of $field is going to be string ‘points’ and value of $finalFieldis going to be ‘230’. We are safe from SQL injection because these variables are passed through mysql_escape function.
Last step is to create array that has name of the column that needs to be updated as key and string which represents prepared statement for MySql’s CASE function as value. Then we push that array inside $final variable.
So after the execution of the second foreach loop, $finalarray is going to look like this:

array:1 [
  "points" => array:1 [
    0 => "WHEN `id` = '100' THEN '230' "
  ]
]

We are updating only ‘points’ column but if we were updating some other columns too, it would also add them:

array:1 [
  "points" => array:1 [
    0 => "WHEN `id` = '100' THEN '230' "
  ],
  "something" => array:1 [
    0 => "WHEN `id` = '100' THEN 'some_value' "
  ]
]

When both loops are done, $final['points'] array is going to consist of 5000 strings looking like this one above.

Finally, it is going to create one query string by combining values from $finaland $idsarrays.

$cases = '';
foreach ($final as $k => $v) {
    $cases .= '`' . $k . '` = (CASE ' . implode("\n", $v) . "\n"
        . 'ELSE `' . $k . '` END), ';
}
$query = "UPDATE `" . $this->getFullTableName($table) . "` SET " . substr($cases, 0, -2) . " WHERE `$index` IN(" . '"' . implode('","', $ids) . '"' . ");";

After this we would end up with $querystring that looks something like this

UPDATE `users` SET `points` = (CASE WHEN `id` = '100' THEN '230'
CASE WHEN `id` = '101' THEN '235'
...
) WHERE `id` IN("100","101" ...)

Special thanks to Mavinoo for this nice and simple package.
I think we can expect something like this to become part of the core Laravel framework in the near future.
Before I started using this package I wasn’t familiar with CASE function so it was weird to me how this package improves performances.
Now everything makes sense and hopefully you learned something too :)