[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__
  %s
  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();
OR
@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
land.

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