CODEWITHSUNDEEP
elixirphoenix-frameworkecto
user2694306
user2694306

Reputation: 4050

Elixir Ecto - How to Upsert/Increment

I have a model that I would like to execute in Phoenix/Elixir. The model basically computes a rolling sum of the amount spent by a user. The model (Receipts) looks like the following:

---------------
| ID | Amount |
---------------
| 1  |   0    |
| ...|  ...   |
| 5  |   4    |
---------------

There is a unique index on ID. I want to insert a user into the table (I define the ID) and then set the amount if the user does not exists, otherwise add the new amount to the existing amount. For example, executing:

Repo.insert(Users.changeset(%Users{}, %{ID: 1, Amount: 10})

Would result in:

---------------
| ID | Amount |
---------------
| 1  |   11   |
| ...|  ...   |
| 5  |   4    |
---------------

and executing Repo.insert(%Users{}, Users.changeset(%{ID: 6, Amount: 5}) would result in:

---------------
| ID | Amount |
---------------
| 1  |   11   |
| ...|  ...   |
| 5  |   4    |
| 6  |   5    |
---------------

I know that I should be doing something with :on_conflict but I'm a bit lost how to get it right. Can anyone point me in the right way?

Upvotes: 4

Views: 4474

Answers (3)

shulamit chatamov
shulamit chatamov

Reputation: 65

  Adding to the first answer return the variable after the update will prevent parallel problems.

iex> Repo.insert(%User{id: 1, amount: amount_to_add},  returning: [:amount], conflict_target: :id, on_conflict: [inc: [amount: amount_to_add]])

Upvotes: 1

NoDisplayName
NoDisplayName

Reputation: 15736

Repo.transaction fn ->
  user =
    User
    |> where(id: ^id)
    |> lock("FOR UPDATE")
    |> Repo.one()

  if user do
    user
    |> change(amount: user.amount + 5)
    |> Repo.update!()
  else
    %User{}
    |> change(amount: 5)
    |> Repo.insert!()
  end
end

That's one way to go which uses postgreSQL syntax for locking. I guess you will figure out the rest.

Upvotes: 3

stoodfarback
stoodfarback

Reputation: 1349

Sure, that's possible. It would look something like this:

iex> amount_to_add = 10
10
iex> Repo.get(User, 1)
nil
iex> Repo.insert(%User{id: 1, amount: amount_to_add}, conflict_target: :id, on_conflict: [inc: [amount: amount_to_add]])
...
iex> Repo.get(User, 1)
%User{id: 1, amount: 10, ...}
iex> Repo.insert(%User{id: 1, amount: amount_to_add}, conflict_target: :id, on_conflict: [inc: [amount: amount_to_add]])
...
iex> Repo.get(User, 1)
%User{id: 1, amount: 20, ...}

Upvotes: 13

Related Questions