Constructing objects in multiple CDBI classes w. single SELECT

[prev] [thread] [next] [Date index for 2005/03/02]

There was some talk recently about searching using the columns of
related classes.

There are two parts to this problem: the first is how to specify and perform
the search, and the second is how to create the many related CDBI
objects as a result of the search.  It's this second part that I'm
interested in at the moment.

Currently, to retrieve the titles of all of the an artist's tracks we do
the following:

    foreach my $artist (Music::Artist->retrieve_all) {
        foreach my $cd ($artist->cds) {
            foreach my $track ($cd->tracks) {
                print $track->title, "\n";
            }
        }
    }

In SQL, this could be done with one query.  In Class::DBI it's done with
many queries.  (I belive it's one query per artist, one per album, and
one per track.  More maybe?)

Sometimes this approach works fine.  But if I want to show an artist's
entire discography on a web page, that could mean hundreds of queries
for one web request.

When I've had to deal with this issue in the past, I've bypassed
Class::DBI entirely and used set_sql with $sth->fetchall_arrayref({}) to
get a list of hashrefs instead.

But then I had trouble keeping track of which parts of my code used
Class::DBI objects and which parts used hashrefs...

With a custom set_sql query and a few custom methods, I've figured out
how to populate many related Class::DBI objects with a single query. 
The code is below.

I'm interested in making this a more general purpose solution.  I have a
few questions:

1) Limitations of HasMany

I had to go behind the back of HasMany and create a custom 'cds'
method in Music::Artist and a custom 'tracks' method in Music::CD.

This is because behind the scenes, I store tracks in a cd like this:

    $cd->{'tracks'} = \@list_of_music_track_objects;

So my custom 'tracks' method returns $self->{'tracks'} if it exists, and
otherwise runs the query.

In other words, I'm making Class::DBI's list accessors behave the same
way as Class::DBI's scalar accessors:  if the data is already stored in
the object, return it and don't hit the database.

My question is: could this feature be added to
Class::DBI::Relationship::HasMany, or should I make a new kind of
relationship?  Or is there a better way to accomplish this?

2) Discovering relationships

If I'm going to create a generalized query mechanism, how can I figure
out how the classes are related to each other?  For instance, how can I
know that the relationship between Music::Artist and Music::CD is
HasMany?


Any help appreciated!

Michael


The code:

    use strict;
    package Music::DBI;
    use base 'Class::DBI';
    Music::DBI->connection('dbi:Pg:dbname=testdb', 'testdb', 'xxxxxxxxxxx');

    package Music::Track;
    use base 'Music::DBI';
    Music::Track->table('track');
    Music::Track->columns(All => qw/trackid cd position title/);
    Music::Track->has_a(cd => 'Music::CD');

    package Music::CD;
    use base 'Music::DBI';
    use Class::DBI::Iterator;
    Music::CD->table('cd');
    Music::CD->columns(All => qw/cdid artist label title year/);
    Music::CD->has_many(retrieve_tracks => 'Music::Track');
    Music::CD->has_a(artist    => 'Music::Artist');
    Music::CD->has_a(label     => 'Music::Label');

    sub tracks {
        my $self = shift;
        if (exists $self->{'tracks'} and ref $self->{'tracks'} eq 'ARRAY') {
            return @{ $self->{'tracks'} } if wantarray;
            return Class::DBI::Iterator->new(
                'Music::Track',
                $self->{'tracks'},
            );
        }
        else {
            return $self->retrieve_tracks;
        }
    }

    package Music::Artist;
    use base 'Music::DBI';
    use Class::DBI::Iterator;
    Music::Artist->table('artist');
    Music::Artist->columns(All => qw/artistid name/);
    Music::Artist->has_many(retrieve_cds => 'Music::CD');

    Music::Artist->set_sql('catalog', qq{
         SELECT
                *,
                cd.title    AS cd_title,
                track.title AS track_title

         FROM   __TABLE(Music::Artist=artist)__,
                __TABLE(Music::CD=cd)__,
                __TABLE(Music::Track=track)__
         WHERE  __JOIN(artist cd track)__
         ORDER  BY artistid, cdid, track.position
    });

    sub cds {
        my $self = shift;
        if (exists $self->{'cds'} and ref $self->{'cds'} eq 'ARRAY') {
            return @{ $self->{'cds'} } if wantarray;
            return Class::DBI::Iterator->new(
                'Music::CD',
                $self->{'cds'},
            );
        }
        else {
            return $self->retrieve_cds;
        }
    }

    sub retrieve_catalog {
        my $class = shift;
        my $sth = $class->sql_catalog();
        my %data;
        $sth->execute;
        $sth->bind_columns(\(@data{ @{ $sth->{NAME_lc} } }));

        my @artists;

        my $artist_id;
        my $cd_id;

        my $cds;
        my $tracks;

        while ($sth->fetch) {
            if (!$artist_id or $artist_id != $data{'artistid'}) {

                $artist_id = $data{'artistid'};
                $cds       = [];

                push @artists, Music::Artist->construct({
                    artistid => $artist_id,
                    name     => $data{'name'},
                    cds      => $cds,
                });

            }

            if (!$cd_id or $cd_id != $data{'cdid'}) {

                $cd_id  = $data{'cdid'};
                $tracks = [];

                push @$cds, Music::CD->construct({
                    cdid     => $cd_id,
                    artist   => $artist_id,
                    title    => $data{'cd_title'},
                    year     => $data{'year'},
                    tracks   => $tracks,
                });
            }
            push @$tracks, Music::Track->construct({
                trackid  => $data{'trackid'},
                cd       => $cd_id,
                title    => $data{'track_title'},
                position => $data{'position'},
            });

        }

        return @artists if wantarray;
        return Class::DBI::Iterator->new(
            'Music::Artist',
            \@artists,
        );


    }


    package main;
    use strict;

    # List version
    my @artists = Music::Artist->retrieve_catalog();
    foreach my $artist (@artists) {
        print "Artist: ".$artist->name."\n";

        my @cds = $artist->cds;

        foreach my $cd (@cds) {
            print "\tAlbum: ".$cd->title." (".$cd->year.")\n";
            foreach my $track ($cd->tracks) {
                print "\t\t".$track->position." ".$track->title."\n";
            }
        }
    }

    # Iterator version
    my $artists = Music::Artist->retrieve_catalog();
    while (my $artist = $artists->next) {
        print "Artist: ".$artist->name."\n";

        my $cds = $artist->cds;

        while (my $cd = $cds->next) {
            print "\tAlbum: ".$cd->title." (".$cd->year.")\n";
            my $tracks = $cd->tracks;
            while (my $track = $tracks->next) {
                print "\t\t".$track->position." ".$track->title."\n";
            }
        }
    }








--
Michael Graham <magog@xxxxxxxx.xxx>