Gravatar

Geoff Evason

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.