Laravel insert or update multiple rows

Question

Im new in laravel and I'm trying to update my navigation tree. So I want to update the whole tree in one request 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 it possible in laravel to insert (if new in an array) or update my current rows in the database?

I want to update everything because I have the _lft, _right, parent_id fields in my tree and im using some kind of smooth js plugin to set up 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 only works for one line, not several, as I tried to do. Maybe there is another way to do this?

Answer 1

, Laravel ( ). :

, , :

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

, :

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

, , , , :

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

insertOrUpdate(myarrays,'MyTableName');

NB: , ! , . , .

Answer 2

public function meta(){ // in parent models.
    return $this->hasMany('App\Models\DB_CHILD', 'fk_id','local_fk_id');
}

, ,

$parent= PARENT_DB::findOrFail($id);
$metaData= [];
        foreach ($meta['meta'] as $metaKey => $metaValue) {

            if ($parent->meta()->where([['meta_key', '=',$metaKey]] )->exists()) { 
                $parent->meta()->where([['meta_key', '=',$metaKey]])->update(['meta_value' => $metaValue]);
            }else{
                $metaData[] = [
                    'FK_ID'=>$fkId,
                    'meta_key'=>$metaKey,
                    'meta_value'=> $metaValue
                ];
            }

        }
$Member->meta()->insert($metaData);

Answer 3

No, you cannot do this. You can have insert()multiple lines at the same time, and you can update()use multiple lines to use the same condition where(), but if you want to use updateOrCreate(), you need to use a loop foreach().

Answer 4

use a database in your controller; public function arrDta () {

        $up_or_create_data=array(
            array('id'=>2, 'name'=>'test11'),
            array('id'=>4, 'name'=>'test22')

        );

        var_dump($up_or_create_data);
        echo "fjsdhg";

        foreach ($up_or_create_data as $key => $value) {
            echo "key  ".$key;
            echo "<br>";
            echo " id: ".$up_or_create_data[$key]["id"];
            echo "<br>";
            echo " Name: ".$up_or_create_data[$key]["name"];


        if (Navigation::where('id', '=',$up_or_create_data[$key]["id"])->exists()) {
            DB::table('your_table_ name')->where('id',$up_or_create_data[$key]["id"])->update(['name' => $up_or_create_data[$key]["name"]]);
        }else{
            DB::insert('insert into your_table_name (id, name) values (?, ?)', [$up_or_create_data[$key]["id"], $up_or_create_data[$key]["name"]]);
        }

        }

Answer 5

I created a UPSERT package for all databases: https://github.com/staudenmeir/laravel-upsert

DB::table('navigation')->upsert(
    [
        ['id' => 1, 'name' => 'some navigation point', 'parent' => '0'],
        ['id' => 2, 'name' => 'some navigation point', 'parent' => '1'],
        ['id' => 3, 'name' => 'some navigation point', 'parent' => '1'],
    ],
    'id'
);