Rails3 Active Admin - How to filter only records that meet all checked items in collection

Question

I have a one-to-many associaton of user to network model. The relevant parts of my schema information is as follows.

# == Schema Information      
# Table name: users
#
#  id                     :integer         not null, primary key
#  name                   :string(255)

#
# Table name: networks
#
#  id         :integer         not null, primary key
#  user_id    :integer
#  uid        :string(255)
#  name   :string(255)
#  pub_url    :string(255)

I want to filter out users who have specific multiple networks using the name property of the network model. For example if I check facebook and foursquare as filters, I want to get back users who have connected both facebook and foursquare. My current implementation is to pass an array containing the names of networks a user can add as displayed below.

filter :networks_name, :as => :check_boxes,
:collection => %w(facebook twitter foursquare linkedin)

This however uses an OR condition for the filters which retrieves users with any of the checked items. What I actually want to get is users who meet all the checked items. What's the right way of achieving this?

Answer 1

ActiveAdmin uses meta_search for its filters (https://github.com/ernie/meta_search):

filter :networks_name_all,
  :as => :check_boxes,
  :collection => %w(facebook twitter foursquare linkedin)

And in your user model:

scope :networks_names_all_in, -> names {
  names.reduce(scoped) do |scope, name|
    subquery = User.select('`users`.`id`').
      joins(:networks).
      where('networks.name = ?', name)
    scope.where("`users`.`id` IN (#{subquery.to_sql})")
  end
}
search_methods :networks_name_all_in

This will create a nice single SQL query for you, with proper joins and where conditions.

For example:

User.networks_name_all_in(["facebook", "twitter"])

will generate the following SQL request:

SELECT *
FROM `users`
WHERE `users`.`id` IN (SELECT `users`.`id`
                       FROM `users`
                       INNER JOIN `networks`
                       ON `networks`.`user_id` = `users`.`id`
                       WHERE `networks`.`name` = 'facebook')
  AND `users`.`id` IN (SELECT `users`.`id`
                       FROM `users`
                       INNER JOIN `networks`
                       ON `networks`.`user_id` = `users`.`id`
                       WHERE `networks`.`name` = 'twitter')

Update: Added functional scope.

Update 2: Make scope resilient to scope merging problems.