Laravel implements batch update of multiple records.

Laravel implements batch update of multiple records.

Preface

I believe that all the children's shoes familiar with laravel know that laravel has batch inserts multiple records at a time, but does not update multiple records by condition at a time.

Do you envy the saveAll of thinkphp and the update_batch of ci? But how can laravel, with such elegance, do you not have a similar batch update method?

Easy to learn

Google, found that stackoverflow (https://stackoverflow.com/questions/26133977/laravel-bulk-update) has been written, but does not prevent SQL injection.

This article, combined with laravel's Eloquent, makes adjustments 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 '; // update public function updateBatch ($ multipleData = []) {try {if (empty ($ multipleData) {throw new \ Exception ("data cannot be blank ");} $ tableName = DB: getTablePrefix (). $ this-> getTable (); // table name $ firstRow = current ($ multipleData); $ updateColumn = array_keys ($ firstR Ow); // by default, id is used as the condition for updating. If no ID is available, $ referenceColumn = isset ($ firstRow ['id']) is used as the condition. 'Id': current ($ updateColumn); unset ($ updateColumn [0]); // concatenate an 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. ")"; // input the pre-processing SQL statement and the corresponding bound data return DB: update ($ updateSql, $ bindings);} catch (\ Exception $ e) {return false ;}}}

You can make adjustments based on your needs. The following is an example of usage:

// Array to be updated in batches $ students = [['id' => 1, 'name' => 'zhang san', 'email '=> 'zhansan @ qq.com'], ['id' => 2, 'name' => 'lily', 'email '=> 'lisi @ qq.com'],]; // update apps in batches (Students :: class)-> updateBatch ($ students );

The generated SQL statement is as follows:

UPDATE pre_studentsSET NAME = CASEWHEN id = 1 THEN 'zhang san' WHEN id = 2 THEN 'Li si' else nameend, email = CASEWHEN id = 1 THEN 'zhansan @ qq.com 'WHEN id = 2 then' lisi @ qq.com' ELSE emailENDWHERE id IN (1, 2)

Is efficiency greatly improved ~

Summary

The above is all the content of this article. I hope the content of this article has some reference and learning value for everyone's learning or work. If you have any questions, please leave a message to us, thank you for your support.