my %query_builders = (
   a => \&a_query_builder,
   b => \&b_query_builder,
);

$query_builders{$query_name}->();
[download]

Thanks for your reply!

but dispatch table seems not to shorten code. I still have to write a_query,b_query,c_query... respectly. In my brain, a_query_builder, b_query_builder ... and x_query_builder sub are all much similar like:

sub a_query_builder{
my ($table_name, $display_cols_ref, $where_cols_ref) = @_;

if ($where_cols_ref->{'year_min'})     { push @clauses, "Year >= $wher
+e_cols_ref->{'year_min'}" }
if ($where_cols_ref->{'year_max'})     { push @clauses, "Year <= $wher
+e_cols_ref->{'year_max'}" }

# ...
$clause = join(" AND ", @clauses);
$display_cols = join(",", @$display_cols_ref);
$sth = $dbh->prepare("SELECT $display_cols FROM $table_name WHERE $cla
+use");
}
[download]

I'm not sure the above treatment correct or not, but I hope to create a general code to handle all kinds of query statement building. Is creating a query class a good idea?





---

---

I am trying to improve my English skills, if you see a mistake please feel free to reply or /msg me a correction

but dispatch table seems not to shorten code.

The only code that could be shortened was hidden as "...".

What the dispatch tables provide is an elegant way of breaking down your unmanageably long function into smaller blocks.

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.

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

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.

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