[Dbix-class] more complex relationships

[Mark Hedges]

Matt asked me to join and forward this to the list to start a 
discussion on how to do N-directional joins in relationships.
Now it's limited to "me" and "foreign" in any relationship, but 
real-world data can be trickier than that.

I ended up writing a subroutine to get the PK info from the two
different directions and then call find() on the class I wanted.

I stored the results in a hash key of $self, is that right?  I
also used Want (list/arrayref) and weaken() (Scalar::Util), is
weakening necessary or is there some kludge like Class::DBI to
clean up stale references?


---------- Forwarded message ----------
Date: Mon, 20 Mar 2006 17:46:18 -0800 (PST)
From: Mark Hedges <hedges at ucsd.edu>
To: Matt S. Trout <mst at shadowcatsystems.co.uk>
Subject: more complex relationships


Oh, you mean you actually say 'foreign' and 'self' in the 
SQL::Abstract relationships, instead of just saying the table 
names and being able to build an alias field that joins across 
multiple tables in different ways.  Here's an example that can't 
be done:

  product         pk product_code

  vendor          pk vendor_name

  vendor_product  pk vendor_name, product_id 
                  fk product_code

  sale            pk sale_id
                  ff vendor_name

  sale_item       pk sale_item_id
                  fk sale_id
                  ff product_id

SaleItems know which vendor they belong to through their parent Sale.
There's no reason (using manual SQL joins) why I should have to store
the vendor_name in the sale_item table too.

But there is no (apparent) way for me to get at which product 
they are in DBIx::Class.

  SaleItem->Sale->VendorProduct -- ?? no product_id

  SaleItem->VendorProduct       -- ?? no vendor_name

What I "wanted to do" was this:

  # a sale has many vendor_products through its vendor and sale_item table
  C::Sale->add_relationship(
      vendor_products => C::VendorProduct,
      { 'sale_item.sale_id'           => 'sale.sale_id',
        'vendor_product.vendor_name'  => 'sale.vendor_name',
        'vendor_product.product_id'   => 'sale_item.product_id'
      },
      { accessor => 'multi' },
  );

Couldn't figure out from the exception why this is an "Invalid rel key."
Then I read the code and oh, 'foreign' in the manpage isn't some mnemonic
for the foreign table name, it's the string "foreign" and you can only
join directly across one hop.

The fix (short of enabling a huge feature in the code) is for me to
include the vendor_name in the sale_item table too, right?

Or am I missing something?

Thanks for your help.

Mark