Is there an idiomatic way to write UPDATE FROM in Ecto?

Questions / Help
1

Hello all!

Is there an idiomatic way to write UPDATE FROM in Ecto? For some context on UPDATE FROM, see for example this StackOverflow answer.

I realized that I’ve been reusing the following snippet in a few places in my codebase:

ids = [1, 2, ...]
fields = ["foo", "bar", ...]
# field on 1 will be set to "foo", field on 2 will be set to "bar", and so on...
Repo.update_all(
  from(a in Article,
    join:
      u in fragment(
        """
        SELECT
        unnest(?::uuid[]) AS id,
        unnest(?::text[]) AS field
        """,
        type(^ids, {:array, Ecto.UUID}),
        type(^fields, {:array, :string})
      ),
    on: a.id == u.id,
    update: [set: [field: u.field]]
  ),
  []
)

I’m not unhappy with it, but it was a little bit tricky to figure out. And ideally I wouldn’t have to use a fragment or manually write out the types of the fields.

I figured I’d ask here in case anyone knew of a more idiomatic solution! Thanks!

1 Like