Re: [CDBI] Aggregate functions in Sweet?

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

On Sun, Sep 18, 2005 at 10:15:10PM -0700, Bill Moseley wrote:
> 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?

Assuming you always want this additional field for this table, you'd probably
be able to do it by replacing the sql_Join_Count; have a dig on the inside
of Sweet.

People have been asking to be able to apply Sweet features to set_sqls but
I'm not a big fan of set_sql myself so I've not given it huge amounts of
thought and never come up with an implementation I consider acceptable;
pointers (and of course patches :) would be very welcome.

        -- 
             Matt S Trout       Specialists in perl consulting, web development, and
  Technical Director    UNIX/Linux systems architecture and automation. Mail
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +

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