How to create has_many_and_belongs_to association for conditions on a different table in Rails?

Question

I have a users table for User model which is created via devise, roles table for Role model. Both User and Role are associated with HABTM.

roles:
id: integer
name: string

roles_users:
user_id:integer
roles_id:integer

I have three roles: admin, manager, and reporter.

Now, I create a Team model which looks like this:

teams:
name: string

I want to have many managers for one team.

So, if I create a team:

t = Team.create(name: 'Football')

then I would like to fetch all the managers( users who have role: 'manager') who handles the team 'Football'. So, I created HABTM between Team and User.

teams_users:
team_id:integer
user_id:integer

I need an association: managers inside Team model. But, so far this code isn't working:

class Team < ActiveRecord::Base
  attr_accessible :name

  validates :name, presence: true

  has_and_belongs_to_many :managers, class_name: 'User', association_foreign_key: 'user_id', conditions: joins(:roles, :roles_users).where('roles_users.role_id = ?', Role.select('id, name').find_by_name('manager').id)

end

When I do: t.managers I get this error:

ActiveRecord::ConfigurationError: Association named 'roles' was not found on Team; perhaps you misspelled it?
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/join_dependency.rb:112:in `build'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/join_dependency.rb:123:in `block in build'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/join_dependency.rb:122:in `each'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/join_dependency.rb:122:in `build'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/join_dependency.rb:18:in `initialize'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/relation/query_methods.rb:358:in `new'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/relation/query_methods.rb:358:in `build_joins'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/relation/query_methods.rb:266:in `build_arel'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/relation/query_methods.rb:260:in `arel'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/relation/delegation.rb:29:in `respond_to?'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association.rb:161:in `interpolate'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:8:in `interpolate'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:81:in `block (2 levels) in add_constraints'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:76:in `each'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:76:in `block in add_constraints'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:39:in `each'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:39:in `each_with_index'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:39:in `add_constraints'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association_scope.rb:31:in `scope'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association.rb:99:in `association_scope'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/association.rb:88:in `scoped'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/collection_association.rb:382:in `find_target'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/collection_association.rb:335:in `load_target'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/collection_proxy.rb:44:in `load_target'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/activerecord-3.2.17/lib/active_record/associations/collection_proxy.rb:89:in `method_missing'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.17/lib/rails/commands/console.rb:47:in `start'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.17/lib/rails/commands/console.rb:8:in `start'
    from /Users/suryat/.rvm/gems/ruby-1.9.2-p290/gems/railties-3.2.17/lib/rails/commands.rb:41:in `<top (required)>'
    from script/rails:6:in `require'
    from script/rails:6:in

What am I doing wrong? Any suggestions?

Answer 1

Setup the association as below:

has_and_belongs_to_many :managers, class_name: 'User', 
                        association_foreign_key: 'user_id', 
                        include: :roles, 
                        conditions: ['roles_users.role_id = ?', Role.select('id, name').find_by_name('manager').id]

You are getting the error as Association named 'roles' was not found on Team because you tried to call association of roles and roles_users on teams (with the conditions option) and teams is not associated with either of them.

What you need in this case is to specify a second-order association using include option so the association of roles is applied on users table instead of teams