How to `update_all` based on a subquery in Rails

Question

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.

ActiveRecord has its limits, which is good because doing complex SQL stuff using another layer of complexity (ActiveRecord or Arel tables) is not helping... — MrYoshiji, Sep 06 '18 at 16:04
Have you tried `User.decorate_with_segment_and_step.where(cache_invalid: true).update_all('cached_segment = segment, cached_step = step')`? — MrYoshiji, Sep 06 '18 at 16:05
Yes, sadly it seems only actual DB columns can be used in the update clause — Matthieu Libeer, Sep 06 '18 at 16:23

score 0 · Answer 1 · answered Sep 20 '18 at 23:09

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...

How to `update_all` based on a subquery in Rails

1 Answers1