How to `update_all` based on a subquery in Rails

user_6193734 0
Viewed 745

I'm trying to achieve something pretty simple in PostgreSQL the Rails way.

Say you have a User model with 3 columns id, cached_segment, cached_step. Say you already have a complex query that calculates segment and query on the fly, encapsulated in a scope User.decorate_with_segment_and_step. This returns an ActiveRecord relation, same as User, but with 3 additional columns:

id  cached_segment cached_step segment    step    cache_invalid
1   NULL           NULL        segment_1  step_1  TRUE
2   segment_1      step_2      segment_1  step_2  FALSE
3   ...

The SQL I would like to generate is the following (PostgreSQL flavor):

UPDATE users
SET cached_segment = segment_1
    cached_step    = step
FROM (#{User.decorate_with_segment_and_step.to_sql}) decorated_users
WHERE decorated_users.cache_invalid AND decorated_users.id = users.id

Ideally, I would be able to do something like

User.
  from(User.decorate_with_segment_and_step, 'decorated_users').
  where(cache_invalid: true).
  update_all('cached_segment = segment, cached_step = step')

I had no luck with the statement above, update_all, according the source code, simply discards the from clause when building the update statement.

Note: I am aware I could just use User.connection.execute(my_raw_sql_here), which is what I'm doing right now. The goal is to stick with ActiveRecord.

bulkupdate rails-activerecord ruby-on-rails
1 Answers

Yes, AR has its limits, but this isn't one of them. Assuming you're using Postgres for everything -- development, testing and production -- your incantation to do this is:

ActiveRecord::Base.connection.execute("UPDATE users SET cached_segment = segment_1, cached_step = step FROM (#{User.decorate_with_segment_and_step.to_sql}) decorated_users WHERE decorated_users.cache_invalid AND decorated_users.id = users.id")

Hope it helps...

user_7834120
Related