Chained has_many through: users -> roles -> lists. Finding lists that a user is assigned to (via roles).

Question

It must be a very common problem. I have a chained many-to-many relationship like this:

User n<==>n Role n<==>n List

ActiveRecord models:

class User
  # linking to roles
  has_many :role_assignments
  has_many :roles, :through => :role_assignments
end

class Role
  # linking back to users
  has_many :role_assignments
  has_many :users, :through => :role_assignments

  # linking to lists
  has_many :list_assignments
  has_many :lists, :through => :list_assignments
end

class List
  # linking back to roles
  has_many :list_assignments
  has_many :roles, :through => :list_assignments
end

# ... and the join models:
class RoleAssignment
  belongs_to :role
  belongs_to :user
end

class ListAssignment
  belongs_to :list
  belongs_to :role
end

What would be a named_scope or an association in the List model to find all Lists that have all Roles that in their turn have a specific User?

Any hint would be greatly appreciated!

Answer 1

The best solution I could come up with so far is the following:

class List
  has_many :list_assignments
  has_many :roles, :through => :list_assignments

  named_scope :managable_by_user, lambda { |u| 
    role_ids = u.roles.map(&:id)
    {
      :include => [:list_assignments], 
      :conditions => {:list_assignments => {:role_id => role_ids}}
    }
  }
end