如何在Ecto中使用同表聚合子查询结果执行update更新操作

1. 子查询原子更新（推荐，和原生SQL逻辑一致） #

你原来的思路是正确的，只要补充缺失的更新过滤条件、执行语句即可，Ecto完全支持在更新语句中使用子查询：

# 定义计算最小sort_order减1的子查询
lowest_order =
  from t in Thing,
    where: t.related_field_id == 123,
    select: min(t.sort_order) - 1

# 定义更新查询，指定更新id为2的记录
update_query =
  from t in Thing,
    where: t.id == 2,
    update: [set: [sort_order: subquery(lowest_order)]]

# 执行更新
Repo.update_all(update_query, [])

如果需要兼容低版本Ecto，也可以直接用fragment写完整的子查询逻辑：

from(t in Thing, where: t.id == 2)
|> Repo.update_all(set: [
  sort_order: fragment("(SELECT min(sort_order) - 1 FROM things WHERE related_field_id = ?)", 123)
])

性能优化建议 #

给things表添加(related_field_id, sort_order)联合索引，PostgreSQL可以直接通过索引拿到最小值，不需要全表扫描，查询效率为O(1)，性能足够支撑高并发场景：

CREATE INDEX idx_things_related_sort ON things (related_field_id, sort_order);

如果需要处理related_field_id = 123无匹配记录的场景，可以用coalesce设置默认值，避免sort_order被更新为null：

lowest_order =
  from t in Thing,
    where: t.related_field_id == 123,
    select: coalesce(min(t.sort_order) - 1, 0)

2. 分两次查询（仅建议低并发场景使用） #

如果业务场景并发量极低，也可以拆成两次查询实现，但是该方案非原子操作，高并发下存在竞态风险，可能导致sort_order计算错误：

# 第一步查询最小sort_order
min_sort = Repo.one(from t in Thing, where: t.related_field_id == 123, select: min(t.sort_order))
new_sort = if is_nil(min_sort), do: 0, else: min_sort - 1

# 第二步执行更新
Repo.update_all(from(t in Thing, where: t.id == 2), set: [sort_order: new_sort])

内容的提问来源于stack exchange，提问作者harryg