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
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
