[Dbix-class] more complex relationships

[Jess Robinson]

On Wed, 22 Mar 2006, Mark Hedges wrote:

>
>
> On Wed, 22 Mar 2006, Brandon Black wrote:
>
>> On 3/22/06, Mark Hedges <hedges at ucsd.edu> wrote:
>>> Here's an example that can't
> Something like this:
>
> CREATE TABLE product (
>    product_code        ENUM('XX','YY')                 NOT NULL,
>    description         TEXT,
>    PRIMARY KEY ( product_code )
> );
>
> CREATE TABLE vendor (
>    vendor_name         VARCHAR(24)                     NOT NULL,
>    PRIMARY KEY ( vendor_name )
> );
>
> CREATE TABLE vendor_product (   -- our products for sale at vendors
>    vendor_name         VARCHAR(24)                     NOT NULL,
>    product_id          VARCHAR(24)                     NOT NULL,
>
>    product_code        ENUM('XX',YY')                  NOT NULL,
>
>    PRIMARY KEY ( vendor_name, product_id )
> );
>
> CREATE TABLE sale (     -- i.e. invoice
>    sale_id             SERIAL,
>
>    vendor_name         VARCHAR(24)                     NOT NULL,
>
>    -- customer record foreign key, etc.
>    PRIMARY KEY ( sale_id )
> );
>
> CREATE TABLE sale_item (    -- item on the invoice
>    sale_item_id        SERIAL,
>
>    sale_id             BIGINT UNSIGNED                 NOT NULL,
>
>    product_id          VARCHAR(24)                     NOT NULL,
>
>    PRIMARY KEY ( sale_item_id )
> );
>


I just spent several minutes writing a DBIx::Class schema to handle this, 
and now it's occured to me that we haven't seen what you've tried..

It certainly doesn't look hard at all, so I have a suspicion that you just 
got your relationships mixed up, can you show the code you tried?

Also, whats a product_id? It seems to just exist in vendor_product's 
imagination.. and sale_item has one as well, but I have no idea where they 
are pointing.

.. Anyway, to get a vendor_name from a sale_item, you need to do something 
like:

DB::Schema::SaleItem->belongs_to(sale_id => 'DB::Schema::Sale');
DB::Schema::Sale->belongs_to(vendor_name => 'DB::Schema::Vendor');

then from a $saleitem:

$saleitem->sale_id->vendor_name

will get you a vendor object

(You can name the relationships however you like)

Jess