# Retrieving "grandchild" data

**URL:** <https://www.ruby-forum.com/t/retrieving-grandchild-data/71466>\
**Category:** Rails\
**Created:** [October 9, 2006, 6:33am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466 "2006-10-09T06:33:24Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 6:33am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/1 "2006-10-09T06:33:24Z")

</div>

If I have three models:

Library has\_many :shelves  
Shelf belongs\_to :library and has\_many :books  
Book belongs\_to :shelf

What is the most efficient way to get all of the books in a Library?

Of course the models could be designed so that there is a direct  
library-book shortcut relationship as well, but assuming that doesn’t  
exist…

This does not work but I’m looking for something equivalent:

@books = @library.shelves.books

Is it best to start with books and include shelf? Like this:

@books = Book.find(:all, :include =\> :shelf, :conditions =\>  
[‘shelves.library\_id = ?’, @library.id]

I don’t believe the above works with great-grandchildren however so I’m  
curious about that as well. For example if Book has\_many :pages and I  
want to know all the pages in a certain library. I know it’s always  
possible with multiple database calls but wondering what is the most  
efficent way.

Thanks!!

---

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 6:56am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/2 "2006-10-09T06:56:44Z")

</div>

> @books = Book.find(:all, :conditions =\>  
> [‘shelves.library\_id = ?’, @library.id],  
> :include=\>{:shelf=\>{:books=\>:pages}}
> 
> You can nest this as much as you like.

Ah perfect! I knew there must be a Rails way to do the joins. Thanks  
very much!

---

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 6:47am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/3 "2006-10-09T06:47:01Z")

</div>

On 10/9/06, Carl J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Of course the models could be designed so that there is a direct  
> [‘shelves.library\_id = ?’, @library.id]  
> No, like this:

@books = Book.find(:all, :conditions =\>  
[‘shelves.library\_id = ?’, @library.id],  
:include=\>{:shelf=\>{:books=\>:pages}}

You can nest this as much as you like. Just be aware of the slightly  
quirky  
naming conventions for the table aliases, in case you want to add  
conditions  
to the child or grandchild tables. Have a look at the Rails doco for  
Association.

Cheers,  
Max

---

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 7:19am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/4 "2006-10-09T07:19:02Z")

</div>

On 10/9/06, Carl J. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Of course the models could be designed so that there is a direct  
> [‘shelves.library\_id = ?’, @library.id]
> 
> I don’t believe the above works with great-grandchildren however so I’m  
> curious about that as well. For example if Book has\_many :pages and I  
> want to know all the pages in a certain library. I know it’s always  
> possible with multiple database calls but wondering what is the most  
> efficent way.
> 
> Thanks!!

This is a classic example of why :through was added to has\_many in 1.1 -  
see the excellent tutorials at:

[http://blog.hasmanythrough.com/articles/2006/02/28/association-goodness](http://blog.hasmanythrough.com/articles/2006/02/28/association-goodness)  
[http://blog.hasmanythrough.com/articles/2006/03/01/association-goodness-2](http://blog.hasmanythrough.com/articles/2006/03/01/association-goodness-2)

Essentially, you add an additional declaration to Library:  
has\_many :books, :through =\> :shelves

And then you can say

@books = @library.books

and everything should work normally.

Not sure about the next level (library-\>shelf-\>book-\>page) - any hm:t  
experts  
want to offer some advice?

–  
Matt J.  
removed\_email\_address@domain.invalid  
President/Technical Director, Acme Art Company ([acmeartco.org](http://acmeartco.org))

---

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 9:37am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/5 "2006-10-09T09:37:14Z")

</div>

Carl J. \<rails-mailing-list@…\> writes:

> 

Be careful though. As you join more tables together, if you don’t have  
enough  
indexing on the columns which are being joined then your queries will  
really  
start to drag as more data is added.

---

<div class="post-metadata">

**Author:** ![cjnkns](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/c5a1d2/32.png) [@cjnkns](https://www.ruby-forum.com/u/cjnkns)\
**Post date:** [October 9, 2006, 8:03am UTC](https://www.ruby-forum.com/t/retrieving-grandchild-data/71466/6 "2006-10-09T08:03:06Z")

</div>

Max hit the fastest solution on it with find() and :include, but at the  
end of the day, these are all Ruby enumerables, so you can fall back  
onto Ruby operators (albeit at the cost of more SQL queries):

@pages = library.shelves.map { |s| s.books.map { |b| b.pages } }  
}.flatten