Filtering results based on a many-to-many relationship

Question

Posts belong to Users. Users have and belong to many roles (i.e., the tables we're dealing with here are posts, users, roles, and roles_users)

How can I select all posts written by a user who has the "Editor" role?

I.e., I want the database-driven version of

Post.all.select{|p| p.user.roles.map(&:name).include?("Editor")}}

Answer 1

You could try something like this:

class Role < ActiveRecord::Base
  has_and_belongs_to_many :users
  has_many :posts, :through => :users
end

Then you can do something like this:

Role.find(editor_id).posts

Answer 2

Post.all(:joins => {:user => :roles}, :conditions => ["roles.name = ?", "editor"])