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?

Harish Shetty · Accepted Answer · 2012-10-12T16:14:13.270

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)

Unfortunately, the original purpose behind this approach was so that I could get all of the friends comments from SQL in one query. See here: http://stackoverflow.com/questions/2382642/ruby-on-rails-how-to-pull-out-most-recent-entries-from-a-limited-subset-of-a-dat If I write a function, that's going to result in an N+1 number of queries. — William Jones, Mar 04 '10 at 23:56
No it will not. It will be 2 queries. First query to get the friends and second query to get the comments for **all** the friends. If you have already loaded the friends to the user model then you are not incurring any cost. I have updated the solution with two more approaches. Take a look. — Harish Shetty, Mar 05 '10 at 00:17
@williamjones You don't have N+1 issue in all three approaches. You can check your log file to ensure this. Personally I like approach 1 as it is quite elegant, i.e. `user.friends.comments` is better than `user.friends_comments` — Harish Shetty, Mar 05 '10 at 00:35
Could you please explain the argument to find_all_by_users_id in the first solution? I'd appreciate it. — James, Jul 09 '10 at 01:34
@James: Rails provides two types of dynamic finders: single row (`find_by`) and multi-row(`find_all_by`). The `find_all_by_user_id` is multi-row dynamic finder. Read the rails documentation for more details: http://guides.rubyonrails.org/active_record_querying.html#dynamic-finders — Harish Shetty, Jul 11 '11 at 03:22

score 8 · Answer 2 · edited Nov 30 '16 at 09:40

8

There is a plugin that solves your problem, take a look at this blog.

You install the plugin with

script/plugin install git://github.com/ianwhite/nested_has_many_through.git

edited Nov 30 '16 at 09:40

Jai Chauhan

4,035
3
36
62

answered Mar 24 '10 at 08:30

Jarl

2,831
4
24
31

score 5 · Answer 3 · answered Dec 01 '11 at 19:17

5

Although this didn't work in the past, it works fine in Rails 3.1 now.

answered Dec 01 '11 at 19:17

Andrew Culver

1,967
2
13
7

score 3 · Answer 4 · answered Jul 12 '11 at 14:12

3

I found this blog entry to be useful: http://geoff.evason.name/2010/04/23/nested-has_many-through-in-rails-or-how-to-do-a-3-table-join/

answered Jul 12 '11 at 14:12

Bryan Larsen

9,468
8
56
46

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

4 Answers4

Linked