Doing a LEFT OUTER join with DBIx::Class

I have recently been using DBIx::Class instead of the more popular Class::DBI. It has many advantages over Class::DBI that I won't go into here, but if you haven't used it yet you should definitely check it out.

One thing I found the other day is how to setup a special LEFT OUTER join query. If you have a situation where you need to do a LEFT OUTER join on your data, but only say in one particular script. Or maybe a one off report that you won't be keeping around. You could go ahead and put in this relationship in your main model class, but for a one off that is a bit of overkill.

What I hadn't thought about, was you can define those relationships from outside the MyModelClass.pm file itself. Take for example a simple Artist -> CD relationship, where you want all artists even if they don't have any CDs:

use ExampleSchema;

ExampleSchema::Artist->has_many('left_outer_albums' =>  
                       'ExampleSchema::Cd', 'artist_id',  
                       { join_type => 'LEFT_OUTER' } );

my $schema = ExampleSchema->connect('dbi:Pg:dbname=outer', '', '');

my $rs = $schema->resultset('Artist')->search(

    undef,

);
while( my $artist = $rs->next ) {
    print "Name: " . $artist->name . "\n";
    print "Albums: \n";
    foreach my $album ( $artist->left_outer_albums ) {
        print "\t" . $album->title . "\n";
    }
}

The nice thing about this is that this special left_outer_artists is defined and used in the one off and doesn't have to polute your main ExampleSchema::Artist relationships that might confuse someone. It may not be the best practice, but it is something to consider.