CODEWITHSUNDEEP
laraveleloquentupsert
Denis Spalenza
Denis Spalenza

Reputation: 753

Laravel 8 upsert doesn't work as expected

I'm trying use upsert as explained here (https://stackoverflow.com/a/66200086/1240380) and in 8.x doc but it is not working as expected.

CartItem::upsert(
    [
        [
            "id" => 105,
            "unit_price" => 146.5124,
            "price" => 293.0248,
            "unit_taxes" => 28.22,
            "taxes" => 56.44,
            "custom_fields" => '{"options":{"estado":15,"user_id":1},"calculated_price":146.5124,"calculated_tax_price":28.22}'
        ],
        [
            "id" => 106,
            "unit_price" => 113.8824,
            "price" => 227.7648,
            "unit_taxes" => 11.54,
            "taxes" => 23.08,
            "custom_fields" => '{"options":{"estado":15,"user_id":1},"calculated_price":"113.8824","calculated_tax_price":"11.539999999999999"}'
        ],
    ],
    'id'
);

The code above is producing the following error

Illuminate\Database\QueryException

SQLSTATE[HY000]: General error: 1364 Field 'cart_id' doesn't have a default value (SQL: insert into cart_items (created_at, custom_fields, id, price, taxes, unit_price, unit_taxes, updated_at) values (2022-01-14 20:41:44, {"options":{"estado":15,"user_id":1},"calculated_price":146.5124,"calculated_tax_price":28.22}, 105, 293.0248, 56.44, 146.5124, 28.22, 2022-01-14 20:41:44), (2022-01-14 20:41:44, {"options":{"estado":15,"user_id":1},"calculated_price":"113.8824","calculated_tax_price":"11.539999999999999"}, 106, 227.7648, 23.08, 113.8824, 11.54, 2022-01-14 20:41:44) on duplicate key update id = values(id), unit_price = values(unit_price), price = values(price), unit_taxes = values(unit_taxes), taxes = values(taxes), custom_fields = values(custom_fields), updated_at = values(updated_at))

The id column is the PK of the table. I also tried using the third param without success.

I cannot understand why it is not working as expected. For some reason the rows exists but they are not detected. The error suggests that laravel is trying creating new rows instead of update the previous one.

Is there something missing?

The rows in question: enter image description here

The table definition: data table definition

EDIT 1: About the columns types, price and unit_price are int but I tried float because in the CartItem I have they configured in $casts variable to cast them to "Money" type (saving them as cents). I tried with int values but I got the same result.

Upvotes: 1

Views: 10812

Answers (2)

javarexma
javarexma

Reputation: 21

Sorry for the late response. I've experienced this a while ago and I figured out that this exception will occur when your associative array will not match in all the fields of your table unless all your fields is nullable. Upsert will execute the create method if the unique identifier not already in the table. So make sure that the non nullable field must included in the array that will pass in the first parameter of the upsert.

Upvotes: 2

Tomir Schmite Jr.
Tomir Schmite Jr.

Reputation: 588

Laravel upserts do a single query containing all the rows are made while using upsert which includes on duplicate key update in MySQL and on conflict ... do update set in Postgres. This command instructs database to update record if it already exists. You need to pass cart_id to MySQL tries do INSERT before get errors on duplicate.

https://laravelproject.com/laravel-upsert/

Upvotes: 1

Related Questions