Class::DBI::Sweet用のプラグインいろいろ
Class::DBI::Sweet用のプラグインいろいろ
ある程度汎用的に使えそうなので置いときます。
CDBI::Sweet用のプラグインはCDBI::Sweet::Topping::Fooでええんじゃろか。。。
SUM( column ) するプラグイン
package Class::DBI::Sweet::Topping::Sum; =head1 SYNOPSYS package MyData::CD; use base qw/Class::DBI::Sweet/; use Class::DBI::Sweet::Topping::Sum; package main; my $sum = MyData::CD->sum( 'price' ); # SELECT SUM(price) =cut use strict; sub import { my $class = shift; my $pkg = caller(0); $pkg->set_sql( Join_Retrieve_Sum => <<__SQL__ ); SELECT SUM( %s ) FROM %s WHERE %s __SQL__ no strict 'refs'; *{"$pkg\::sum"} = \&_sum; } sub _sum { my $proto = shift; my $class = ref($proto) || $proto; my $sum_column = shift; my ( $criteria, $attributes ) = $class->_search_args(@_); # make sure we take copy of $attribues since it can be reused my $sum_attr = { %{$attributes} }; # no need for LIMIT/OFFSET and ORDER BY in SUM($sum_column) delete @{$sum_attr}{qw( rows offset order_by )}; my ( $sql_parts, $classes, $columns, $values ) = $proto->_search( $criteria, $sum_attr ); my $sql_method = 'sql_' . ($attributes->{sql_method} || 'Join_Retrieve'); $sql_method .= '_Sum'; my $sth = $class->$sql_method( "me.$sum_column", @{%$sql_parts}{qw/ from where /} ); $class->_bind_param( $sth, $columns ); return $sth->select_val(@$values); } 1; __END__
SUM()だけじゃなくて、集約関数をuse時に指定して自動生成するのが良さそう。
COUNT(*) 付きでJOINするプラグイン
package Class::DBI::Sweet::WithCount; use strict; =head1 SYNOPSYS package MyData::Artist; use base qw/Class::DBI::Sweet/; use Class::DBI::Sweet::Topping::WithCount; __PACKAGE__->has_many( cds => 'MyData::CD' ); package main; # SELECT artist.id, COUNT(*) AS counting FROM ... GROUP BY artist.id my @artists = MyData::Artist->retrieve_all_with_count( 'cds' ); =cut sub import { my $class = shift; my $pkg = caller(0); $pkg->columns( TEMP => qw/counting/ ); $pkg->set_sql( Join_Retrieve_WithCount => <<'__SQL__' ); SELECT __ESSENTIAL(me)__, COUNT(*) counting FROM %s WHERE %s GROUP BY __ESSENTIAL(me)__ %s %s __SQL__ no strict 'refs'; *{"$pkg\::retrieve_all_with_count"} = sub { my $proto = shift; my $class = ref $proto || $proto; my $join = shift; $class->search( { $join => {'!=', undef}, }, { sql_method => 'Join_Retrieve_WithCount', statement_order => [qw/ from where order_by limit /], } ); }; } 1; __END__
retrieve_allだけじゃなくてsearchにも応用できそうですが。
あと、COUNT(*)じゃなくてSUM(column)に対応するとかね。