[Dbix-class] Left join with nulls

[Alan Hicks]

Hi,

I've just started to use DBIx with Catalyst and think it's truly great 
and simplifies much of what I need to do.

Alas I have a query that I can't get my head around so any advice is 
appreciated.  I'm trying to get a list of available categories and sub 
categories for a catalogue I am looking after and have previously got 
this using the following sql:-

SELECT  c.id,
         c.name,
         sc.id,
         sc.name,
         CASE
                 WHEN cc.id IS NOT NULL THEN 1
                 ELSE 0
                 END AS cat_cat,
         CASE
                 WHEN csc.id IS NOT NULL THEN 1
                 ELSE 0
                 END AS cat_sub_cat
FROM    category c
INNER JOIN sub_category sc
ON      c.id = sc.category
LEFT JOIN catalogue_cat cc
ON      cc.cat = c.id
AND     cc.id = 2
LEFT JOIN catalogue_subcat csc
ON      csc.subcat = sc.id
AND     csc.id = 2
ORDER BY c.sort_order, sc.sort_order

My tables are as follows:-

package MyApp::Model::DBSchema::Category;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('category');
__PACKAGE__->add_columns(qw/id sort_order folder name description/);

package MyApp::Model::DBSchema::CatalogueCat;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('catalogue_cat');
__PACKAGE__->add_columns(qw/id cat/);

package MyApp::Model::DBSchema::CatalogueSubCat;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/PK::Auto Core/);
__PACKAGE__->table('catalogue_subcat');
__PACKAGE__->add_columns(qw/id subcat/);

Thanks in advance,
Alan