[Dbix-class] Left join with nulls

[Sarah Berry]

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

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> 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,
>          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
>
> _______________________________________________
> 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/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060628/a9aaf588/attachment.htm