如何使用雄辩/流利的单个查询更新多行? [英] How to update multiple rows from a single query using eloquent/fluent?

问题描述

我正在学习如何使用口才/流利的 解决方案

您可能会从此大量插入内容或更新要点中找到灵感:

/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
* 
* insertOrUpdate([
*   ['id'=>1,'value'=>10],
*   ['id'=>2,'value'=>60]
* ]);
* 
*
* @param array $rows
*/

function insertOrUpdate(array $rows){
    $table = \DB::getTablePrefix().with(new self)->getTable();


    $first = reset($rows);

    $columns = implode( ',',
        array_map( function( $value ) { return "$value"; } , array_keys($first) )
    );

    $values = implode( ',', array_map( function( $row ) {
            return '('.implode( ',',
                array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
            ).')';
        } , $rows )
    );

    $updates = implode( ',',
        array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
    );

    $sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";

    return \DB::statement( $sql );

}

参考: https://gist.github.com/RuGa/5354e44883c7651fd15c

我认为我不需要提供任何解释，因为该函数中的每个代码块都在讲述自己.

I was learning about How to insert multiple rows from a single query using eloquent/fluent and I found the answer here

Can somebody share any documentation about how to update bulk rows in single query?

My queries are below.

Update tblrole set role = 'Super Admin' where RoleID = 1;
Update tblrole set role = 'Super Admin A' where RoleID = 2;
Update tblrole set role = 'Super Admin B' where RoleID = 3;
Update tblrole set role = 'Super Admin C' where RoleID = 4;

解决方案

You might find inspiration from this mass insert or update gist:

/**
* Mass (bulk) insert or update on duplicate for Laravel 4/5
* 
* insertOrUpdate([
*   ['id'=>1,'value'=>10],
*   ['id'=>2,'value'=>60]
* ]);
* 
*
* @param array $rows
*/

function insertOrUpdate(array $rows){
    $table = \DB::getTablePrefix().with(new self)->getTable();


    $first = reset($rows);

    $columns = implode( ',',
        array_map( function( $value ) { return "$value"; } , array_keys($first) )
    );

    $values = implode( ',', array_map( function( $row ) {
            return '('.implode( ',',
                array_map( function( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )
            ).')';
        } , $rows )
    );

    $updates = implode( ',',
        array_map( function( $value ) { return "$value = VALUES($value)"; } , array_keys($first) )
    );

    $sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";

    return \DB::statement( $sql );

}

Ref: https://gist.github.com/RuGa/5354e44883c7651fd15c

I don't think I need to provide any explanation as each chunk of code in the function speaks of itself.

这篇关于如何使用雄辩/流利的单个查询更新多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！