否定ActiveRecord查询范围 [英] Negate ActiveRecord query scope

问题描述

我在模型上的作用域有点复杂

I have a slightly complicated scope on a model

class Contact < ActiveRecord::Base
  scope :active,       -> { where(inactive: false) }
  scope :groups,       -> { where(contact_type: 2308) }
  scope :group_search, -> (query) do
    active.groups.where("last_name LIKE '%' + ? + '%'", query)
  end
end

出于测试目的，我要确保所有联系人 出于正确的原因，排除了 group_search 返回的不是。

For testing purposes, I want to make sure that all Contacts not returned by group_search are excluded for the right reasons.

但是要得到该列表，我必须加载

But to get that list, I have to load

Contact.all - Contact.group_search('query')

运行两个查询，返回 Array 而不是 Relation ，并且比我想要的慢。

which runs two queries, returns an Array instead of a Relation, and is slower than I'd like.

由于我正在测试 group_search 范围，如果编写另一个范围是负面的，则会破坏观点。我宁愿做这样的事情：

And since I'm testing the group_search scope, writing another scope that is its negative would kind of spoil the point. I'd rather just do something like:

Contact.merge.not(Contact.group_search('query'))

生成以下SQL查询：

SELECT * 
FROM contacts 
WHERE NOT (contact_type = 2308 AND inactive = 0 AND last_name LIKE '%' + ? + '%')

有什么办法吗？

推荐答案

要取消作用域，可以使用：

To negate an scope you can use:

Contact.where.not(id: Contact.group_search('query'))

这与使用 pluck （在评论之一中提出）：

This is not the same as using pluck (proposed in one of the comments):

Contact.where.not(id: Contact.group_search('query').pluck(:id)) 

没有 pluck ，它会产生一个查询（有两个选择）：

Without the pluck, it produces one query (with two selects):

SELECT  `contacts`.* FROM `contacts` WHERE `contacts`.`id` NOT IN (SELECT `contacts`.`id` FROM `contacts` WHERE `contacts`.`group_search` = 'query')

使用按下，它会产生两个独立的查询：

With the pluck, it produces two independent queries:

SELECT `contacts`.`id` FROM `contacts` WHERE `contacts`.`group_search` = 'query'
SELECT  `contacts`.* FROM `contacts` WHERE `contacts`.`id` NOT IN (1, 324, 325, 326, 327, 328, 329, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 340, 341, 342, 343, 344, 345, 346, 347, 348, 349, 350, 351, 352, 353, 354, 355, 356, 357, 358, 359, 360, 361)

查询很多记录时，第一个是效率更高的方法。当然， Contact.where.not（group_search：'query'）效率更高，因为它可以一次选择一个查询（但是在某些情况下可能无法实现）：

When querying many records, the first one is way more efficient. Of course Contact.where.not(group_search: 'query') is more efficient as it produces one query with one select (but this may be not possible in some cases):

SELECT `contacts`.`id` FROM `contacts` WHERE `contacts`.`group_search` != 'query'

这篇关于否定ActiveRecord查询范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！