backfill-script.ex

defmodule Mix.Tasks.Tmp.BackfillGender do
  use Mix.Task
  import Ecto.Query

  @shortdoc "Backfill gender with title data in users table"
  def run(_) do
    sql_query = ~S"""
    UPDATE users SET
      gender = mapping.gender
    FROM (VALUES
      ('man', 'Mr'),
      ('woman', 'Ms')
    ) as mapping(gender, title)
    WHERE
      users.title = mapping.title AND users.gender IS NULL AND users.id = ANY($1);
    """

    query =
      from(u in WttjApi.Accounts.User,
        where: is_nil(u.gender) and not is_nil(u.title),
        select: u.id
      )

    stream = WttjApi.Repo.stream(query, max_rows: 1000)

    {:ok, chunks} =
      WttjApi.Repo.transaction(fn ->
        Stream.chunk_every(
          stream,
          1000
        )
        |> Enum.to_list()
      end)

    total_chunks = chunks |> Enum.count()

    chunks
    |> Enum.with_index(1)
    |> Enum.each(fn {chunk, index} ->
      %Postgrex.Result{} = Ecto.Adapters.SQL.query!(WttjApi.Repo, sql_query, [chunk])

      IO.puts("Updated #{index}/#{total_chunks}")
      Process.sleep(10)
    end)
  end
end