[CDBI] Preloading objects when using a view

Bill Moseley moseley at hank.org
Wed Mar 29 16:17:04 BST 2006

On Wed, Mar 29, 2006 at 08:38:35AM -0500, Juan Camacho wrote:
> On 3/28/06, Bill Moseley <moseley at hank.org> wrote:
> > It would be nice to do something like:
> >
> >     __PACKAGE__->has_a( artist_name => ['Music::Artist', 'name' ] );
> >
> Nothing much I can contribute here :) I simply wanted to say that it
> would also be nice that the implementation include an option for a
> LEFT JOIN.  In that way, you are guaranteed to have the CD data and it
> would be closer to the results you get from doing two calls to the db.

I'm not trying to add joins to CDBI with the above, rather a way to
have CDBI populate columns in related objects when the column values
are returned in a select on a view.

This is a simplified example, but with a view like:

create view cd_info as
  select cd.id as id,
         cd.inventory_num as inventory_num,
         cd.artist as artist,
         artist.name as artist_name
  from cd join artist on cd.artist = artist.id;

Then I might have a class for that view that inherits from

What would be nice is for CDBI to know that "artist_name" maps to the
"name" column in Music::Artist and populate that column when creating
the related object.  That way when displaying my list of CDs calling
$cd->artist->name won't cause CDBI to go back to the database again
for each row.

Could this be done with a custom relationship? This is kind of ugly:

        'Music::Artist' => {
            artist_name => 'name',
            artist_age  => 'age',
            artist_id   => 'id',
        } );

One way is to override _init in the class that represents the view.
A long-hand approach.

sub _init {
    my $class = shift;

    my $data = $_[0] || {};

    # Construct related objects

    $data->{location} = DB::Location->construct( {
                            id => $data->{location},
                            name => $data->{location_name},
    $data->{workshop} = DB::Workshop->construct( {
                            id => $data->{workshop},
                            name => $data->{workshop_name},
    $data->{class_status} = DB::ClassStatus->construct( {
                            id => $data->{class_status},
                            name => $data->{class_status_name},

    my $object = bless {}, $class;
    $object->_attribute_store( $data );

    return $object;

I suppose I could prefix the columns in the view with the related
table name and use meta_info to try and guess which columns returned
in the view represent columns in related classes, but that seems like
a bit of overhead.

Bill Moseley
moseley at hank.org

More information about the ClassDBI mailing list