# One To Many To Many

**URL:** <https://www.ruby-forum.com/t/one-to-many-to-many/72844>\
**Category:** Rails\
**Created:** [February 24, 2006, 4:41pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844 "2006-02-24T16:41:05Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 4:41pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/1 "2006-02-24T16:41:05Z")

</div>

Hey all,

I’m trying to find a simple way to reference all of the objects that  
belong to their parent’s parent. In other words:

Clients --\> Orders --\> LineItems

How do I get all of a client’s lineitems in one array (i.e.,  
client.all\_lineitems)?

Thanks!  
-Neal

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/2 "2006-02-24T18:25:21Z")

</div>

Neal L wrote:

> I’m trying to find a simple way to reference all of the objects that  
> belong to their parent’s parent. In other words:
> 
> Clients --\> Orders --\> LineItems
> 
> How do I get all of a client’s lineitems in one array (i.e.,  
> client.all\_lineitems)?

I’m assuming LineItem belongs\_to :order and Order belongs\_to :client.

If you are using Edge Rails, you can set this up as a  
has\_many…:through association, then you just say  
client.orders.line\_items and let Rails do the work for you.

If you’re on 1.0, you can do this with some custom SQL:

in class LineItem…  
def self.find\_for\_client(client)  
self.find\_by\_sql(‘SELECT DISTINCT li.\* FROM clients c, orders o,  
line\_items li WHERE c.id = :client\_id AND o.client\_id = c.id AND  
li.order\_id = o.id’, :client\_id =\> client.id)  
end

–josh

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 6:58pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/3 "2006-02-24T18:58:48Z")

</div>

I’m running Edge and client.orders.line\_items does not work.

It returns:

> > client.orders.line\_items  
> > NoMethodError: undefined method `line\_items’ for Order:Class

It would be awesome if this worked but it doesn’t.

-Jonathan

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 7:35pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/4 "2006-02-24T19:35:37Z")

</div>

Jonathan Y. wrote:

> I’m running Edge and client.orders.line\_items does not work.
> 
> It returns:
> 
> > > client.orders.line\_items  
> > > NoMethodError: undefined method `line\_items’ for Order:Class

How about showing your class definitions with the has\_many declarations?

If you’re on Edge, assuming you have the right foreign keys in the  
tables, you can do something like:

class Order \< ActiveRecord::Base  
has\_many :orders  
has\_many :line\_items, :through =\> :orders  
…  
end

Then you can do: client.line\_items

–josh

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 8:44pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/5 "2006-02-24T20:44:57Z")

</div>

Neal L wrote:

> Clients --\> Orders --\> LineItems

orders.map {|o| o.lineitems}

might work, though it’d generate a fair number of queries.

In SQL you could do:

SELECT li.\* FROM  
clients c, orders o, lineitems li  
WHERE  
o.id = c.order\_id AND li.id = c.lineitem\_id  
AND c.id = ?

Assuming appropriate indices, this should even be relatively fast.

/me curses at rail’s default id name breaking NATURAL JOIN

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 8:59pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/6 "2006-02-24T20:59:26Z")

</div>

Jonathan Y. wrote:

> client.line\_items works.  
> client.orders.line\_items does not work. Your original example was for  
> client.orders.line\_items.
> 
> It would be nice to be able to do client.orders.line\_items.

D’oh! You’re right, that was a brain fart. client.orders.line\_items  
won’t work. It would be nice if the association was smart enough to do  
that join for you using that syntax, wouldn’t it? I wonder how hard it  
would be to add that to the association magic?

–josh

---

<div class="post-metadata">

**Author:** ![Neal\_L](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/74df32/32.png) [@Neal\_L](https://www.ruby-forum.com/u/Neal_L)\
**Post date:** [February 24, 2006, 7:53pm UTC](https://www.ruby-forum.com/t/one-to-many-to-many/72844/7 "2006-02-24T19:53:31Z")

</div>

Right,

client.line\_items works.  
client.orders.line\_items does not work. Your original example was for  
client.orders.line\_items.

It would be nice to be able to do client.orders.line\_items.

It would be even nicer to traverse down to an arbitrary depth:

country = Country.find\_by\_name(‘United States’)  
country.states.clients.orders.line\_items # Returns all of the line  
items ordered in the United States. This is just an arbitrary example.

The SQL generated would look something like:

SELECT line\_items.\* FROM states LEFT JOIN clients ON states.id =  
clients.state\_id LEFT JOIN orders ON clients.id = orders.client\_id  
LEFT JOIN line\_items ON orders.id = line\_items.order\_id WHERE  
states.country\_id = 1

-Jonathan