Nested has_many: through in Rails (or how to do a 3 table join)
Today I came across a problem that I would have expected would work in Rails (2.3.5), but doesn’t. I wanted to use a has_many :through relationship on a association that was itself a has_many :through.
Here is an example. Let’s say I have Group class, which has many users through memberships. Further, a user has many comments. What I want is a simple way to get all the comments that were made by users in a group.
class Group < ActiveRecord::Base
has_many :memberships
has_many :users, :through => :memberships
# This is what I would like to do, but this does not work!
# has_many :comments, :through => :users
end
class User < ActiveRecord::Base
has_many :comments
has_many :memberships
has_many :groups, :through => :memberships
end
class Comment < ActiveRecord::Base
belongs_to :user
has_many :likes
named_scope :approved, :conditions => {:approved => true}
end
What I want is to be able to call
@group = Group.find(params[:id])
@comments = @group.comments.approved.find(:all, :include => :likes)
Option #1 : Includes : use includes when loading the group so I can eager load the comments. Then I can collect them:
@group = Group.find(params[:id], :include => {:users, :comments})
@comments = @group.users.map{|u| u.comments}.flatten
Problems with Option #1 : First, it is inefficient. If I don’t use the user objects anywhere, I’m loading them for nothing. Second, I can’t use pagination or other filters easily on the comments association.
Option #2 : find_by_sql : use a method on Group to load up comments, like so:
class Group < ActiveRecord::Base
has_many :memberships
has_many :users, :through => :memberships
# This is what I would like to do, but this does not work!
# has_many :comments, :through => :users
def comments
# Use a 3 table sql join to load the comments for all users in this group.
Comment.find_by_sql("
SELECT c.* FROM comments c
INNER JOIN users u ON u.id = c.user_id
INNER JOIN memberships m ON m.user_id = u.id
WHERE m.group_id = #{id}")
end
end
Problems with Option #2 : You can’t do eager loading, pagination, or use any named scopes on the comments class. So, if I wanted to load on ‘approved’ comments I’d have to write another method. boo.
Option #3 : named_scope + method : use a named_scope on Comment and a method on Group so that i can make the calls that I want to…
class Group < ActiveRecord::Base
has_many :memberships
has_many :users, :through => :memberships
# This is what I would like to do, but this does not work!
# has_many :comments, :through => :users
# This lets us call the code in a nice looking way:
# e.g. group.comments.approved
def comments
Comment.all_for_group(self)
end
end
class Comment < ActiveRecord::Base
belongs_to :user
has_many :likes
named_scope :approved, :conditions => {:approved => true}
# perform the 3 table join in a way that will
# let us also call include and other filters.
named_scope :all_for_group, lambda{ |group|
{
:joins => {:users, :memberships},
:conditions => {:memberships => {:group_id => group.id},
:select => "DISTINCT `comments`.*"
}
}
end
Option #3 is definitely the cleanest, and what I would recommend.
I’m not sure if Rails 3 supports nested has_many :through, but 2.3.5 does not. There is a (very old) ticket for Rails. There is also a nested has_many :through plugin that is has an experimental branch for 2.3.x. I don’t like using things that are ‘experimental’.
The number of times you need to do multiple has_many :through associations should be fairly small. If you are doing it a lot, you should probably reconsider your data model. In most cases, a simple named_scope and method ought to do the trick for you like it did for me.
