On Jul 6, 2007, at 11:29 PM, Andrew Sterling Hanenkamp wrote:
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
Does this help?
my $choices = Doxory::Model::ChoiceCollection->new();
$choices->unlimit;
$choices->group_by( function => "SUBSTR(created,1,10)" );
my $date_name = $choices->column(
function => 'SUBSTR(created,1,10)',
column => 'date'
);
my $count_name = $choices->column( function => 'COUNT', column
=> 'id' );
$choices->order_by( function => 'SUBSTR(created,1,10)', order =>
'DESC' );
$choices->rows_per_page(10);
my $data;
while ( my $choice = $choices->next ) {
$data->{$choice->{'values'}{ 'date' } } = $choice-
>__value('id');
};
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 _______________________________________________ jifty-devel mailing list [email protected] http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
PGP.sig
Description: This is a digitally signed message part
_______________________________________________ jifty-devel mailing list [email protected] http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
