Eloquent updates multiple records in batches (update if they exist, insert if they do not exist)

Eloquent updates multiple records in batches (update if they exist, insert if they do not exist)

It is not a batch assignment of multiple fields in one record.

Similar to batch insert:

DB::table('users')->insert(array(
  array('email' => 'aaa@example.com', 'name' => 'zhangsan', 'age'=> 0),
  array('email' => 'bbb@example.com', 'name' => 'wangwu', 'age'=> 0),
  ...
));

Is there any similar sentence:

DB::table('users')->update( array(
  array('email' => 'aaa@example.com', 'name' => 'zhangsan', 'age'=> 20),
  array('email' => 'bbb@example.com', 'name' => 'wangwu', 'age'=> 25),
  array('email' => 'ccc@example.com', 'name' => 'chenliu', 'age'=> 50),
  ...
) , 'email' );

The functions implemented are:

1. When the query conditions exist, update the original data in batches;

例：
email='aaa@example.com'时， 'age'修改为 20,
email='bbb@example.com'时， 'age'修改为 25,
...

2. When the query conditions do not exist, insert data in batches.

例：
email='ccc@example.com'时， 'age'修改为 50,
...

My code is:

public function updateOrCreate (Request $request) {
  
  $insert_array = [];

  $datas = $request->all();

  foreach ($datas as $key=> $data) {
    $user = User::where('email', $data['email'])->first();
    if (!$user) {
        $insert_array[] = $data;
    // 更新原数据
    } else {
        $user->email = $data['email'];
        $user->age = $data['age'];
        $user->save();
    }
  }
  // 批量插入数据
  User::insert($insert_array);
}

The above code will cause performance problems when the number of updated data is more than a thousand!

Please advise, is there a better solution?

Please give me more advice.

Reply content:

Eloquent updates multiple records in batches (update if they exist, insert if they do not exist)

It is not a batch assignment of multiple fields in one record.

Similar to batch insert:

DB::table('users')->insert(array(
  array('email' => 'aaa@example.com', 'name' => 'zhangsan', 'age'=> 0),
  array('email' => 'bbb@example.com', 'name' => 'wangwu', 'age'=> 0),
  ...
));

Is there any similar sentence:

DB::table('users')->update( array(
  array('email' => 'aaa@example.com', 'name' => 'zhangsan', 'age'=> 20),
  array('email' => 'bbb@example.com', 'name' => 'wangwu', 'age'=> 25),
  array('email' => 'ccc@example.com', 'name' => 'chenliu', 'age'=> 50),
  ...
) , 'email' );

The functions implemented are:

1. When the query conditions exist, update the original data in batches;

例：
email='aaa@example.com'时， 'age'修改为 20,
email='bbb@example.com'时， 'age'修改为 25,
...

2. When the query conditions do not exist, insert data in batches.

例：
email='ccc@example.com'时， 'age'修改为 50,
...

My code is:

public function updateOrCreate (Request $request) {
  
  $insert_array = [];

  $datas = $request->all();

  foreach ($datas as $key=> $data) {
    $user = User::where('email', $data['email'])->first();
    if (!$user) {
        $insert_array[] = $data;
    // 更新原数据
    } else {
        $user->email = $data['email'];
        $user->age = $data['age'];
        $user->save();
    }
  }
  // 批量插入数据
  User::insert($insert_array);
}

The above code will cause performance problems when the number of updated data is more than a thousand!

Please advise, is there a better solution?

Please give me more advice.

1. Change the query to a DB operation
2. Do not have a query operation in foreach
3. You can assemble all emails into one query statement, and the server will compare which record currently exists