否定 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 返回的所有 Contacts not正确的理由.

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 查询:

to generate the following SQL query:

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

有没有办法做到这一点?

Is there any way of doing this?

推荐答案

要取消作用域，您可以使用:

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

使用 pluck，它会产生两个独立的查询:

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屋！