Rails method to query the join table in has_and_belongs_to_many

Question

I have a user model and a role model with the has_and_belongs_to_many relation. The connection table is role_users (two columns - user PK and role) and does not have a corresponding model.

I want to have a method that returns all users with a given role. In SQL, that will 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 the activating Rails element has a find_by_sql method, but it expects only one result.

What is the "Rails Way" to give me a list of users with a given role, for example.

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

Answer 1

As a rule, the HABTM association includes this method by default!

IE: has_and_belongs_to_many Role: Users

You just need to call the users method for the current role:

role = Role.last

users = role.users

This is all the magic of ORM. Do not reinvent your bike :)

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

Answer 2

I assume the roles are in a table called "roles". It will be something like this:

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

or to solve the method-ish class that you present there, use 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 unverified examples, so they may need a little tweaking.

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

Note the difference between: join and: includes. There is a Railscast for this: http://railscasts.com/episodes/181-include-vs-joins

Answer 3

What about

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

Answer 4

Updated: Your mentioned method can be achieved as follows:

 def self.find_users_with_role(role) role.users end 

---

It is easy and can be done in 2 steps:

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

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

Secondly, find all the users associated with this role.

all_users = role.users

It is assumed that you have correctly configured the relationship between the user and role models with the has_and_belongs_to_many relationship

 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 

Answer 5

This also works:

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