# How best to find items joined across several tables?

**URL:** <https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296>\
**Category:** Rails\
**Created:** [June 12, 2008, 7:05pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296 "2008-06-12T19:05:33Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 7:05pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/1 "2008-06-12T19:05:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 7:58pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/2 "2008-06-12T19:58:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 8:03pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/3 "2008-06-12T20:03:54Z")

</div>

Or even better:

user  
has\_many :resposnes, :through =\> :posts

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 8:17pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/4 "2008-06-12T20:17:59Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 9:25pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/5 "2008-06-12T21:25:13Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 9:37pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/6 "2008-06-12T21:37:59Z")

</div>

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

From [api.rubyonrails.org](http://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.

---

<div class="post-metadata">

**Author:** ![ant](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/a/85e7bf/32.png) [@ant](https://www.ruby-forum.com/u/ant)\
**Post date:** [June 12, 2008, 9:48pm UTC](https://www.ruby-forum.com/t/how-best-to-find-items-joined-across-several-tables/140296/7 "2008-06-12T21:48:41Z")

</div>

Or, another way is to do a finder\_sql.

has\_many :sub\_responses, :finder\_sql =\> ‘fancy-pants sql join  
expression’