[Dbix-class] Create and execute query

[Will Hawes]

Maurice Height wrote:
> Hi
> 
> I am attempting to learn DBIx::Class (version 0.05001) via some simple test
> data in a MySQL database.
> My setup is MySQL (v5.0.18) on WinXP, Activestate Perl (v 5.8.7 build 815).
> 
> I have a db table 'history' setup as:
> 
> -------------------------------
> package DB_Schema::History;
> use base qw/DBIx::Class/;
> __PACKAGE__->load_components(qw/Core/);
> __PACKAGE__->table('history');
> __PACKAGE__->add_columns(qw/code day open high low close volume/);
> __PACKAGE__->set_primary_key(qw/code day/);
> 1;
> -------------------------------
> 
> I have got some very simple queries to work OK on this table
> but I would like to do something more complicated like:
> 
> 
> SELECT code, MIN(volume*low) AS min_traded, AVG(volume*low) AS av_traded,
>              AVG(close) AS av_close, AVG(volume) AS av_volume
> FROM   history
> WHERE  day >= '2006-02-01' AND  day <= '2006-02-07' AND low >= 1.00
>        AND volume > 100000
> GROUP BY code
> 
> How do I write a search command for the SQL above?
> 
> my $schema = DB_Schema->connect( $dsn, $user, $pwd, $attrs );
> my $rs     = $schema->resultset('History')->search({
>                  select = ????
>              );
> 
> Maurice
> 

The Cookbook should explain that nicely, the relevant links being:

http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures
http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Grouping_results