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

Jesse Vincent jesse at bestpractical.com
Tue Jul 10 00:29:34 EDT 2007


Generally this seems reasonable. I definitely agree that we need  
custom Record and Collection classes, either as alternate base  
classes or as subclasses that neuter the behaviour we need to stop.

-j


On Jul 8, 2007, at 11:57 AM, Andrew Sterling Hanenkamp wrote:

> Well, just going off of the current API, I think it could be extended
> to handle the most general cases pretty easily. For example, my
> particular problem could be solved by generalizing count() and
> count_all().
>
> # count becomes something similar to this...
> sub count {
>    my $self = shift;
>    return $self->apply_aggregate_function(function => 'COUNT', alias
> => 'main', column => 'id');
> }
>
> # count_all becomes something similar to this...
> sub count_all {
>    my $self = shift;
>    return $self->apply_aggregate_function(function => 'COUNT', alias
> => 'main', column => 'id', unlimit => 1);
> }
>
> A little more finesse is needed, but I think that would be the gist.
> Then, you could call any aggregate function that way:
>
> my $total = $self->apply_aggregate_function(function => 'SUM', column
> => 'amount');
> my $average_cost = $self->apply_aggregate_function(function =>
> 'AVERAGE', column => 'amount');
> my $start_date = $self->apply_aggregate_function(function => 'MIN',
> column => 'event_date');
> # etc.
>
> I'm not sure I like "apply_aggregate_function", but that's the best I
> could think of off the top of my head. I think it would be safe to
> memoize these results similar to how the count() subroutines do and we
> could provide sum(), average(), min() and others as shortcuts too.
>
> For the harder problem where you want to create grouped sub-totals or
> something for a report, I think something else is necessary. For
> example, if you wanted to print a report for:
>
> SELECT category, SUM(amount) AS total
> FROM ledger_entries
> WHERE entry_date >= ? AND entry_date < ?
> GROUP BY category
>
> You could have the equivalent using the API I suggest above, but it
> would require multiple queries instead of this one. The other
> difficulty we run into here (and above as well, really) is individual
> rows that the current_user may not have access to will be included in
> the aggregate. I can't think of any feasible way around that problem,
> so I'd just put a warning in the docs stating that if you need the
> security constraints applied, do your owning aggregation in Perl by
> iterating over the rows yourself.
>
> Here's one idea for this API:
>
> # Create a new collection, but use an anonymous record class
> my $collection = App::Model::LedgerEntryCollection->new(
>    record_class => 'Jifty::Record::Anonymous'
> );
> my $category_name = $collection->column( column => 'category' );
> my $total_name = $collection->column( function => 'SUM', column =>  
> 'amount' );
> $collection->limit( column => 'entry_date', operation => '>=', value
> => $start_date );
> $collection->limit( column => 'entry_date', operation => '<', value =>
> $end_date );
> $collection->group_by( column => 'category' );
> while (my $report_row = $collection->next) {
>    print $collection->value($category_name), ': ',
> $collection->value($total_name), "\n";
> }
>
> That's my first idea for that problem. If we can somehow signal to the
> collection class that we're not instantiating records with IDs, but
> some kind of special record for reporting, this would work it into the
> API as is. I'm not settled about this latter idea, but something like
> it could do the job.
>
> I've also thought about the idea of adding a class like
> Jifty::DBI::Report, which would be similar to collection, but not
> really bound to records, but it seems like it would too closely
> overlap Collection and lead to confusion.
>
> On 7/8/07, Jesse Vincent <jesse at bestpractical.com> wrote:
>>
>> On Jul 7, 2007, at 2:35 PM, Andrew Sterling Hanenkamp wrote:
>>
>> > That's what I thought, which brings me back to: how do I get the
>> > result of an aggregate function like MAX or SUM when I'm not
>> > interested in any individual records--as in, in a similar way to  
>> how
>> > we can grab the COUNT of rows?
>> >
>> > I can just perform the query using SQL on the DBI for now, but JDBI
>> > could probably add a shortcut for this pretty easily if one doesn't
>> > already exist.
>>
>> Better shortcuts are needed. Our current "method" involves trolling
>> the returned datastructure.  Whyat would a good API look like?
>>
>> _______________________________________________
>> jifty-devel mailing list
>> jifty-devel at lists.jifty.org
>> http://lists.jifty.org/cgi-bin/mailman/listinfo/jifty-devel
>>
>>
>>
> _______________________________________________
> 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/20070709/8c1cc6c8/PGP.pgp


More information about the jifty-devel mailing list