[Dbix-class] Left join with nulls

[Alan Hicks]

Hi Sarah,

Thanks for the pointer.

I've been using Catalyst and DBIx for some months now and very happy 
too, alas there are a couple of although not complex queries are three 
or more relations deep with additional filter and I haven't been able to 
find a way of using DBIx from any of the examples in the documentation 
or the list.

I guess time to refactor, that usually works.

Thanks,
Alan

Sarah Berry wrote:
> Hi Alan,
> 
> I'm assuming you've already looked through the documentation? There's a 
> great cookbook and example page at
> http://www.annocpan.org/~DANIELTWC/DBIx-Class-0.06999_01/lib/DBIx/Class/Manual.pod 
> <http://www.annocpan.org/~DANIELTWC/DBIx-Class-0.06999_01/lib/DBIx/Class/Manual.pod>
> 
> I recently changed our web app from using SQL to using DBIx. The easiest 
> way I found to make the switch was to start with a simple DBIx "query" 
> and work my way up to the complex query I really wanted, rather than 
> trying to convert an existing complex SQL query to DBIx all at once. Too 
> much can go wrong!
> 
> I can't give you a whole lot of specific pointers on your particular 
> query, being a beginner myself, but I hope this points you in the right 
> direction.
> 
> Sarah
> 
> On 6/27/06, *Alan Hicks* <ahicks at p-o.co.uk <mailto:ahicks at p-o.co.uk>> wrote:
> 
>     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 <http://c.id>,
>              c.name <http://c.name>,
>              sc.id <http://sc.id>,
>              sc.name <http://sc.name>,
>              CASE
>                      WHEN cc.id <http://cc.id> IS NOT NULL THEN 1
>                      ELSE 0
>                      END AS cat_cat,
>              CASE
>                      WHEN csc.id <http://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 <http://c.id> = sc.category
>     LEFT JOIN catalogue_cat cc
>     ON      cc.cat = c.id <http://c.id>
>     AND     cc.id <http://cc.id> = 2
>     LEFT JOIN catalogue_subcat csc
>     ON      csc.subcat = sc.id <http://sc.id>
>     AND     csc.id <http://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
> 
>     _______________________________________________
>     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/dbix-class@lists.rawmode.org/
> 
> 
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> 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/dbix-class@lists.rawmode.org/