Laravel 5.5 - update or create bulk insert

Question

i am trying to preform update or create action on many records using laravel model. Normal insert with updateOrCreate works perfectly with foreach but i want to avoide it as it slowing things down. I have something like 200k records. Is there is any way to achive it? I tried this answer below https://stackoverflow.com/a/34815725/1239122 But it is not super elegant. Any ideas?

Answer 1

You can use insert() in Query Builder to bulk insert. But update statement needs conditions. Please read the document: https://laravel.com/docs/5.7/eloquent#updates

Answer 2

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

Answer 3

As far as I know, you can not do it for the update as you need to check the condition for the update, But you can do it for insert

  $data = array(
   array('name'=>'John', 'phone'=>'1234567890'),
   array('name'=>'Deo', 'phone'=>'9876543210'),
     //...
    );

Model::insert($data);