Rails way for querying join table in has_and_belongs_to_many

Question

I have a user model and a role model with a has_and_belongs_to_many reliationship. The join table is roles_users (two columns - the PK of the user and the role) and has no corresponding model.

I want to have a method that returns all users with a given role. In SQL that'd be something like

SELECT u.id FROM role.r, roles_users ru WHERE r.role_id = #{role.id} AND r.role_id = ru.role_id

I see that Rails' activerecord has a find_by_sql method, but it's only expecting one results to be returned.

What is the "Rails Way" to give me a list of users with a given role e.g.

def self.find_users_with_role(role)
  users = []
  users << # Some ActiveRecord magic or custom code here..?
end

Solution 1

Generally, HABTM association include this method by default!

IE: Role has_and_belongs_to_many :users

You need just call users method for current role:

role = Role.last

users = role.users

This is all ORM magic. Don't invent your bicycle :)

More information http://apidock.com/rails/ActiveRecord/Associations/ClassMethods/has_and_belongs_to_many

Solution 2

I'm assuming the roles are in a table called "roles". It would be something like this:

User.all(:joins => :roles,
         :conditions => {:roles => {:id => role.id}})

or for a class method-ish solution like you're presenting up there, use a named scope.

named_scope :with_role, lambda { |role_id| { :joins => :roles, 
                                             :conditions => {:roles => {:id => role_id} } } }
# call it like so:
User.with_role(role.id)

These are untested examples, so they might need a little tweaking.

Just options on ActiveRecord::Base#find: http://api.rubyonrails.org/classes/ActiveRecord/Base.html#M002263

Note the difference between :joins and :includes. There's a Railscast for that: http://railscasts.com/episodes/181-include-vs-joins

Solution 3

How about

    User.find(
      :all,
      :include => :roles,
      :conditions => ["roles.id = ?", role.id])