Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?

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 perusing the Monastery: (3)
As of 2018-04-21 09:59 GMT
Find Nodes?
    Voting Booth?