Ecto update query using value from a subquery

user_1606323 0
Viewed 157

I want to update a field based on the value of an aggregate subquery on the same table. In plain SQL (in my case, PostgreSQL) it could look like this:

UPDATE
    things
SET
    sort_order = (
        SELECT
            min(sort_order) - 1
        FROM
            things
        WHERE
            related_field_id = 123)
WHERE
    things.id = 2

So basically I want to set the sort_order of a record in things to 1 less than the current lowest sort_order of a subset of records in the same table.

I've tried somthing like

lowest_order =
  from(t in Thing,
    where: t.related_field_id == 123,
    select: fragment("MIN(?) - 1", t.sort_order)
  )

from(t in Thing,
  update: [set: [sort_order: subquery(lowest_order)]]
)

But ecto doesn't accept this as a valid query expression. Is there a way to achieve the above? Perhaps this approach is sub-optimal so I'm happy to hear alternatives.

ecto subquery sql
1 Answers

I know this is an old question, so you might not need this answer anymore, but hopefully this helps someone.

lowest_order =
  from(t in Thing,
    where: t.related_field_id == 123,
    select: %{lowest_order: fragment("MIN(?) - 1", t.sort_order)}
  )

from(t in Thing,
  join: sub in subquery(lowest_order),
  where: t.id == 2,
  update: [set: [sort_order: sub.lowest_order]]
)
user_6027160
Related