[CDBI] Group by and AbstractSearch

Brett Sanger swiftone at swiftone.org
Fri Oct 21 14:53:27 BST 2005

I'm trying to aggregate some data with a group by statement, and I want
to use the AbstractSearch Plugin to allow me to optionally restrict it.

(Failing that, I want to use SQL::Abstract)

The Wiki gave me hints towards the following code:

(In my class)

__PACKAGE__->set_sql( summarize =>
  "select fips, provider_type, count(id) as Count
  from __TABLE__
  group by fips, provider_type"

sub area_summary {
  my $self = shift;
  my $sql = SQL::Abstract->new();
  my ($where, @bind) = $sql->where(@_);
  return $self->sql_summarize($where)->select_val(@bind);

(in my calling code):

@results = CDBI::Class->area_summary();
@results = CDBI::Class->area_summary(
		fips => [@foo],   #Or any other SQL::Abstract statement

Which, unfortunately, doesn't seem to work:  I get only one field back, not
three, and I get many empty results sets.  Also, I'm a little bothered because
I'm no longer certain what I'm coding, and I don't want to go to cargo cult

I'm assuming sql_summarize() returns an statement object for the statement, and
select_val on that is the equivilant of execute()....but I'd like for someone
to confirm.  And if we can get this code working, that'd be even better.

Thanks in advance!
SwiftOne  /  Brett Sanger
swiftone at swiftone.org   

More information about the ClassDBI mailing list