How best to find items joined across several tables?

Hi All,

For the sake of argument I have these models

user
has_many :posts

post
  belongs_to :user
  has_many :responses

response
  belongs_to :post

How do I find all responses that a user has had to all of their posts

obviously

User.find(1).posts.responses fails since posts returns an array of
post objects

I could use User.find(1).posts.map{|p| p.responses}.flatten but this
seems messy. I'm sure there must be a better (more rails) way of doing
this. Has anybody got any suggestions?

Many thanks

Anthony

User.find(1).posts.collect{|p| p.resposnses}

Or even better:

user
  has_many :resposnes, :through => :posts

Response.find(:all, :select => 'responses.*', :readonly => false,
               :join => "INNER JOIN posts ON posts.id = responses.post_id",
               :conditions => ["posts.user_id = ?", 1])

Of course, you could also do:

class User
   has_many :posts
   has_many :responses, :through => :posts
end

and then:

User.find(1).responses

(Since you asked for a "more rails" way.)

-Rob

Rob Biedenharn http://agileconsultingllc.com
Rob@AgileConsultingLLC.com

Hi Rob and Julian,

Thanks for the replies. I especially like the through method, which I
have used before but usually in a way to link say for example user <-
membership -> group. I hadn't thought of using it in this way. How far
can this be extended? Sy giving the silly example that there are sub-
responses (responses has_may sub_responses) is there a way to link the
user to the sub_responses with another through such that I can use
User.find(1).sub_responses?

Thanks

Anthony

IIRC I tried to do that and it didn't work.

From api.rubyonrails.org has_many section:

You can only use a :through query through a belongs_to or has_many
association on the join model.

But, to improve on the collect example you could do

User.find(:first,
  :conditions => [:id => 1],
  :include => [ {responses => sub_responses} ]).responses.collect{|r|
r.sub_responses}

Then, at least your only doing one query.

Or, another way is to do a finder_sql.

has_many :sub_responses, :finder_sql => 'fancy-pants sql join
expression'