How best to find items joined across several tables?

rubyonrails-talk
1

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

2

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

3

Or even better:

user   has_many :resposnes, :through => :posts

4

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

5

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

6

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.

7

Or, another way is to do a finder_sql.

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