Handling Upsert stale error

This works perfectly when there is just one order in the order table.

def create_cart_items(attrs \\ %{}) do
    on_conflict =
      from c in CartItems,
        where: c.order_id == ^attrs.order_id and c.user_id == ^attrs.user_id,
        update: [inc: [quantity: ^attrs.quantity]]
    {:ok, cart_item} =
      %CartItems{}
      |> CartItems.changeset(attrs)
      |> Repo.insert(
        on_conflict: on_conflict,
        conflict_target: :item_id,
        returning: true
      )
    attrs = Map.from_struct(cart_item)
    cart_item |> update_cart_items(%{quantity: attrs.quantity, item_id: attrs.item_id})

But once there is more than one order in the order table i get a:

[debug] ** (Ecto.StaleEntryError) attempted to insert a stale struct:

I need some help here. Maybe I am doing something wrong here

Perhaps because the conflict target is the item_id column, but your on_conflict query does not actually match on that and so it ends up trying to update columns that aren’t actually in conflict … in any case, you can also just pass the update list to on_conflict. There is no need for a whole other query:

  on_conflict = [inc: [quantity: ^attrs.quantity]]
 on_conflict = [inc: [quantity: ^attrs.quantity]]

Yea this works, but it creates a problem. I am using this feature in a shopping cart. CartItems have a has_many relationship with Order and I want the quantity update to happen only in CartItems sharing the same order id. That’s what i was trying to achieve here: on_conflict = from c in CartItems, where: c.order_id == ^attrs.order_id and c.user_id == ^attrs.user_id, update: [inc: [quantity: ^attrs.quantity]]. Doing this: on_conflict = [inc: [quantity: ^attrs.quantity]] updates the quantity irrespective of the order_id

Check to see exactly where this is failing - this error message is given whenever an UPDATE statement returns “0 rows affected”. If the insert didn’t happen, the database won’t set an id on cart_item.

Alright

@dumadi @al2o3cr I’m curious if and how this eventually is resolved? Also, is this expected behavior?

I have an on_conflict which may have “0 rows affected” when the update doesn’t occur. I guess this is also fine SQL behavior. Still Ecto will error out with a StaleEntryError.

For example: add a new entry or update an existing one but only when update_allowed is true.

Taken from GitHub - benvds/ecto_on_conflict_test :

test "sync_entry/2 does not update existing entries with same subject when update not allowed" do
      assert entry_one =
               Log.sync_entry(%{
                 note: "phoenix 1",
                 subject: "phoenix",
                 update_allowed: false
               })

      assert entry_one.note == "phoenix 1"

      # (Ecto.StaleEntryError) attempted to insert a stale struct
      assert entry_two =
               Log.sync_entry(%{
                 note: "phoenix 2",
                 subject: "phoenix",
                 update_allowed: false
               })

      # not updated
      assert entry_two.note == "phoenix 1"
    end

def sync_entry(attrs) do
    changeset = Entry.changeset(%Entry{}, attrs)

    on_conflict =
      Entry
      |> where([e], e.update_allowed == true)
      |> update(set: [note: ^get_field(changeset, :note)])

    Repo.insert(changeset,
      on_conflict: on_conflict,
      conflict_target: [:subject, :update_allowed]
    )

    Entry
    |> where(
      subject: ^get_field(changeset, :subject),
      update_allowed: ^get_field(changeset, :update_allowed, true)
    )
    |> Repo.one()
  end

The stale_error_field option for Repo.insert may be helpful; or you could always just rescue Ecto.StaleEntryError.

FWIW, that link has a trailing : that makes it not work

Thx! That fixed it.

Added it to the repo for future reference: