[Dbix-class] Is there a better way to handle inserts with multi column primary keys?

[John Napiorkowski]

Turns out a more careful reading of the documentation
could answer this question.  All the '->find(xx)'s
where not needed.  If the schemas are properly written
 using 'belong_to' and 'has_many' then I could just
say (from the lower example):

my $schema = DB::Main->connect(...);
my $doc = $schema->resultset('documents')->find(1);

my $paras = $doc->paragraphs;

and get an iterator that works the way I wanted.  When
I get a change I will write a clarification posting so
that other newbies like me can understand (hopefully)
the 'DBIx way'.

--john

--- John Napiorkowski <jjn1056 at yahoo.com> wrote:

> This is a question about multi column relationships.
> 
> Let's say you have two tables like this in postgres.
> 
> The first table is a list of document titles and the
> second is a list of the titles for paragraphs in a
> given document:
> 
> create table documents (
>  document_id serial not null,
>  title varchar(24) not null,
>  constraint PRIMARY KEY (document_id)
> );
> 
> create table paragraphs (
>  paragraph_id serial not null,
>  document_id int not null,
>  title varchar(24) not null,
>  constraint PRIMARY KEY (paragraph_id, document_id),
>  constraint FORIEGN KEY (document_id) REFERENCES
> documents(document_id)
> );
> 
> and in table documents you have
> 
> document_id, title
> 1, "First Document"
> 2, "Second Document"
> 
> and in table paragraphs you have
> 
> document_id, paragraph_id, title
> 10, 1, "First Paragraph in First Document"
> 10, 2, "Second Paragraph in First Document"
> 11, 1, "First Paragraph in SecondDocument"
> 
> Now if I set up my DBIx schemas properly I can
> access
> the paragraphs through documents like so:
> 
> my $schema = DB::Main->connect(...);
> my $doc = $schema->resultset('paragraphs')->find(1);
> my $para = $doc->paragraphs->find({document_id=>1,
> paragraph_id=>1});
> 
> Basically this works, but I was wondering if there
> is
> a way to avoid repeating the document_id in the
> second
> find.  I am thinking this is possible because the
> $doc
> object knows it's own primary_key.  In fact when I
> turn on sql debugging to see what SQL is generated I
> see something like:
> 
>     -> prepare_cached for DBD::Pg::db
> (DBI::db=HASH(0x9a34824)~0x9a35574 'SELECT
> pa.paragraph_id, pa.document_id, pa.title FROM
> paragraphs pa me WHERE ( ( ( pa.document_id = ? AND
> pa.paragraph_id = ? ) AND ( pa.document_id = ? ) )
> )'
> HASH(0x9a503a8) 3) thr#814c008
> 
> So I can see that DBIx is repeating the contraint
> for
> document_id twice, thus is is smart enough to know
> that to properly do a find it needs to do this.
> 
> So I though I could just try:
> 
> my $para =
> $doc->paragraphs->find({paragraph_id=>1});
> 
> or even:
> 
> my $para = $doc->paragraphs->find(1);
> 
> But both give me errors.
> 
> Although it works in the example above, I'd like to
> find out if there is a way I can simplify my syntax
> and avoid repeating the second document_id.  I think
> the ideal syntax would be something like:
> 
> my $para =
> $doc->paragraphs->find({paragraph_id=>1});
> 
> or something like it.  Does anyone have experience
> with this?  If there is no way to do this and other
> people think it's a good idea I'd like to work on a
> patch to provide it.
> 
> Thanks for your insight.
> 
> --john
> 
> 
> 
> 
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> http://mail.yahoo.com 
> 
> _______________________________________________
> List:
>
http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
> Wiki: http://dbix-class.shadowcatsystems.co.uk/
> IRC: irc.perl.org#dbix-class
> SVN:
>
http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
> 


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com