Using Postgres UPDATE … FROM in Ecto without raw SQL

Question

Based on an Elixir thread from last year, I was able to write a raw SQL query to bulk update records with the values from an unrelated table. However, I would like to be able to generate this query using Ecto.

In the example below, assume there are two tables, cats and dogs, and the cats table has a foreign key (dog_id). I want to link a dog to a cat.

The code below is how I'm doing this manually with Elixir and raw SQL:

cat_ids = [1,2,3] # pretend these are uuids
dog_ids = [4,5,6] # ... uuids

values =
  cat_ids
  |> Enum.zip(dog_ids)
  |> Enum.map(fn {cat_id, dog_id} ->
    "('#{cat_id}'::uuid, '#{dog_id}'::uuid)"
  end)
  |> Enum.join(", ")

sql = """
UPDATE cats as a
SET dog_id = c.dog_id
from (values #{values}) as c(cat_id, dog_id)
where c.cat_id = a.id;
"""

Repo.query(sql)

Is there a way to move this to Repo.update_all or some use of fragments so I'm not manually building the query?

Gabriel Rufino Gabriel Rufino · Accepted Answer · 2018-08-08T23:00:58

Sure, you can use Ecto syntax, but it ain't much different in my opinion, tho you have to use a Schema, for example in my application I have an user authentication and that's how we update the token:

def update_token(user_id, token) do
  Repo.transaction(fn ->
            from(t in UserAuthentication, where: t.user_id == ^to_string(user_id))
            |> Repo.update_all(set: [token: token])
end

and the UserAuthentication schema looks more or less like:

defmodule MyApp.UserAuthentication do
  use Ecto.Schema
  import Ecto.Changeset

  schema "user_authentication" do
    field(:user_id, :integer)
    field(:token, :string)
    timestamps()
  end

  def changeset(%__MODULE__{} = user, attrs) do
    user
    |> cast(attrs, [:user_id, :token])
    |> validate_required([:user_id, :token])
  end
end

This is good for data validation and works in any DB you attach.

Using Postgres UPDATE … FROM in Ecto without raw SQL

1 Answers