One To Many To Many

Rails
1

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

2

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

3

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

4

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

5

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

6

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

7

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