如何在ActiveRecord中实现update ... from或CTE更新并支持in_batches

原有代码错误原因 #

• 调用from方法后，Person查询的主表被替换为Thing的子查询，update_all默认会更新主表（也就是Thing表），完全不符合更新Person表的需求
• 用哈希形式给update_all传值时，右侧的"things.id"会被当做字符串字面量转义，不会识别为跨表字段引用，最终缓存的会是things.id字符串而非实际的关联id
• 条件中的"people.id": :people_id写法不被ActiveRecord支持，:people_id会被转义为字符串值，无法实现两表字段关联的效果
• 额外注意：窗口函数生成的别名不能直接用在WHERE子句中，需要放在JOIN条件里或者嵌套一层子查询过滤

方案1：UPDATE ... FROM 语法实现（适配PostgreSQL） #

这个方案不需要手写全量SQL，尽量保留ActiveRecord的链式调用能力，同时原生支持in_batches拆分批次，避免大数据量下锁表：

# 先构造带排序的Thing子查询
ranked_things = Thing.select(
  :id, 
  :people_id,
  # 用ROW_NUMBER代替RANK，避免同创建时间返回多条的问题
  "ROW_NUMBER() OVER (PARTITION BY people_id ORDER BY created_at ASC) AS thing_rank"
)

# 按批次更新，默认每批1000条，可通过of参数调整批次大小
Person.in_batches(of: 1000) do |person_batch|
  person_batch
    .joins("INNER JOIN (#{ranked_things.to_sql}) ranked_things ON people.id = ranked_things.people_id AND ranked_things.thing_rank = 1")
    .update_all("cached_thing_id = ranked_things.id")
end

方案2：CTE 实现（适配支持CTE更新的数据库） #

如果偏好CTE的写法，完全可以手写SQL的同时利用in_batches的批次拆分能力，只需要把当前批次的ID范围注入到SQL里即可：

Person.in_batches(of: 1000) do |person_batch|
  update_sql = <<~SQL
    WITH ranked_things AS (
      SELECT 
        id, 
        people_id,
        ROW_NUMBER() OVER (PARTITION BY people_id ORDER BY created_at ASC) AS thing_rank
      FROM things
    )
    UPDATE people
    SET cached_thing_id = ranked_things.id
    FROM ranked_things
    WHERE 
      ranked_things.thing_rank = 1
      AND people.id = ranked_things.people_id
      -- 限定只更新当前批次的Person，避免全表操作
      AND people.id IN (#{person_batch.select(:id).to_sql})
  SQL
  ActiveRecord::Base.connection.execute(update_sql)
end

注意事项 #

• 如果你使用MySQL，不支持UPDATE ... FROM语法，需要改成多表JOIN更新的写法即可，批量逻辑完全通用
• 如果数据量特别大，可以给things.people_id和things.created_at加联合索引，大幅提升子查询的执行速度
• 如果你不需要批量处理的能力，全量更新直接去掉in_batches的包裹即可，语法完全兼容

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