[jifty-devel] Queries using GROUP BY and aggregate functions

Andrew Sterling Hanenkamp sterling at hanenkamp.com
Fri Jul 6 23:29:56 EDT 2007


I was trying to figure out how to perform this query in JDBI without
success, so I've fallen back on a manual query on the DBI handle until
I can get back to it:

SELECT linked_row, MAX(some_number)
FROM foos
WHERE linked_row = ?
GROUP BY linked_row

I tried to do this by writing this code out:

my $collection = App::Model::FooCollection->new;
my $max_column_name = $collection->column( column => 'some_number',
function => 'MAX' );
$collection->group_by( column => 'linked_row' );
$collection->limit( column => 'linked_row', value => 5 );
my $max_row = $collection->next;
my $max = $max_row ? $max_row->column($max_column_name) : 0;

That yields:

FATAL - Can't call method "check_read_rights" on an undefined value at
/Library/Perl/5.8.6/Jifty/Collection.pm line 71.

When I trace it out, Jifty::DBI::Collection is croaking because it's
attempting to load the record matching the "id" of the row, as best as
I can figure. However, I'm not interested in and do not want a query
returning a record.

I suppose what I really want is something more like:

my $collection = App::Model::FooCollection->new;
$collection->limit( column => 'linked_row', value => 5 );
$collection->group_by( column => 'linked_row' );
my @max = $collection->aggregate( function => 'MAX', column => 'some_number' );
my $max = @max ? $max[0] : 0;

Here the aggregate subroutine would apply MAX(some_number) and return
all the values found for the query. In this specific case, it ought to
return just a single value.

AFAIK I haven't run across anything like this before, but I need
something like this for a couple pieces of a toy project I'm working
on. Thoughts?

Cheers,
Andrew


More information about the jifty-devel mailing list