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
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')
which runs two queries, returns an Array instead of a Relation, and is slower than I'd like.
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'))
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'))
This is not the same as using pluck (proposed in one of the comments):
Contact.where.not(id: Contact.group_search('query').pluck(:id))
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)
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'