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

[Jesse Vincent]

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
> jifty-devel at lists.jifty.org
> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>

-------------- next part --------------
A non-text attachment was scrubbed...
Name: PGP.sig
Type: application/pgp-signature
Size: 186 bytes
Desc: This is a digitally signed message part
Url : http://lists.bestpractical.com/pipermail/jifty-devel/attachments/20070706/3692fbff/PGP.pgp