Implementation method of batch update multiple records in Laravel

Preface

Believe that familiar with laravel children's shoes are known, laravel have a batch to insert multiple records at once, but there is no one-time update multiple records by conditions.

Whether Envy thinkphp SaveAll, whether Envy ci update_batch, but so elegant laravel how there is no similar batch update method?

Master in the Civil

Google a bit, found that StackOverflow (Https://stackoverflow.com/questions/26133977/laravel-bulk-update) has been written, However, SQL injection cannot be prevented.

This article, combined with Laravel's eloquent, has been adapted to effectively prevent SQL injection.

Sample code

<?phpnamespace app\models;use db;use illuminate\database\eloquent\model;/** * Student table Model */class Students extends Model{ Protected $table = ' students '; Bulk Update public Function updateBatch ($multipleData = []) {try {if (empty ($multipleData)) {throw new \exception ("Data not   can be empty "); } $tableName = Db::gettableprefix (). $this->gettable ();   Table name $firstRow = current ($multipleData);   $updateColumn = Array_keys ($firstRow); The default is update with ID, if no ID is the first field $referenceColumn = Isset ($firstRow [' id '])?   ' ID ': current ($updateColumn);   unset ($updateColumn [0]); Stitching the 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.   ")";  Incoming preprocessing SQL statements and corresponding binding data return Db::update ($UPDATESQL, $bindings);  } catch (\exception $e) {return false; } }}

Can be adjusted according to their own needs, the following are examples of usages:

To batch update the array $students = [' id ' = ' + 1 ', ' name ' = ' Zhang San ', ' email ' = ' zhansan@qq.com '], [' id ' = + 2, ' name ' = ' Lee ' Four ', ' email ' = ' lisi@qq.com '],];//volume update app (Students::class)->updatebatch ($students);

The resulting SQL statement is as follows:

UPDATE Pre_studentsset NAME = casewhen id = 1 Then ' Zhang San ' when id = 2 Then ' John Doe ' ELSE nameend, e-mail = casewhen id = 1 Then ' en Ansan@qq.com ' When id = 2 and ' lisi@qq.com ' ELSE emailendwhere ID in (1, 2)