Rails: select model

Question

I have a model Shop:

class Shop < ActiveRecord::Base
  has_and_belongs_to_many :services
end

and a model Service:

class Service < ActiveRecord::Base
  has_and_belongs_to_many :shops
end

I would like to query every shop that provides all of the following services:

Reparation
Advise
Shipping

So return all shops where services contain reparation AND advise AND shipping.

Is this possible with ActiveRecord querying only?

Thanks!

This would require a SQL query hard-coded, not only ActiveRecord. Because `Shop.includes(:services).where(services: { name: ['reparation', 'advise', 'shipping'] })` would return shop having at least one of the 3 services (OR condition and not AND as you asked). — MrYoshiji, Jun 18 '15 at 14:33

score 2 · Accepted Answer · edited May 23 '17 at 12:22

See MySQL: Select records where joined table matches ALL values for how to do it in sql.

For a simpler method, which makes a series of simple queries instead of a single complex query, you could do this:

#starting with the services in an array called @services
#(which could come from params[:service_ids] for example)
#find shops which have ALL these services.
shop_ids = @services.map(&:shop_ids).inject{|a,b| a & b}
@shops = Shop.find(shop_ids)

Key to this is .inject{|a,b| a & b} : inject is an array method which performs a function between the first and second elements (a and b) then uses the result of that with the block again, with the third element etc, working it's way through the array. The & operator is array intersect, so you will end up only with the shop_ids which are returned for ALL services.

Rails: select model

1 Answers1