[CDBI] Aggregate functions in Sweet?

[prev] [thread] [next] [Date index for 2005/09/19]

I've become dependent (read lazy) on CDBI::Sweet.  But, now I need to
use an aggregate function.

For example, I have a table "class" which is a class taught at some
"location", and locations have_a "region" like "North" or "South".

    my $region = $class->location->region;
    print $region->name;

So, now I want to have a list of regions, and show how many classes
are available in each region.  A reasonable need.

In my Region class I Have:

    __PACKAGE__->columns( TEMP => 'class_count' );

    my $sql_counts = <<EOF;
        SELECT __ESSENTIAL(me)__, COUNT(class.id) AS class_count

        FROM   __TABLE=me__, class, location

        WHERE  class.location = location.id AND
               me.id          = location.region AND
               class_time     > now()

      GROUP BY __ESSENTIAL(me)__
    EOF


    __PACKAGE__->set_sql( class_counts => $sql_counts );

    __PACKAGE__->set_sql( 
        rows_class_counts => "SELECT COUNT(*) FROM ( $sql_counts ) AS T",
    );

Then in a parent class:

    sub count_classes {
        my ( $class, $options ) = @_;

        $options ||= {};

        # First do the count(*)
        my $pager = Data::Page->new(
            $class->sql_rows_class_counts->select_val,
            ( $options->{rows} || 10 ),
            ( $options->{page} || 1 ),
        );

        my $sth = $class->sql_class_counts;
        $sth->execute;
        my $itr = $class->sth_to_objects( $sth );
        return ( $pager, $itr );
    }

Of course, I plan on being able to pass in $criteria and use
SQL::Abstract to generate my additional WHERE (the check on
"class_time" won't be hard-coded), and add LIMIT and OFFSET so this
works like Sweet's page().

And then it's starting to feel like I'm duplicating much of what Sweet
already does.

Is there plan for anything like this in Sweet?

Is there a better way to do the above?


Thanks,



        -- 
        Bill Moseley
moseley@xxxx.xxx


_______________________________________________
ClassDBI mailing list
ClassDBI@xxxxx.xxxxxxxxxxxxxxxx.xxx
http://lists.digitalcraftsmen.net/mailman/listinfo/classdbi