[Dbix-class] Looking for the DBIC way.

[Mike Earley]

I'm playing around with the example Artist/Cd/Tracks SQLite database
in order to learn DBIC.  I've defined a left outer join relationship
between the artist and cd tables like this:

     package MyDatabase::Main::Artist.pm;

     __PACKAGE__->has_many( 'cds_left_outer',
                            'MyDatabase::Main::Cd',
                            { 'foreign.artist' => 'self.artistid' },
                            { 'join_type'      => 'LEFT OUTER'    } );

When I trace the query I get this (reformated) SQL statement:

     SELECT me.artistid, me.name,
            cds_left_outer.cdid, cds_left_outer.artist, cds_left_outer.title
     FROM   artist me
     LEFT OUTER JOIN cd cds_left_outer ON ( cds_left_outer.artist = me.artistid )
     ORDER BY cds_left_outer.artist

Running this query through SQLite I get these correct results:

     2|Pink Floyd|||
     1|Michael Jackson|1|1|Thriller
     1|Michael Jackson|2|1|Bad
     3|Eminem|3|3|The Marshall Mathers LP

Running this Perl code gives me exactly the same results:

     my $artist_rs = $dbh->resultset( 'Artist' )->search( undef, { prefetch => 'cds_left_outer' } );
     while ( my $artist = $artist_rs->next )
     {
         my $cd_rs = $artist->cds_left_outer;
         if ( $cd_rs->count == 0 )
         {
             print join( '|', $artist->artistid, $artist->name, '', '', '' ), "\n";
         }
         else
         {
             while ( my $cd = $cd_rs->next )
             {
                 print join( '|', $artist->artistid, $artist->name, $cd->cdid, $cd->artist, $cd->title ), "\n";
             }
         }
     }

I also created similar DBI code:

     my $dbh = DBI->connect( 'dbi:SQLite:example.db' );
     my $sth = $dbh->prepare( "
         SELECT me.artistid, me.name,
                cds_left_outer.cdid, cds_left_outer.artist, cds_left_outer.title
         FROM   artist me
         LEFT OUTER JOIN cd cds_left_outer ON ( cds_left_outer.artist = me.artistid )
         ORDER BY cds_left_outer.artist
     " );
     $sth->execute() || die $!;
     while ( my $row = $sth->fetchrow_arrayref() )
     {
         print join( '|', map { $_ || '' } @{$row} ), "\n";
     }

In the DBI version I did not need to program nested loops to access the child
data.  I'm wondering if I'm not seeing some basic DBIC accessor trick? Is
there a different, more DBIC, way to code this or does the has_many nature of
the relationship force me to use nested loops to access the data?

Thanks for any insight or example code.

Mike