Re: [CDBI] Aggregate functions in Sweet?

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

On Sun, Sep 18, 2005 at 10:15:10PM -0700, Bill Moseley wrote:
> I've become dependent (read lazy) on CDBI::Sweet.  But, now I need to
> use an aggregate function.

I hacked on Sweet last night so I can use aggregates.

Sweet uses an sql fragment (created with set_sql()) for the selects.

    __PACKAGE__->set_sql( Join_Count => <<'SQL' );
    SELECT COUNT(*)
    FROM   %s
    WHERE  %s
    SQL

    __PACKAGE__->set_sql( Join_Retrieve => <<'SQL' );
    SELECT __ESSENTIAL(me)__%s
    FROM   %s
    WHERE  %s
    SQL

The problem is that the %s used in the WHERE also includes ORDER BY
and LIMIT/OFFSET.  So I split those out so they can be specified
separately.  That's the one thing that breaks existing code.  If you
currently use a custom Join_Retrieve in your classes then ORDER and
LIMIT will not be included.  Is anyone using a custom Join_Retrieve?

Here's an example: show a list of artists that have CDs with a year
less than 2000 and the the count of those cds.

In package MyDB::Artist

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

    __PACKAGE__->set_sql( 'count_by_cd', <<'');
        SELECT      __ESSENTIAL(me)__, COUNT(cds.cdid) as cd_count
        FROM        %s                  -- ("limit")
        WHERE       %s                  -- ("where")
        GROUP BY    __ESSENTIAL(me)__
        %s %s                           -- ("limit" and "order_by")


Then in your application code:

    my ($pager, $iterator) = MyDB::Artist->page(
        {
            'cds.year'  => { '<', 2000 },
        },
        {
            sql_method          => 'count_by_cd',
            statement_order     => [qw/ from where limit order_by / ],
            disable_sql_paging  => 1,
            order_by            => 'cd_count desc',
            rows                => 10,
            page                => 1,
        } );

One problem is that Sweet has to figure out the joins by the criteria
-- so to show all artists regardless of year use something like:

    'cds.title' => { '!=' => undef }



        -- 
        Bill Moseley
moseley@xxxx.xxx


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