upsert() is just inserting not updating by unique keys.

hi everyone, i use upsert() but it just inserting and creating duplicate records. here is an example of my code:

migration:

 			$table->bigIncrements('id');
            $table->foreignId('user_id')->constrained('users');
            $table->morphs('model');
            $table->foreignId('owner_id')->constrained('users');
            $table->decimal('percentage', 5, 1);
            $table->softDeletes();
            $table->timestamp('created_at')->nullable();

            $table->unique(['model_type', 'model_id', 'owner_id']);

controller:


Shareholder::upsert([
	[
	'model_type' =>  'translator',
	'model_id' =>  1,
	'owner_id' =>  100,
	'percentage' => 20
	],
	[
	'model_type' =>  'translator',
	'model_id' =>  1,
	'owner_id' =>  100,
	'percentage' => 20
	],
], 
['id', 'model_type', 'model_id', 'owner_id'], 
['percentage']);

thank you.

LaryAI

1 year ago

Level 58

The issue you're encountering with the upsert() method in Laravel is likely due to the incorrect specification of the unique key constraints and the conflict columns. The upsert() method requires you to specify the unique columns that should be used to determine if a record should be updated or inserted.

In your case, the unique constraint is on the combination of model_type, model_id, and owner_id. Therefore, you should use these columns as the conflict columns in the upsert() method.

Here's the corrected code for your controller:

Shareholder::upsert([
    [
        'model_type' => 'translator',
        'model_id' => 1,
        'owner_id' => 100,
        'percentage' => 20,
    ]
], 
['model_type', 'model_id', 'owner_id'], 
['percentage']);

Explanation:

Data Array: The data array should be wrapped in an additional array to ensure it is treated as a batch insert.
Conflict Columns: The conflict columns should be ['model_type', 'model_id', 'owner_id'] because these are the columns that form the unique constraint.
Update Columns: The columns to be updated in case of a conflict should be ['percentage'].

This should ensure that the upsert() method correctly updates the existing record if a conflict is found based on the unique key constraint, rather than inserting a duplicate record.

shadkamel

1 year ago

Level 50

i just updated my code.

MohamedTammam

1 year ago

Level 51

You're adding the id to your insert array. The way upsert works is by comparing your values that you provided against the columns you provided. If there're a record with the same values for these columns, the record will be updated, otherwise a new record will be added.

Remove the id from your second argument.

Shareholder::upsert([
	[
	'model_type' =>  'translator',
	'model_id' =>  1,
	'owner_id' =>  100,
	'percentage' => 20
	],
	[
	'model_type' =>  'translator',
	'model_id' =>  1,
	'owner_id' =>  100,
	'percentage' => 20
	],
], 
['model_type', 'model_id', 'owner_id'], 
['percentage']);

2 likes

shadkamel

1 year ago

Level 50

@MohamedTammam sorry i didn't understand which id do you mean please ? model_id or owner_id ?

shadkamel

1 year ago

Level 50

@MohamedTammam oh i got it, but still same problem.

MohamedTammam

1 year ago

Level 51

@shadkamel https://laravel.com/docs/11.x/eloquent#upserts

All databases except SQL Server require the columns in the second argument of the upsert method to have a "primary" or "unique" index. In addition, the MySQL database driver ignores the second argument of the upsert method and always uses the "primary" and "unique" indexes of the table to detect existing records.

Try using updateOrCreate if upsert not working.

2 likes

shadkamel

1 year ago

Level 50

@MohamedTammam Ok, thank you for your reply.

Please or to participate in this conversation.