Rails 3: How to get all Posts which ids are not in a given list?

Question

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

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

How would I get all posts with publisher_id not equals to 10, 16, or 17 (i.e. all possible ids besides those three) ?

Answer 1

in rails 4 we can do like below

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

it will generate SQL like below

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

Answer 2

Just perform a :

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

Answer 3

Untested, but should be like (using metawhere gem):

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

Answer 4

Using "pure" 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

Every single answer on this page is wrong because none of these answers take care of ALL array cases, Especially arrays that have only one element.

Here is an example that will 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 takes care of this very important case is @Tudor Constantin's. But the problem is he didn't actually show a 'way' of using his methodology to solve the real abstract example question the OP posted (not just using the hard-coded numbers).

here is my solution to dynamically find the ids not in an Activerecord association given an array of ids to exclude, 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!

Answer 6

Neat solution I've used:

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

• The presence of the 0 means it always has one element in (assuming nothing has an ID of 0)
• ID becoming a splat means it'll always be an array.

Answer 7

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