How best to find items joined across several tables?

Rails
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

On Jun 12, 2008, at 1:58 PM, julian wrote:

post
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

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 B. http://agileconsultingllc.com
[email protected]

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

On Jun 12, 7:17 pm, Rob B. [email protected]

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’