Laravel insert or update multiple rows

Question

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?

Answer 1

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.