Reversed has_many in Rails

Question

Let's say I have models: User and Item and relation many-to-many between them. How to get Users who have exactly(no more) Items with the defined attributes i.e. Users who have Items with colors = ['red', 'black'].

Of course I can do something like this:

---

User.all :joins => [:items, :items], :conditions => {:"items.color" => "red", :"items_users.color" => 'black'}

---

But for more attributes it's going to be quite cumbersome. I can do also:

---

User.all(:conditions => ["items.color in (?), ['red', 'black']], :include => :items)

---

But this one returns also users with items having colors = ['red', 'black', 'blue', 'etc']

So the only solution is to get all and to sort with ruby syntax ? How to do it in one SQL query or Rails AR syntax ?

Answer 1

The way which optimizes programmer time and readability, in my opinion:

#get all users who have items which are both red and black but no other colors
candidate_users = User.all(:include => :items)
candidate_users.reject! do |candidate| 
  candidate.items.map {|item| item.color}.sort != ['black', 'red']
end

If you are expecting to be looping through a metric truckload of users there, then you'll need to SQL it up. Warning: SQL is not my bag, baby: test before use.

select users.*, items.* FROM users 
  INNER JOIN items_users ON (items_users.user_id = users.id) 
  INNER JOIN items ON (items_users.item_id = items.id) 
    GROUP BY users.id HAVING COUNT(DISTINCT items.color) = 2

What I think that evil mess does:

1) Grabs every user/item combination 2) Winnows down to users who have items of exactly 2 distinct colors

Which means you'll need to:

candidate_users.reject! do |candidate| 
  candidate.items.map {|item| item.color}.sort != ['black', 'red']
end

You can probably eliminate the need for the ruby here totally but the SQL is going to get seven flavors of ugly. (Cross joins, oh my...)