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* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> 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/[email protected]/ > > > > ------------------------------------------------------------------------ > > _______________________________________________ > 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]/ _______________________________________________ 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]/
