DBICの集約関数
http://www.ornithopter.jp/archives/2006/11/dbixclassdbic_d_1.html
ケースによってはもっとスマートに扱えます。
こんな感じのデータがあったとします。
> select * from item; +----+------------+------+-------+---------------------+---------------------+ | id | rid | name | price | created_on | timestamp | +----+------------+------+-------+---------------------+---------------------+ | 1 | tkVjn4E2cQ | pen | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 2 | DN2mdgPzIv | note | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 3 | UBCzoZKGES | clip | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | +----+------------+------+-------+---------------------+---------------------+ 3 rows in set (0.00 sec) > select * from order_log; +----+------------+---------+-------+---------------------+---------------------+ | id | rid | item_id | price | created_on | timestamp | +----+------------+---------+-------+---------------------+---------------------+ | 1 | UFmfRbLqhb | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 2 | uQpIOMYUAI | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 3 | BygAzHR1Yy | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 4 | yjRWqnha7M | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 5 | NS2Ozw4KxQ | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 6 | bf0Q5eUi4J | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 7 | VWM9BqVpVt | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 8 | oeAvPeoJC7 | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 9 | pDWrqE76EM | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 10 | hB0OC6Gr0J | 1 | 500 | 2007-02-17 13:55:06 | 2007-02-17 13:55:06 | | 11 | bRCs0jNTkc | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 12 | 8kmAxd6WMX | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 13 | unKf9Ev55M | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 14 | 4S0klustrh | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 15 | ERuaDrgUEq | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 16 | InxEbt1WY3 | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 17 | OZeIim9NNn | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 18 | 7Rm21QIKty | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 19 | CivnxL9d9X | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 20 | 28WbYvoOc0 | 2 | 600 | 2007-02-17 13:55:39 | 2007-02-17 13:55:39 | | 21 | o2LngQVTnE | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 22 | 8JZxq7XcvC | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 23 | ya1G5atTlt | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 24 | U7DaqFgbDs | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 25 | TB4tnp0fua | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 26 | avhNCX65Yq | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 27 | tthExVsWcp | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 28 | BeB8NNjlaj | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 29 | nLhTCnh50X | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | | 30 | ScxXW7SeM3 | 3 | 100 | 2007-02-17 13:55:58 | 2007-02-17 13:55:58 | +----+------------+---------+-------+---------------------+---------------------+
ここでpenの売り上げ合計を確認したい場合、
my $rs = $schema->resultset('OrderLog')->search( { 'item_id.name' => 'pen', }, { join => 'item_id', } ); warn $rs->get_column('me.price')->sum;
これだけで十分。
もちろん、カウントとったりしたい場合とかは複数回クエリーが投げられることになり、
１クエリで全ては処理できませんが。
実行されるSQL
Executing : SELECT SUM( me.price ) FROM order_log me JOIN item item_id ON ( item_id.id = me.item_id ) WHERE ( item_id.name = ? ) ORDER BY me.id DESC : 'pen' -->Query Time: 0.001746. -->ALL Query Time: 0.001746 --------------------------------------------------------------------------------- 5000 at /...
DBICは集合関数を結構賢くさばいてくれます。
他にもmin、maxとかも。
実際はminもmaxもsumもfuncメソッドへのラッパーなだけなので、
他にも色々できるます。
funcメソッドはこんなの。
sub func { my $self = shift; my $function = shift; my ($row) = $self->{_parent_resultset}->search(undef, {select => {$function => $self->{_column}}, as => [$self->{_column}]})->cursor->next; return $row; }
