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?
4 Answers
You can try this library for batch insert and update.
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()
]);
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);
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
Related