CODEWITHSUNDEEP
phplaravelinsertrow
Mateusz Kudej
Mateusz Kudej

Reputation: 497

Laravel insert or update multiple rows

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?

Upvotes: 20

Views: 57038

Answers (7)

Abdelalim Hassouna
Abdelalim Hassouna

Reputation: 768

It's now available in Laravel >= 8.x

The method's first argument consists of the values to insert or update, while the second argument lists the column(s) that uniquely identify records within the associated table. The method's third and final argument is an array of columns that should be updated if a matching record already exists in the database:

Flight::upsert([
    ['departure' => 'Oakland', 'destination' => 'San Diego', 'price' => 99],
    ['departure' => 'Chicago', 'destination' => 'New York', 'price' => 150]
], ['departure', 'destination'], ['price']);

Upvotes: 24

Eliyas Hossain
Eliyas Hossain

Reputation: 620

I didn't find a way to bulk insert or update in one query. But I have managed with only 3 queries. I have one table name shipping_costs. Here I want to update the shipping cost against the shipping area. I have only 5 columns in this table id, area_id, cost, created_at, updated_at.

// first get ids from table
$exist_ids = DB::table('shipping_costs')->pluck('area_id')->toArray();
// get requested ids
$requested_ids = $request->get('area_ids');
// get updatable ids
$updatable_ids = array_values(array_intersect($exist_ids, $requested_ids));
// get insertable ids
$insertable_ids = array_values(array_diff($requested_ids, $exist_ids));
// prepare data for insert
$data = collect();
foreach ($insertable_ids as $id) {
$data->push([
    'area_id' => $id,
    'cost' => $request->get('cost'),
    'created_at' => now(),
    'updated_at' => now()
]);
}
DB::table('shipping_costs')->insert($data->toArray());

// prepare for update
DB::table('shipping_costs')
->whereIn('area_id', $updatable_ids)
->update([
    'cost' => $request->get('cost'),
    'updated_at' => now()
]);

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

I've created an 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'
);

Upvotes: 9

Xebio
Xebio

Reputation: 31

Eloquent Style

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

Upvotes: 3

omitobi
omitobi

Reputation: 7334

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.

Upvotes: 17

Borna
Borna

Reputation: 548

in your controller use DB; 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"]]);
        }

        }

Upvotes: -1

Alexey Mezenin
Alexey Mezenin

Reputation: 163748

No, you can't do this. You can insert() multiple rows at once and you can update() multiple rows using same where() condition, but if you want to use updateOrCreate(), you'll need to use foreach() loop.

Upvotes: 1

Related Questions