Rails 3: How to get all messages whose identifiers are not specified in this list?

Question

To get all posts with publisher_id equal to 10, 16 or 17, I do:

 Post.where(:publisher_id => [10, 16, 17]) 

How can I get all posts with publisher_id not equal to 10, 16 or 17 (i.e. all possible identifiers besides these three)?

Answer 1

Just do a:

 Post.where(["publisher_id NOT IN (?)", [10, 16, 17]]) 

Answer 2

in rails 4 we can do as below

 Post.where.not(:publisher_id => [10, 16, 17]) 

it will generate SQL as shown below

 SELECT "posts".* FROM "posts" WHERE ("posts"."publisher_id" NOT IN (10, 16, 17)) 

Answer 3

Unconfirmed, but should look like (using a metawein gem):

 Post.where( :id.not_eq => [10,16,17] ) 

Answer 4

Using the "clean" ActiveRecord syntax sprinkled with Arel using Rails 3, you can do something like this:

 Post.where( Post.arel_table[:publisher_id].not_in([10, 16, 17]) ) 

Answer 5

Each answer on this page is incorrect because none of these answers care about ALL array cases, especially arrays that have only one element .

Here is an example that will be FAIL using any of the so-called solutions on this page:

 @ids = [1] Post.where("publisher_id NOT IN (?)", @ids) #ERROR Post.where("publisher_id NOT IN (?)", [4]) #ERROR #...etc #ALSO @ids = [] Post.where("publisher_id NOT IN (?)", @ids) #ERROR Post.where("publisher_id NOT IN (?)", []) #ERROR #...etc #The problem here is that when the array only has one item, only that element is #returned, NOT an array, like we had specified #Part of the sql that is generated looks like: #...WHERE (publisher_id NOT IN 166) #It should be: #...WHERE (publisher_id NOT IN (166)) 

The only answer on this page that is actually on the right track and will take care of this very important case is @Tudor Constantin's. But the problem is that he did not actually show the “way” to use his methodology to solve the real abstract sample question OP (not only using hard-coded numbers).

here is my solution for dynamically searching for identifiers not included in the Activerecord association given by an array of identifiers for exclusion that will work with an array of n elements (... including n = 1 and n = 0)

 @ids = [166] @attribute = "publisher_id" @predicate = "NOT IN" @ids = "(" + @ids.join(",") + ")" if @ids == "()" #Empty array, just set @ids, @attribute, and @predicate to nil @ids = @attribute = @predicate = nil end #Finally, make the query Post.where( [@attribute, @predicate, @ids].join(" ") ) #Part of the sql that is generated looks like: #...WHERE (publisher_id NOT IN (166)) #CORRECT! #If we had set @ids = [] (empty array) #Then the if statement sets everything to nil, and then #rails removes the blank " " space in the where clause automatically and does #the query as if all records should be returned, which #logically makes sense! 

If this helped you, please vote! If you are embarrassed or do not understand one of my comments, let me know.

Answer 6

A convenient solution that I used:

 ids = #however you get the IDS Post.where(["id not in (?)", [0,*ids]) 

• The presence of 0 means that it always has one element in (if nothing has identifier 0)
• An identifier that becomes a splat symbol means that it will always be an array.

Answer 7

 Post.where(" id NOT IN ( 10, 16, 17) ")