Filtering child objects in a has_many :through relationship in Rails 3

Greetings,

I have an application where Companies and Users need to belong to each other through a CompanyMembership model, which contains extra information about the membership (specifically, whether or not the User is an admin of the company, via a boolean value admin). A simple version of the code:

class CompanyMembership < ActiveRecord::Base
  belongs_to :company
  belongs_to :user
end

class Company < ActiveRecord::Base
  has_many :company_memberships
  has_many :users, :through => :company_memberships
end

class User < ActiveRecord::Base
  has_m开发者_StackOverflowany :company_memberships
  has_many :companies, :through => :company_memberships
end

Of course, this makes it simple to get all the members of a company via company.users.all, et al. However, I am trying to get a list of all Users in a Company who are admins of that Company (and also to test whether a user is an admin of a given company). My first solution was the following in company.rb:

def admins
  company_memberships.where(:admin => true).collect do |membership|
    membership.user
  end
end

def is_admin?(user)
    admins.include? user
end

While this works, something feels inefficient about it (it's iterating over each membership, executing SQL each time, right? Or is Relation smarter than that?), and I'm not sure if there's a better way to go about this (perhaps using scopes or the fancy new Relation objects that Rails 3 uses?).

Any advice on the best way to procede (preferably using Rails 3 best practices) would be greatly appreciated!

---

I believe I was going about this the wrong way, specifying conditions on company_memberships instead of users, which was what I actually wanted (a list of Users, not a list of CompanyMemberships). The solution I think I was looking for is:

users.where(:company_memberships => {:admin => true})

which generates the following SQL (for company with ID of 1):

SELECT "users".* FROM "users"
  INNER JOIN "company_memberships"
    ON "users".id = "company_memberships".user_id
  WHERE (("company_memberships".company_id = 1))
    AND ("company_memberships"."admin" = 't')

I'm not sure yet if I'll need it, but the includes() method will perform eager loading to keep down the number of SQL queries if necessary:

Active Record lets you specify in advance all the associations that are going to be loaded. This is possible by specifying the includes method of the Model.find call. With includes, Active Record ensures that all of the specified associations are loaded using the minimum possible number of queries.queries. ^{RoR Guides: ActiveRecord Querying}

(I'm still open to any suggestions from anyone who thinks this isn't the best/most effective/right way to go about this.)

---

An even cleaner way would be to add an association to your Company model, something like this:

has_many :admins, :through => :company_memberships, :class_name => :user, :conditions => {:admin => true}

You might have to dig into the rails doc to get the exact syntax right.

You shouldn't need :include, unless you have other classes associated with :user that you might reference in your view.

---

How about something like this:

Company.find(:id).company_memberships.where(:admin => true).joins(:user)

---

I stumbled across this answer and believe that nowadays there is a nicer way using has_many association scopes (has_many documentation):

has_many :admins, -> { where(admin: true) }, through: :company_memberships, class_name: :user

The second parameter of a has_many association can be a proc or lambda that contains your filter.

---

I made the activerecord_where_assoc gem to do this.

With it, no need for joins or includes. Doing eager loading remains your choice, not an obligation.

users.where_assoc_exists(:company_memberships, admin: true, company_id: @company.id)

The question is unclear on how to filter the company, so I added it myself. Without this, if a user can be in multiple company, it being admin in one company could mean being treated as an admin in another company.

The gem doesn't work in Rails 3, but we are 9 years later and Rails 4.1 and more are supported.

Here are the introduction and examples. Read more details in the documentation.