Geek Answers Handbook

Cancel ActiveRecord request scope

I have a slightly complicated scope of the 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 this list, I need to download

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

which launches two queries returns Array instead of Relation and is slower than I would like.

And since I'm testing the group_search , writing another scope that is negative can ruin the gist. I would 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 to do this?

+7
source share
2 answers

To undo a scope that you can use:

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

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

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

Without pluck it makes one request (with two choices):

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

Using pluck it makes two independent requests:

 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 you request a lot of records, the first one is more efficient. Of course, Contact.where.not(group_search: 'query') more efficient since it produces one query with one select (but in some cases this may not be possible):

 SELECT 'contacts'.'id' FROM 'contacts' WHERE 'contacts'.'group_search' != 'query'
+6
source

I think what you are looking for is called domain negation, you can use where_values (or where_values_hash in Rails> = 5):

 conditions = Contact.group_search('query').where_values @contacts = Contact.where.not(conditions.reduce(:and))

For this solution to work in Rails 4.x, you must provide values ​​in the scope as arrays:

 scope :groups, -> { where(contact_type: [2308]) }

I also found a neat general implementation for negating areas , you might also find it interesting.

+8
source

More articles:


All Articles