Mapping column values to objects on create() or select().

[prev] [thread] [next] [Date index for 2005/06/23]

Hi.  This question is so stupid, but I've been trying to understand
the documentation in search of the most straightforward way to do
this.  I can think of several ugly-looking ways to do this, but I am
sure CDBI provides the way.  I just don't think I know the right way
to ask this question.  Perhaps it is intuitively obvious to everyone
else so no one ever asks.

Often, I will have a table where several columns are foreign keys to
some other table.  The other table has a primary key that's not useful
for a (normal) humans -- it's an integer.  But there's another column
in the referenced table that's a unique string.  So it's annoying for
me to have to do

 my $film = Film->retrieve( name => 'Clash of the Titans' );
 my $theater = Theater->retrieve( name => 'Cinemegaloplex III' );
 my $showing = 
    Show->create( { film => $film, theater => $theater, time => ... } );

I just want to pass the string in the name column instead and have Show DWIM:

 my $showing = Show->create( 
    { film => 'Clash of the Titans', theater => 'Cinemegaloplex III', ... } );

Is this a constraint, or a trigger, or validation, or deflation, or
what?  I can envision writing some kind of before_create trigger or a
deflate for each column to make sure the name refers to a thing that
exists in the corresponding table, but which is more right?  I really
want the same thing to handle my selects, too.  Say, return all the 
showings of my movie by just searching on the string, not retrieving it first.

Rollin