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)"

Dorsey

Hmm. Bad upvote on the comment where(:hidden => false) by someone. That code will not generate the type of SQL the OP is looking for. – Pau

Answer 1

With a different syntax, yes. Example:

posts = Post.scoped.table # or Arel::Table.new("posts")
posts.where(posts[:hidden].eq(true).not).to_sql
# => SELECT  FROM `posts` WHERE NOT ((`posts`.`hidden` = 1))

Pau

Not precisely what I was looking for but still a very good answer. – Dorsey

Did you find another way to do this? Perhaps a better syntax? Did the answer work for you? – Pau

Actually I think it's not even possible since what I am actually asking is to invert and ActiveRecord::Relation object which might or might not have multiple joins and includes which would complicate the matter too much (which WHEREs should we invert and which not?). I think I'll leave this open until an answer comes along. – Dorsey

Answer 2

In rails 4 there is not suffix for this purpose:

Post.where.not(hidden: true).to_sql
# => SELECT FROM `posts` WHERE `posts`.`hidden` != 1

In rails 3 you can use squeel gem. It gives many usefull features. And with it you can write:

Post.where{ hidden != true }.to_sql
# => SELECT FROM `posts` WHERE `posts`.`hidden` != 1

Numbers

Answer 3

We can take Zabba's answer further by passing the inverted query back into ActiveRecord:

table = Post.arel_table
query = table[:hidden].eq(true).not # the inverted query, still ARel
Post.where(query) # plug it back into ActiveRecord

This will return ActiveRecord objects, as you would normally expect.

Commonality

Answer 4

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.

Sixty

He, I just googled a solution for a inverse where and you answered this solution yesterday. Another reason to move to rails 7 :) – Valdes

Answer 5

What I do when I'm looking for records with a "not true" condition (eg, false or nil) is:

Post.where(["(hidden IS NULL) OR (hidden = ?)", false])

Bova

Answer 6

Finally we have the method invert_where with Rails 7.

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

Please check this commit reference for more details.

Poop