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

问题描述

我是laravel的新用户，我正在尝试更新导航树. 因此，我想在一个查询中更新整个树，而无需foreach.

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')
);

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

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

我想更新所有内容，因为我的树中有字段_lft，_right，parent_id，我使用一些可拖动的js插件设置导航结构-现在我要保存它.

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.

我尝试使用

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?

推荐答案

我想知道为什么Laravel核心中尚不提供这种功能(直到今天).查看此要点查询字符串的结果如下所示:

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屋！