Method Example of Realizing Batch Update of Multiple Records by laravel

Preface

I believe children who are familiar with laravel know that laravel inserts multiple records once in batches, but does not update multiple records once according to conditions.

Do you envy saveAll of thinkphp and update_batch of ci, but why doesn't such an elegant laravel have a similar batch update method?

Master is in the folk

After reading Google for 1 time, it was found that someone had written it on stackoverflow (https://stackoverflow.com/questions/26133977/laravel-bulk-update), but it could not prevent sql injection.

In this paper, the Eloquent combined with laravel is adjusted, which can effectively prevent sql injection.

Sample code

<?php
namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;

/**
 *  Student table model 
 */
class Students extends Model
{
 protected $table = 'students';

 // Batch update 
 public function updateBatch($multipleData = [])
 {
  try {
   if (empty($multipleData)) {
    throw new \Exception(" Data cannot be empty ");
   }
   $tableName = DB::getTablePrefix() . $this->getTable(); //  Table name 
   $firstRow = current($multipleData);

   $updateColumn = array_keys($firstRow);
   //  Default to id Is a conditional update, if there is no ID Then in the first 1 Fields are conditions 
   $referenceColumn = isset($firstRow['id']) ? 'id' : current($updateColumn);
   unset($updateColumn[0]);
   //  Splice sql Statement 
   $updateSql = "UPDATE " . $tableName . " SET ";
   $sets  = [];
   $bindings = [];
   foreach ($updateColumn as $uColumn) {
    $setSql = "`" . $uColumn . "` = CASE ";
    foreach ($multipleData as $data) {
     $setSql .= "WHEN `" . $referenceColumn . "` = ? THEN ? ";
     $bindings[] = $data[$referenceColumn];
     $bindings[] = $data[$uColumn];
    }
    $setSql .= "ELSE `" . $uColumn . "` END ";
    $sets[] = $setSql;
   }
   $updateSql .= implode(', ', $sets);
   $whereIn = collect($multipleData)->pluck($referenceColumn)->values()->all();
   $bindings = array_merge($bindings, $whereIn);
   $whereIn = rtrim(str_repeat('?,', count($whereIn)), ',');
   $updateSql = rtrim($updateSql, ", ") . " WHERE `" . $referenceColumn . "` IN (" . $whereIn . ")";
   //  Afferent preprocessing sql Statement and corresponding bound data 
   return DB::update($updateSql, $bindings);
  } catch (\Exception $e) {
   return false;
  }
 }
}

You can adjust it according to your own needs. Here are examples of usage:

//  Arrays to Bulk Update 
$students = [
 ['id' => 1, 'name' => ' Zhang 3', 'email' => 'zhansan@qq.com'],
 ['id' => 2, 'name' => ' Li 4', 'email' => 'lisi@qq.com'],
];

//  Batch update 
app(Students::class)->updateBatch($students);

The generated SQL statement is as follows:

UPDATE pre_students
SET NAME = CASE
WHEN id = 1 THEN
 ' Zhang 3'
WHEN id = 2 THEN
 ' Li 4'
ELSE
 NAME
END,
 email = CASE
WHEN id = 1 THEN
 'zhansan@qq.com'
WHEN id = 2 THEN
 'lisi@qq.com'
ELSE
 email
END
WHERE
 id IN (1, 2)

Is the efficiency improved by a lot ~

Summarize