[Dbix-class] complex relationships I

[Matt S Trout]

On Thu, Sep 20, 2007 at 11:32:20AM -0700, Benjamin Hitz wrote:
> 
> Hi, we are in the process of replacing an old, hand-rolled API (and  
> bags-o-sql) with DBIc.  Our Oracle database is pretty large (~110  
> tables) with some complicated relationships.  We have used  
> DBIx::Class::Schema::Loader::Oracle to generate some table classes,  
> but of course they don't find all the many-to-manys.
> 
> Looking for some advice on how to implement a couple types of  
> relationships (This is long, so I will put the next one in another  
> email)
> 
> "Trees" e.g.:
> CREATE TABLE CV_TERM
> (CV_TERM_NO NUMBER(10) NOT NULL
> ,CV_NO NUMBER(10) NOT NULL
> ,TERM_NAME VARCHAR2(1024) NOT NULL
> ,DBXREF_ID VARCHAR2(40)
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> ,CVTERM_DEFINITION VARCHAR2(4000)
> )
> CREATE TABLE CVTERM_RELATIONSHIP
> (CVTERM_RELATIONSHIP_NO NUMBER(10) NOT NULL
> ,CHILD_CV_TERM_NO NUMBER(10) NOT NULL
> ,PARENT_CV_TERM_NO NUMBER(10) NOT NULL
> ,RELATIONSHIP_TYPE VARCHAR2(40) NOT NULL
> ,DATE_CREATED DATE DEFAULT SYSDATE NOT NULL
> ,CREATED_BY VARCHAR2(12) DEFAULT SUBSTR(USER,1,12) NOT NULL
> )
> So CVTERM_RELATIONSHIP has two foreign keys to the CV_TERM table.

Shouldn't the parent id be the PK if multiple parents are disallowed?
 
> This comes out from Schema::Loader as:
> 
> In the Cv_term.pm module:
> 
> __PACKAGE__->has_many(
>   "cvterm_relationship_child_cv_term_noes",
>   "DB::Mod::Cvterm_relationship",
>   { "foreign.child_cv_term_no" => "self.cv_term_no" },
> );
> __PACKAGE__->has_many(
>   "cvterm_relationship_parent_cv_term_noes",
>   "DB::Mod::Cvterm_relationship",
>   { "foreign.parent_cv_term_no" => "self.cv_term_no" },
> );

and then this could be a belongs_to

> In the Cvterm_relationship.pm module:
> __PACKAGE__->belongs_to(
>   "child_cv_term_no",
>   "DB::Mod::Cv_term",
>   { cv_term_no => "child_cv_term_no" },
> );
> __PACKAGE__->belongs_to(
>   "parent_cv_term_no",
>   "DB::Mod::Cv_term",
>   { cv_term_no => "parent_cv_term_no" },
> );
> 
> So, I would guess I want to add:
> 
> __PACKAGE__->many_to_many (children =>  
> 'cvterm_relationship_child_cv_term_noes', 'child_cv_term_no');
> and
> __PACKAGE__->many_to_many (parents =>  
> 'cvterm_relationship_parent_cv_term_noes', 'parent_cv_term_no');
> 
> to Cv_term.pm
> 
> Of course, this is kinda silly because we don't really have multiple  
> parents (but it is technically allowed in the schema).

Well, a many-many is really a bridge across two relationships. They don't
technically have to include a one-many one.

> So if I did something like:
> 
> my $CvTermObj = DBIx::Class::Schema::Subclass->schema->resultset 
> ('CV_TERM')->find({term_name => 'physical interactions'});
> (actually not sure if find returns a single object in scalar context,  
> but that's a detail)

find() always returns either a single object or undef.

You might find writing the parent method by hand best so that it enforces
what your schema doesn't.

> my @arrayOfChildrenWhichAreAlsoCvTermObjs = $cvTermObj->children();
> 
> That didn't seem so bad... maybe the next one is tougher?

I've done pretty much exactly this one before except a multi-parent DAG.

So no, it's not so bad :)

-- 
      Matt S Trout       Need help with your Catalyst or DBIx::Class project?
   Technical Director                    http://www.shadowcat.co.uk/catalyst/
 Shadowcat Systems Ltd.  Want a managed development or deployment platform?
http://chainsawblues.vox.com/            http://www.shadowcat.co.uk/servers/