5

I would like to do an update ... from, or an update using a CTE, but using ActiveRecord. Ideally so I can take advantage of in_batches.

For example, Person has many Things and I'd like to cache their first Thing. I'd imagine it would work something like this.

Person
  .in_batches
  .from(
    Thing.select(
      :id, 
      :people_id, 
      "rank() over (partition by people_id order by created_at asc) as thing_rank"
    )
   )
   .where(
     "things.thing_rank": 1,
     "people.id": :people_id
   )
  .update_all(cached_thing_id: "things.id")

But I can't figure any combination that works.

Alternatively, is there a way to use in_batches while manually writing out the SQL? And I'm not attached to the subquery method, a CTE or temporary view is fine.

Schwern
  • 153,029
  • 25
  • 195
  • 336
  • 4
    Apparently `Arel::UpdateManager` does not have a `from`. Its `source` is `left` (like a FROM in the `SelectManager`) but this value is only used for the "UPDATE" statement. Due to this it appears this is one of the very few times I would recommend writing a SQL String because the only other way I can see assembling this is using a correlated subquery which is certainly not the most performant option – engineersmnky Sep 13 '21 at 18:51
  • 1
    @engineersmnky Thanks. I went with writing out the SQL and adding a `where people.id = any($1)` and supplied the ids in each batch. – Schwern Sep 13 '21 at 19:10
  • 2
    Yeah apparently this has been a request for quite some time https://github.com/rails/rails/issues/13496, https://github.com/rails/arel/pull/294 – engineersmnky Sep 13 '21 at 19:27

0 Answers0