Re: [CDBI] Aggregate functions in Sweet?

> I don't think that's quite right. This kind of reporting *can* be

On Mon, Sep 19, 2005 at 05:16:23PM +0100, William Ross wrote:
> I don't think that's quite right. This kind of reporting *can* be  
> done with cdbi. It can also be done much more efficiently with  
> elaborate SQL queries. The difficulty is in combining them to get  
> some ease and some efficiency.

I'm generating a lot of html tables that are paged (using Sweet's
page() method) and have click-able column headings for sorting. Most
of the tables require joins.  CDBI::Sweet can do most of the joins,
but I have been creating Postgresql views instead -- mostly because it
makes it so easy to do the sorting and not having to worry about
table relationships in my Catalyst controllers (to setup the joins).

I mean, it's really easy to create a new screen.  I create the view in
Postgresql, create about a three line CDBI class for the view, and
then this in the Catalyst controller:

    sub customers : Local {
        my ($class, $c) = @_;
        my $options = {
            template    => 'customers.tt',
            class       => 'DB::View::Customers',
            criteria    => {
                status      => { '>=', 65 },
            },
        };
        $c->forward('/paged_list', [ $options ]);
    }

and the paged_list private action deals with setting up the sort and
number of rows/page, page num, etc.  What little work there is left is
in the template.

Unfortunately, I have not been able to setup a view with an aggregate
that works with the above.  For example:

    CREATE VIEW count_regions
        AS
            SELECT  region.name AS region_name,
                    region.id   AS region_id,
                    region.sort_order AS region_sort,
                    count(class.id) AS class_count

             FROM   region, location, class

            WHERE   class.location  = location.id AND
                    region.id       = location.region

         GROUP BY   region_name, region_id, region_sort;

Then I cannot do:

    select * from region_counts where class.class_time > now();

because then I no longer have my aggregate grouping.

> I know nothing about Sweet, so I can't answer the original question,  
> but the code all looks normal apart from that             alarming  
> nested query. Can't you get the number of rows from $itr->count? Or  
> for that matter from the second $sth. But I expect I've missed  
> something.

What is done in Sweet when doing paged results is first a count(*) to
get total rows and then do the real select using LIMIT and OFFSET.

But yes, with the aggregate function it would be better to just do the
query and grab the results needed for the page that is to be
displayed.

        -- 
        Bill Moseley
moseley@xxxx.xxx

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