ruby-on-rails Rails 3:如何获取所有ID不在给定列表中的帖子?

osh3o9ms  于 2023-05-30  发布在  Ruby
关注(0)|答案(7)|浏览(98)

要获取publisher_id等于10、16或17的所有帖子,我这样做:

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

我如何得到所有的帖子与publisher_id不等于到10,16,或17(即除了这三个之外的所有可能的ID)?

r1wp621o

r1wp621o1#

只需执行以下操作:

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

qgelzfjb2#

在rails 4中,我们可以像下面这样做

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

它将生成如下SQL

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

eit6fx6z3#

未经测试,但应该像(使用metahere gem):

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

vm0i2vca4#

使用“纯”ActiveRecord语法,并在Rails 3中使用Arel,您可以执行以下操作:

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

vcirk6k65#

我用过的最好的解决方案:

ids = #however you get the IDS
Post.where(["id not in (?)", [0,*ids])
  • 0的存在意味着它总是有一个元素(假设没有任何东西的ID为)
  • ID变成splat意味着它永远是一个数组。
2ekbmq32

2ekbmq326#

这一页上的每一个答案都是错误的,因为这些答案都没有考虑到所有数组的情况,特别是只有一个元素的数组
下面是一个使用本页上任何“所谓”解决方案都将失败的示例:

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

在这个页面上唯一的答案,实际上是在正确的轨道上,并照顾这个非常重要的情况是@都铎康斯坦丁的。但问题是,他实际上并没有展示一种“方法”,用他的方法来解决OP发布的真实的抽象的示例问题(而不仅仅是使用硬编码的数字)。
下面是我的解决方案,动态查找不在Activerecord关联中的id,给定一个要排除的id数组,它将适用于n个元素的数组(...包括n=1和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!
vmpqdwk3

vmpqdwk37#

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

相关问题