2006-03-28

Class::DBI::Sweet::Pie - Class::DBI::Sweetに集約関数を追加する

　これでClass::DBIで集約関数が使いたくなっても困らないぞ、と。

　あとはドキュメントを整備したりテストコードを書けばいいかな……。

　YAPCが終わる頃までにはCPANに登録したいと思うんだけど*1、正しいモジュールの作り方をすっかり忘れてしまってますよ。h2xsとか使うんだっけ？

SYNOPSYS

  package MyData::CD;
  use base qw/Class::DBI::Sweet/;
  __PACKAGE__->has_a( artist => 'MyData::Artist' );
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('sum');
  __PACKAGE__->mk_aggregate_function( max => 'maximum');

  package MyData::Artist;
  use base qw/Class::DBI::Sweet/;
  __PACKAGE__->has_many( cds => 'MyData::CD' );
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('min');

  package main;

  # 一番高価なCDの価格
  $max_price = MyData::CD->maximum( 'price' );

  # fooさんのCDの合計金額
  $total_price = MyData::CD->sum( 'price', 'artist.name' => 'foo' );

  # fooさんのCDで一番安価なものの価格
  ($artist) = MyData::Artist->search( name => 'foo' );
  $min_price = $artist->min('cds.price');

ソースコード

package Class::DBI::Sweet::Pie;
use strict;
use vars qw/$VERSION @EXPORT @EXPORT_OK/;
$VERSION = '0.02';

use Exporter::Lite;
@EXPORT = qw/mk_aggregate_function/;

sub mk_aggregate_function {
    my $class = shift;
    my ($aggregate_func, $alias) = @_;
    $alias ||= $aggregate_func;

    $class->set_sql( "Join_Retrieve_$aggregate_func" => <<__SQL__ );
  SELECT $aggregate_func( %s )
  FROM   %s
  WHERE  %s
__SQL__

    no strict 'refs';
    *{"$class\::$alias"} = sub {
	my $self = shift;
	my $class = ref($self) || $self;
	my $aggregate_column = shift;
	my ($criteria, $attributes) = $class->_search_args(@_);

	if ($aggregate_column !~ /^(\w+)\./) {
	    $aggregate_column = "me.$aggregate_column";
	}
	elsif (ref $self) {
	    my $table = $1;
	    $attributes->{prefetch} = [ $table ];
	    foreach my $pcol ($self->primary_column) {
	        $criteria->{ $pcol } = $self->$pcol;
	    }
	}

	# make sure we take copy of $attribues since it can be reused
	my $agfunc_attr = { %{$attributes} };

	# no need for LIMIT/OFFSET and ORDER BY in AGGREGATE_FUNC()
	delete @{$agfunc_attr}{qw( rows offset order_by )};

	my ($sql_parts, $classes, $columns, $values) = $class->_search( $criteria, $agfunc_attr );

	my $sql_method = 'sql_' . ($attributes->{sql_method} || 'Join_Retrieve') . "_$aggregate_func";
	my $sth = $class->$sql_method( $aggregate_column, @{%$sql_parts}{qw/ from where /} );

	$class->_bind_param( $sth, $columns );
	return $sth->select_val(@$values);
    }
}

1;
__END__
=head1 NAME

Class::DBI::Sweet::Pie - create Aggregate Function

=head1 SYNOPSYS

  package MyData::CD;
  use base qw/Class::DBI::Sweet/;
  __PACKAGE__->has_a( artist => 'MyData::Artist' );
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('sum');
  __PACKAGE__->mk_aggregate_function( max => 'maximum');

  package MyData::Artist;
  use base qw/Class::DBI::Sweet/;
  __PACKAGE__->has_many( cds => 'MyData::CD' );
  use Class::DBI::Sweet::Pie;
  __PACKAGE__->mk_aggregate_function('min');

  package main;

  # 一番高価なCDの価格
  $max_price = MyData::CD->maximum( 'price' );

  # fooさんのCDの合計金額
  $total_price = MyData::CD->sum( 'price', 'artist.name' => 'foo' );

  # fooさんのCDで一番安価なものの価格
  $artist = MyData::Artist->search( name => 'foo' );
  $min_price = $artist->min('cds.price');

=head1 DESCRIPTION

　Class::DBI::Sweetを使ったテーブルで集約関数を簡単に使えるようになります。

=cut

Class::DBI::Plugin::AggregateFunctionでCOUNT(*)する

　以下で動くはず。たぶん。

　類似のモジュールはClass::DBI::Plugin::CountSearchとかClass::DBI::Search::Countとかいっぱいあるので、あんまり意味はありませんが。

　COUNT(DISTINCT column)とかするときは使えるかもしれない。

package MyData::CD;
use base qw/Class::DBI/;
use Class::DBI::Plugin::AggregateFunction;
__PACKAGE__->mk_aggregate_function( count => 'counting' );

package main;
$count = MyData::CD->counting('*', artist => $artist);
$artist_count MyData::CD->counting('distinct artist');

*1：Class::DBI::Plugin::AggregateFunctionも一緒に。