浅倉卓司＠blog風味？

Class::DBI::Sweet用のプラグインいろいろ

Perl, CDBI | 17:42 |

　ある程度汎用的に使えそうなので置いときます。

　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)に対応するとかね。

ツイートする