Bulk insert or update for the given where-CodePudding

I have a function that gets $homes and $member_id as parameters. $homes is an array, $member_id is an integer.

$homes = array( 'home13', 'home21', 'home34', 'home44' );
$member_id = 5;

How do I insert/update into the following DB?

in the format:

id  home_name member_id
1   home13     5
2   home21     5
2   home34     5
2   home44     5

The function I used:

public function store($homes, $member_id) {
      foreach( $homes as $home ) {
            $data[] = [ 'member_id' => $member_id, 'home_name' => $home ];
        }
      Home::insert( $data );
}

which works fine while inserting. However, I need to update all records for a given $member_id if the member_id already exists.

For example, if the $member_id is 5 and $homes is now array( 'home54' ), all other home_name that are previously stored for the $member_id 5 should be deleted. so that the following would only remain.

id  home_name member_id
1   home54     5

CodePudding user response：

You can use Laravel's upsert() method

Example:

Home::upsert([
    ['home_name' => 'home54', 'member_id' => 5, 'id' => 1],
    ['home_name' => 'home13', 'member_id' => 5, 'id' => 2],
    ['home_name' => 'home21', 'member_id' => 5, 'id' => 3],
], ['member_id', 'id'], ['home_name']);

This method consists of three arguments.

Values to insert/update
Columns that uniquely identify the values
The values that need to be updated

Please note that you need to use a unique composite key to use upsert() method.

Example from Seeder class:

$table->unique(['member_id','id']);

Reference from Laravel docs: https://laravel.com/docs/9.x/eloquent#upserts

CodePudding user response：

You can use updateOrCreate method to achieve this

public function store($homes, $member_id) {
  foreach( $homes as $home ) {
        Home::updateOrCreate( 'member_id' => $member_id, 'home_name' => $home );
    }
  }