Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

DBIC and Aggregate MAX() Function

by Galdor (Sexton)
on Dec 05, 2013 at 16:15 UTC ( [id://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 (Archbishop) 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?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1065785]
Front-paged by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (3)
As of 2024-03-28 13:57 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found