Rails ActiveRecord - update_all: how to update multiple fields at once with a mixed argument type

Question

I have a use case where I would like to use the ActiveRecord :: Relation update_all method and specify several fields for installation. I use update_all because many records can be updated, and I do not want to download them all and update them one at a time.

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

Is there a simple syntax with update_all to make it readable, line by line 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 does not work, because it will try to find placeholders and replace values, so the state: :bar and updated_at: DateTime.current tags are ignored.

A common solution for this would be to convert just a single line of the SQL statement, as shown below, but I don’t really like it because I need to figure out SQL queries, and this is 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(', ')) 

Anyone with an elegant solution?

Thanks!

Answer 1

update_all accepts a string, array or hash (but you cannot mix and match). In the case of an array, it expects the conditions of the ActiveRecord array. 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 ])