Rails ActiveRecord - update_all: how to update multiple fields at once with mixed type of arguments

Question

I have a use case where I would like to use the ActiveRecord::Relation update_all method and specify several fields to set. I use update_all because a lot of entries can be updated and I don't want to load them all and update them one by one.

Some of them need a direct SQL SET statement, for instance because I set a column according to the value of another column.

Is there a simple syntax with update_all to make this readable, along the lines of this =>

MyModel.where(state: :foo).update_all([
  'timespent = timespent + 500',  # Can't be anything else than a SQL statement
  state: :bar,  # Regular Rails SQL interpolation
  updated_at: DateTime.current   
])

Note that the syntax above doesn't work because it will try to look for placeholders and replace values, hence the state: :bar and updated_at: DateTime.current are ignored.

A partial solution to this would be to convert everything to a single SQL statement string like below, but I don't like this too much because I need to figure out SQL statements, and it's a bit too complicated when coding with Rails ;) :

MyModel.where(state: :foo).update_all([
  "timespent = timespent + 500",  # Can't be anything else than a SQL statement
  "state = 'bar'",
  "updated_at = NOW()"   # Needed to translate from Rails to PGSQL
].join(', '))

Anybody with an elegant solution out there?

Thanks !

Answer 1

update_all takes a string, array, or hash (but you can't mix and match). In the case of an array it expects ActiveRecord Array Conditions. So you should be able to do something like this:

MyModel.where(state: :foo).update_all([
  'timespent = timespent + 500, state = ?, updated_at = ?',
  'bar',
  DateTime.current
])