Ruby-on-Rails: Multiple has_many :through possible?

Question

Is it possible to have multiple has_many :through relationships that pass through each other in Rails? I received the suggestion to do so as a solution for another question I posted, but have been unable to get it to work.

Friends are a cyclic association through a join table. The goal is to create a has_many :through for friends_comments, so I can take a User and do something like user.friends_comments to get all comments made by his friends in a single query.

class User   has_many :friendships   has_many :friends,             :through => :friendships,            :conditions => "status = #{Friendship::FULL}"   has_many :comments   has_many :friends_comments, :through => :friends, :source => :comments end  class Friendship < ActiveRecord::Base   belongs_to :user   belongs_to :friend, :class_name => "User", :foreign_key => "friend_id" end 

This looks great, and makes sense, but isn't working for me. This is the error I'm getting in relevant part when I try to access a user's friends_comments:
ERROR: column users.user_id does not exist
: SELECT "comments".* FROM "comments" INNER JOIN "users" ON "comments".user_id = "users".id WHERE (("users".user_id = 1) AND ((status = 2)))

When I just enter user.friends, which works, this is the query it executes:
: SELECT "users".* FROM "users" INNER JOIN "friendships" ON "users".id = "friendships".friend_id WHERE (("friendships".user_id = 1) AND ((status = 2)))

So it seems like it's entirely forgetting about the original has_many through friendship relationship, and then is inappropriately trying to use the User class as a join table.

Am I doing something wrong, or is this simply not possible?

Answer 1

Edit:

Rails 3.1 supports nested associations. E.g:

has_many :tasks has_many :assigments, :through => :tasks has_many :users, :through => :assignments 

There is no need for the solution given below. Refer to this screencast for more details.

Original Answer

You are passing a has_many :through association as a source for another has_many :through association. I don't think it will work.

  has_many :friends,             :through => :friendships,            :conditions => "status = #{Friendship::FULL}"   has_many :friends_comments, :through => :friends, :source => :comments 

You have three approaches to solving this issue.

1) Write an association extension

 has_many  :friends,             :through => :friendships,            :conditions => "status = #{Friendship::FULL}" do      def comments(reload=false)        @comments = nil if reload         @comments ||=Comment.find_all_by_user_id(map(&:id))      end  end 

Now you can get the friends comments as follows:

user.friends.comments 

2) Add a method to the User class.

  def friends_comments(reload=false)     @friends_comments = nil if reload      @friends_comments ||=Comment.find_all_by_user_id(self.friend_ids)   end 

Now you can get the friends comments as follows:

user.friends_comments 

3) If you want this to be even more efficient then:

  def friends_comments(reload=false)     @friends_comments = nil if reload      @friends_comments ||=Comment.all(               :joins => "JOIN (SELECT friend_id AS user_id                                FROM   friendships                                WHERE  user_id = #{self.id}                         ) AS friends ON comments.user_id = friends.user_id")   end 

Now you can get the friends comments as follows:

user.friends_comments 

All methods cache the results. If you want to reload the results do the following:

user.friends_comments(true) user.friends.comments(true) 

OR better still:

user.friends_comments(:reload) user.friends.comments(:reload)