Rails way for querying join table in has_and_belongs_to_many

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 so开发者_如何学Gomething 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

---

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

---

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

---

How about

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

---

Updated: Your mentioned method can be achieved like this:

def self.find_users_with_role(role)
  role.users
end

---

It is easy and can be done in 2 steps:

First, find the role you want to return all users from.

#Assume the role you want has the id of 3.
role = Role.find(3)

Second, find all the users associated with that role.

all_users = role.users

This assumes that you correctly set up the association between user and role models with the relationship has_and_belongs_to_many

class User < ActiveRecord::Base
  has_and_belongs_to_many :roles
#More codes below
end

and

class Role < ApplicationRecord
  has_and_belongs_to_many :users
end

---

This also works:

users = Role.find(:all, conditions => ["id = ?", role_id]).map{ |role| role.users }