Update multiple columns wthout Enum.each Ecto Elixir

Question

I have a model as Entry. I am trying to update all entries in DB with 6 digit number sequence,

Entry
|> where(type: "entry:referral")
|> order_by(asc: :id)
|> Repo.all()

the above gives me all entries, and the column which I want to update is referral_number, its string type.

the very first entry will have "000001" and next ones 000002 .. "000010" and so on.

Is there any way to not use Enum.each to update the data, with an incremental numbers?

this is what I got:

Entry
|> where(type: "entry:referral")
|> order_by(asc: :id)
|> Repo.all()
|> Enum.with_index
|> Enum.each(fn {entry, index} ->
  referral_number =
    Integer.to_string(index)
    |> String.pad_leading(6, "0")

  Entry.changeset(entry, %{referral_number: referral_number})
  |> Repo.update()
end)

Answer 1

You probably want to use a window function to generate the row numbers and speedup the update of all rows.

Since you can't update in the same query of a window function, you need to use a subquery. I managed to do the process in 3 steps:

1. generate the rows with numbers based on order
2. add the left pad with PostgreSQL's lpad
3. update all the rows with the new values

Here is the code representing the steps:

import Ecto.Query
alias MyApp.Repo

ordered_rows_query =
  from e in Entry, select: %{
    id: e.id,
    row_number: row_number() |> over(order_by: [asc: e.id])
  }

rows_with_lpad_query =
  from e in Entry, join: o in subquery(ordered_rows_query), on: e.id == o.id, select: %{
    code: fragment("LPAD(?::text, 6, '0')", o.row_number),
    id: e.id
  }

update_query =
  from e in Entry, join: new_codes in subquery(rows_with_lpad_query),
    on: new_codes.id == e.id,
    update: [set: [code: new_codes.code]]

Repo.update_all(update_query, [])

There is a way to collapse the first two queries into one, but it's slightly difficult to read:

ordered_rows_with_lpad_query =
  from e in Entry, select: %{
    id: e.id,
    code: fragment("LPAD(?::text, 6, '0')", row_number() |> over(order_by: [asc: e.id]))
  }

update_query =
  from e in Entry, join: new_codes in subquery(ordered_rows_with_lpad_query),
    on: new_codes.id == e.id,
    update: [set: [code: new_codes.code]]

Repo.update_all(update_query, [])

See Ecto.Query.WindowAPI for details on how to use the window functions in Ecto.