How to toggle a boolean field for multiple records generating just one SQL query-CodePudding

I'm trying to write a migration to update a boolean field where the field will mean the exact opposite of what it means currently. Therefore I need to toggle every record to update the records where this field is true to false and vice-versa.

Example:

class ChangePostsVisibleToArchived < ActiveRecord::Migration[6.1]
  def change
    rename_column :posts, :visible, :archived

    # Toggle all tracked topics
    # What I DON'T want to do:
    Post.all.each { |post| post.toggle! :archived }
  end
end

The way I described above will generate one SQL command per Post record. Is there a way I can toggle all records within a single SQL command using rails ActiveRecord syntax?

CodePudding user response：

Post.update_all "archived = NOT archived"

CodePudding user response：

Because you have to read each record's current value in order to switch it, you are going to have to do more than one query.

It could be done in two queries:

active = Post.where(archived: true)
archived = Post.where(archived: false)

active.update_column(archived: false)
archived.update_column(archived: true)

But, there's nothing wrong with this:

ActiveRecord::Base.connection.execute('UPDATE posts SET archived = NOT archived')

Rails enables direct SQL for just this reason.