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

Andrew Sterling Hanenkamp sterling at hanenkamp.com
Sun Jul 8 14:57:21 EDT 2007


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
>
>
>


More information about the jifty-devel mailing list