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]/
