Is there a way to invert an ActiveRecord::Relation query?

Question

Let's say we have the following:

irb> Post.where(:hidden => true).to_sql
=> "SELECT `posts`.* FROM `posts` WHERE posts.hidden = 1"

Could we somehow get an inverted SQL query out of it?

What I am looking for, should probably look like this:

irb> Post.where(:hidden => true).invert.to_sql
=> "SELECT `posts`.* FROM `posts` WHERE NOT (posts.hidden = 1)"

Answer 1

invert_where (Rails 7+)

Starting from Rails 7, there is a new invert_where method.

According to the docs, it:

Allows you to invert an entire where clause instead of manually applying conditions.

class User
  scope :active, -> { where(accepted: true, locked: false) }
end

User.where(accepted: true)
# WHERE `accepted` = 1

User.where(accepted: true).invert_where
# WHERE `accepted` != 1

User.active
# WHERE `accepted` = 1 AND `locked` = 0

User.active.invert_where
# WHERE NOT (`accepted` = 1 AND `locked` = 0)

Be careful because this inverts all conditions before invert_where call.

class User
  scope :active, -> { where(accepted: true, locked: false) }
  scope :inactive, -> { active.invert_where } # Do not attempt it
end

# It also inverts `where(role: 'admin')` unexpectedly.
User.where(role: 'admin').inactive
# WHERE NOT (`role` = 'admin' AND `accepted` = 1 AND `locked` = 0)

Sources:

• invert_where from official Rails API docs.
• Link to the PR.
• BigBinary blog article about invert_where method.