[Dbix-class] Left join with nulls
Sarah Berry
berry.sarah at gmail.com
Wed Jun 28 16:38:09 CEST 2006
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/[email protected]/
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.rawmode.org/pipermail/dbix-class/attachments/20060628/a9aaf588/attachment.htm
More information about the Dbix-class
mailing list