Query on two HABTM models (Rails3)

Question

I have two models, Clients and Items with a HABTM association. I would like to find the fastest way to select all Clients, which have several (more than one) of the items (or a certain number of items).

class Client < ActiveRecord::Base
  has_and_belongs_to_many :items
end

class Item < ActiveRecord::Base
  has_and_belongs_to_many :clients
end

The query below fetches the clients, which have any ONE item from the list:

required_item_ids = Item.where(:kind => :book).collect(&:id)
Client.join(:items).where(:items => {:id => required_item_ids})

But needed is the list of clients having SEVERAL or ALL of the required items.

I believe this is a simple question as I am a newbie to Rails, but I looked through all similar questions here and in some other places and did not find the answer.

I don't think you can do that directly with AR. You're probably gonna have to write up your own SQL (for each Client, select all Items, then check if required_items are INCLUDED in the selected items; if so, return the client) — alste, Jul 19 '11 at 08:58

score 1 · Accepted Answer · answered Jul 19 '11 at 10:14

If this is a one-off job, then you could get this list via looping through all the clients in Ruby. Something like:

clients_with_more_than_one_book_items = []
book_items = Item.where(:kind => :book)

Client.find_each do |client|
  books_count = 0
  client.items.each do |item|
     if book_items.include?(item)
        books_count += 1
     end
  end

  clients_with_more_than_one_book_items << client if books_count > 1
end

However, that may take a while to run if you have lots of clients and items, so if this is a query that you're running often, it's probably a good idea to add a books_count attribute to the Client model, and to add code (within callbacks on the model) that keep this updated as a kind of counter cache.

Thank you, Frankie. Thought of this too, but this is an expensive query and I was looking for a faster solution. Using a counter cache seems okay, if there is no other way. — Alexei Danchenkov, Jul 19 '11 at 10:44

Query on two HABTM models (Rails3)

1 Answers1