Laravel 插入或更新多行 [英] Laravel insert or update multiple rows

问题描述

我是 laravel 的新手，我正在尝试更新我的导航树.所以我想在一个不使用 foreach 的查询中更新我的整个树.

数组(array('id'=>1, 'name'=>'一些导航点', 'parent'='0'),array('id'=>2, 'name'=>'一些导航点', 'parent'='1'),array('id'=>3, 'name'=>'一些导航点', 'parent'='1'));

我只是想问一下 - laravel 中是否可以插入(如果数组中是新的)或更新我在数据库中的当前行?

我想全部更新，因为我的树中有字段 _lft、_right、parent_id 并且我使用一些可拖动的 js 插件来设置我的导航结构 - 现在我想保存它.

我尝试使用

Navigation::updateOrCreate(array(array('id' => '3'), array('id'=>'4')), array(array('name' =>'test11'), array('name' => 'test22')));

但它只适用于单行，而不是我尝试做的多行.也许还有其他方法可以做到这一点?

解决方案

我想知道为什么 Laravel 核心还没有这种功能(直到今天).查看 this gist 查询字符串的结果如下所示:这里

我将代码放在这里以防万一将来链接断开，我不是作者:

/*** Laravel 4/5 的批量(批量)插入或更新副本** 插入或更新([* ['id'=>1,'value'=>10],* ['id'=>2,'值'=>60]* ]);*** @param 数组 $rows*/函数插入或更新(数组 $rows){$table = DB::getTablePrefix().with(new self)->getTable();$first = 重置($rows);$columns = implode(',',array_map( 函数( $value ) { return "$value"; } , array_keys($first) ));$values = implode(',', array_map( function( $row ) {return '('.implode(',',array_map( 函数( $value ) { return '"'.str_replace('"', '""', $value).'"'; } , $row )).')';} ， $ 行));$updates = implode(',',数组映射(函数($value){返回$value = VALUES($value)"；}，array_keys($first)));$sql = "INSERT INTO {$table}({$columns}) VALUES {$values} ON DUPLICATE KEY UPDATE {$updates}";返回 DB::statement( $sql );}

因此，您可以安全地将数组插入或更新为:

insertOrUpdate(大批(array('id'=>1, 'name'=>'一些导航点', 'parent'='0'),array('id'=>2, 'name'=>'一些导航点', 'parent'='1'),array('id'=>3, 'name'=>'一些导航点', 'parent'='1')));

如果函数的第一行有任何问题，您可以简单地添加一个表名作为第二个参数，然后注释掉该行，即:

函数 insertOrUpdate(array $rows, $table){......}insertOrUpdate(myarrays,'MyTableName');

<块引用>

注意:请注意清理您的输入！并记住时间戳字段没有被触及.您可以通过手动添加到主数组中的每个数组来做到这一点.

Im new in laravel, and im trying to update my navigation tree. So i want to update my whole tree in one query without foreach.

array(
  array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
  array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
  array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
);

I just want to ask - is there posibility in laravel to insert(if new in array) or update my current rows in database?

I want to update all, because i have fields _lft, _right, parent_id in my tree and im using some dragable js plugin to set my navigation structure - and now i want to save it.

I tried to use

Navigation::updateOrCreate(array(array('id' => '3'), array('id'=>'4')), array(array('name' => 'test11'), array('name' => 'test22')));

But it works just for single row, not multiple like i tried to do. Maybe there is another way to do it?

解决方案

I wonder why this kind of feature is not yet available in Laravel core (till today). Check out this gist The result of the query string would look like this: here

I am putting the code here just in case the link breaks in the future, I am not the author:

/**
* 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 );
}

So you can safely have your arrays inserted or updated as:

insertOrUpdate(
    array(
      array('id'=>1, 'name'=>'some navigation point', 'parent'='0'),
      array('id'=>2, 'name'=>'some navigation point', 'parent'='1'),
      array('id'=>3, 'name'=>'some navigation point', 'parent'='1')
    )
);

Just in case any trouble with the first line in the function you can simply add a table name as a second argument, then comment out the line i.e:

function insertOrUpdate(array $rows, $table){
    .....
}

insertOrUpdate(myarrays,'MyTableName');

NB: Be careful though to sanitise your input! and remember the timestamp fields are not touched. you can do that by adding manually to each arrays in the main array.

这篇关于Laravel 插入或更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！