Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery

DBIC and Aggregate MAX() Function

by Galdor (Acolyte)
on Dec 05, 2013 at 16:15 UTC ( #1065785=perlquestion: print w/replies, xml ) Need Help??
Galdor has asked for the wisdom of the Perl Monks concerning the following question:

Toying with ORM but it seems much harder than juts writing plain straight SQL (yes I like SQL - it is GOOD). Anyway given table:
create table stocks_daily ( exchange char(10) NOT NULL, symbol char(10) NOT NULL, date datetime NOT NULL, o real NOT NULL, h real NOT NULL, l real NOT NULL, c real NOT NULL, volume int NOT NULL, adjust real NOT NULL );
How do I return the MAX(c) (which is close price)? I have wasted lots of time on this and best I came up with is:
# get the max: my $high = $quote_model->search( { symbol => $symbol, date => { '>=' => $date}, }, {'select' => [ { max => 'c', } ], } )->single(); print "high: ", $high->c, "\n";
The syntax is way whacky - This is far more complex than:
select Max(c) from stocks_daily where symbol = '?' and date >= '?'
Anyway I always get error "Use of uninitialized value .."

Replies are listed 'Best First'.
Re: DBIC and Aggregate MAX() Function
by Your Mother (Bishop) on Dec 05, 2013 at 16:59 UTC

    For something like this, I'd use column results. Related reading, DBIx::Class::ResultSetColumn and DBIx::Class::Manual::Features.

    my $stocks_daily = $schema->resultset("stocks_daily"); # <-- update,ad +ded ";" my $rs = $stocks_daily->search({ symbol => $symbol, date => { ">=" => $date }}); my $c = $rs->get_column("c"); print $c->max || "nope!", $/;
      Marvelous! Thank-you Sir that does it...A little more words that I would have hopped - but I can again move forward - much appreciate the doc steer too ...

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1065785]
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (10)
As of 2018-06-18 10:13 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (109 votes). Check out past polls.