How do I make an exclusive rather than inclusive query with named scopes in Rails?

Question

I've created an application that finds service providers based on multiple services passed to it. This is done via has many_through so there is a join object. Basically, if a user asks for service providers with Service A and Service B my scope returns service providers who offer either BOTH Service A and Service B or other service providers who provide either Service A or Service B. Instead of this we'd like to limit it to only providers who provide BOTH services.

My scope looks like this:

  named_scope :with_services, lambda { |services| {
      :conditions => ["services.id IN (#{services.map{|s| s.id}.join(', ')}) 
                        AND service_options.service_owner_type='ServiceProvider' 
                        AND service_options.service_owner_id = service_providers.id"],
      :include => [:services, :service_options]
    }
  }

As you can see I'm using the IN operator to do this. But IN is like saying "get me any provider who has service A or service B" when what I really want is "get me any service provider who has both service A and service B."

Is it possible to do this type of filtering in a single query or would I need to do a compound query or simply loop through the results and remove them from the list if they do not support all of the required services?

Answer 1

I think you can probably do it by providing the list of services for each of Service A and B.

So either amend the scope to take two lists:

:conditions => ["services.id IN (service_a) AND services.id IN (service_b)"] 

You might even actually be able to call the scope twice

 Model.with_services(service_a).with_services(service_b)

Also at no extra cost, you can shorthand your array manipulation to find the ids:

services.collect(&:id).join

&:id is the equivalent of a block using "|o| o.id".