Help with ActiveRecord for Models with long associtation chain

Hi,

I am trying to use this set of associations for what must be a fairly
standard data model, to give me the accessor @batch.products

Batch:
  has_many :orders
  has_many :line_items :through=>orders
Order:
  has_many :line_items
  belongs_to :batch
  has_many :products, :through=>:line_items
LineItem:
  belongs_to :product
  belongs_to :order
Product::
  has_many :line_items

OK I think they are the main associations involved.

I sort of want to say that Batch
has_many :products, :through=>orders, :through=>line_items

This doesnt work and I have tried other variations. The only way i
can think at the moment is to make products into a named scope using
an sql find, but I am sure there must be a way of telling AR how to do
the joins without using sql.

TRANSACTION question?
I want to get to @batch.products so that I can iterate through each
product and update the product stock quantities based on the line_item
quantities. Then mark the batch as updated and finally commit as a
single batch save. My thinking being that this would be carried out
as a safe transaction. The alternative, would be to build a specific
transaction, but I am not sure how to do this across 2 models (ie
Batch and Product)

All help much appreciated.
Tonypm

Hi,

OK I think they are the main associations involved.

I sort of want to say that Batch
has_many :products, :through=>orders, :through=>line_items

This doesnt work and I have tried other variations. The only way i
can think at the moment is to make products into a named scope using
an sql find, but I am sure there must be a way of telling AR how to do
the joins without using sql.

has_many :through can't be nested (I've vague feeling there's a plugin
that does this).
You could probably simplify the find down by using :joins (ie
something like
Product.find :all, :joins => {:line_items => {:order => :batch}},
                         :conditions => {'batch.id' => some_batch_id

TRANSACTION question?
I want to get to @batch.products so that I can iterate through each
product and update the product stock quantities based on the line_item
quantities. Then mark the batch as updated and finally commit as a
single batch save. My thinking being that this would be carried out
as a safe transaction. The alternative, would be to build a specific
transaction, but I am not sure how to do this across 2 models (ie
Batch and Product)

Unless batch and product are in different databases, Batch.transaction
and Product.transaction are exactly the same. If you don't like
'preferring' one over the other you could always do
ActiveRecord::Base.transaction do
  ...
end

Fred

Fred,

thanks for very quick response. That clarifies things and helps me
move forward.

Actually my update is complicated further, since a line_item can be a
packet with a bill of materials of several products so transaction
will be the way to go. and I see I dont need to worry about tables
since the transaction is on the actual connection not the database.

Many Thanks
Tonypm

Fred - Oh and by the way!

Product.find :all, :joins=>{:line_items=>{:order
=> :batch}}, :conditions=>{'batches.id'=>361}

worked perfectly - wouldnt have figured that out myself!

Thanks
Tonypm