Inverted has_many in Rails

Question

Let's say I have models: User and Element and the relationship between many. How to get users who have exactly (no more) Elements with certain attributes, that is, users who have elements 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, this will be rather cumbersome. I can also:

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

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

So, the only solution is to get everything and sort with ruby ​​syntax? How to do this in a single SQL query or Rails AR syntax?

Answer 1

A way to optimize the time and readability of a programmer, 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 expect to go through metric user loading there, you will need a SQL server. Warning: SQL is not my bag, child: 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 is an evil mess:

1) captures every combination of users / items 2) Winnows down for users who have items in exactly 2 different colors

This means that you need to:

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

You can probably completely eliminate the need for ruby, but SQL will get seven ugly flavors. (The cross unites, oh my ...)