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

[John Napiorkowski]

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