Re: How treat query elegantly and more?

DBIx::Class really is the way to go here (if your DB design is sane; if your design is poor then DBIC will have an impedance mismatch). Pseudo code-

my %query = ( some => "default relevant to controller" );

my @stuff = $c->request->param("some_checkbox_list");

$query{foo} = { in => \@stuff } if @stuff;

$query{something} = "tacos" if $something_taco_related;

# a dozen more similar style, disparate variable loads.

my %where = ( some => "dynamic",  list => "of conditions" );

my $rs = $c->model("DBIC::SomeTable")
    ->search(\%query, { join => [ blah, blah ], %where });
$c->stash( some_stuff_rs => $rs );
[download]

DBIC (and by extension, SQL::Abstract) shines when dynamic data drives your queries as well as when you need ready access through relationships.

Sidebar not relevant to this particular thread but a previous one: it's somewhat poor etiquette to ask the same question in three places at once (e.g., Cat list, PM, Stack Overflow). You should generally do one at a time and wait a day to see what answers you get or at least mention and link to the cross-posted question.

Comment on Re: How treat query elegantly and more? Download Code

Replies are listed 'Best First'.
Re^2: How treat query elegantly and more? by xiaoyafeng (Deacon) on Jul 01, 2010 at 06:20 UTC
Please point out, if I'm wrong, but I've read DBIx::Class doc several times, it mention join(left join, inner join etc.) very few. Like below database design, if DBI, I could simply use join clause to retrieve various of names. how could do that by DBIx::Class? `sale_detailed<id store_id person_id merchant_id datetime .........> store < store_id store_name ........> person< person_id person_name .....> merchant< merchant_id merchant_name .....> .... ....` [download] I am trying to improve my English skills, if you see a mistake please feel free to reply or /msg me a correction	[reply] [d/l]
Re^3: How treat query elegantly and more? by Your Mother (Archbishop) on Jul 01, 2010 at 17:14 UTC
You are correct that there is a somewhat steep ramp to using DBIC. :( You have to get your schema first. Today is a bad day for me to do a short tutorial on it or I would. The things you can read to help get set-up are– DBIx::Class::Schema::Loader (which can generate your schema for you automatically if your foreign/primary keys are correct) and the dbicadmin utility DBIx::Class::Manual::Intro DBIx::Class::Manual::Example DBIx::Class::Manual::Cookbook DBIx::Class::Manual::FAQ DBIx::Class::Manual::Joining It's probably tempting to jump ahead to the join stuff but you'll have a much easier time if you get the basics around schema generation and connection down first. DBIC is difficult to pick up. It took me quite a while but it's really been worth it. Rose::DB is a highly regarded alternative which has better performance but, I'd say, less flexibility and community support. Also worth at least knowing about are Fey and KiokuDB... Viva la Perl5.	[reply]
Re^4: How treat query elegantly and more? by metaperl (Curate) on Jul 02, 2010 at 19:08 UTC
The things you can read to help get set-up are– Those theoretical documents are nice. However, I felt there was a clear need for completely working code samples on a small but realistic database. thus DBIx::Cookbook was born and primarily with examples for DBIx::Class. The first section of the manual of DBIx::Class::Manual::Cookbook (Searching) is what I took on and completed. The remaining sections remain to be done... contributors welcome! The mantra of every experienced web application developer is the same: thou shalt separate business logic from display. Ironically, almost all template engines allow violation of this separation principle, which is the very impetus for HTML template engine development. -- Terence Parr, "Enforcing Strict Model View Separation in Template Engines"	[reply]
Re^2: How treat query elegantly and more? by xiaoyafeng (Deacon) on Jul 01, 2010 at 05:51 UTC
Thanks for your reminder, I realized my impolite when last question. I promise I would not make similar mistake again. ;) I am trying to improve my English skills, if you see a mistake please feel free to reply or /msg me a correction	[reply]