CODEWITHSUNDEEP
mysqllaravelupsert
The VaLo
The VaLo

Reputation: 65

Laravel upsert only if two columns conflict

I have the following table

id | invoice_id | position | item_name | qty
---|------------|----------|-----------|----
 1 |    1234    |   200    |   shaft   | 2
 2 |    1234    |   202    |   spear   | 1
 3 |    1235    |   200    |   shaft   | 10
 4 |    1235    |   202    |   spear   | 20

How can I "target" to update the first row (id is 1) only if invoice_id AND position match? If I would add a new item with position 500 for the invoice_id of 1234, it would insert a new row, otherwise if position is 200 or 202, it would update the existing (name or qty)?

I have seen the laravel's v8 upsert() function but it needs only one unique identifying column, and not in this case two.

Upvotes: 0

Views: 3020

Answers (1)

N69S
N69S

Reputation: 17206

Use the method updateOrCreate(). First parameter is the condition of unicity, the second paramerter is the reste of the attributes. It's the single row version of upsert()

Model::updateOrCreate(
    ['invoice_id' => '1234', 'position' => '500'],
    ['qty' => 5, 'item_name' => 'shaft']
);

upsert() is not limited to one unique column

Flight::upsert([
    ['invoice_id' => '1234', 'position' => '500', 'qty' => 5, 'item_name' => 'shaft'],
    ['invoice_id' => '1234', 'position' => '600', 'qty' => 10, 'item_name' => 'shaft']
], ['invoice_id', 'position'], ['qty']);

First parameter is for entries, second is for a list on identifying column and Thrid parameter is for the updated fields if a match is found.

All databases systems except SQL Server require the columns in the second argument provided to the upsert method to have a "primary" or "unique" index.

To be able to use the upsert method, you need to add a unique constraint on the composite key made out of invoice_id and position

Upvotes: 3

Related Questions