[CDBI] Aggregate functions in Sweet?
will at spanner.org
Mon Sep 19 17:16:23 BST 2005
On 19 Sep 2005, at 15:09, Michael Peters wrote:
> Bill Moseley wrote:
>> I've become dependent (read lazy) on CDBI::Sweet. But, now I need to
>> use an aggregate function.
> I don't know specifically about ::Sweet, but CDBI has always been
> mapping an object to a specific row. When using aggregate functions
> there is no corresponding row in the result set.
>> 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.
> Completely reasonable, but not CDBI is not the right tool for that
> of job. Any kind of reporting or aggregation is best done with
> SQL/DBI. I sometimes like to include this methods in my data classes
> that are CDBI based, but you might want to separate them.
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 think the rule of thumb is that you can carry on using CDBI objects
as long as the data returned by your queries still corresponds to a
set of rows from one database table. You can achieve a lot by using
TEMP columns to hold the results of summary operations *on other
tables* (as in Bill's example), provided you return rows from this
table and you don't mind that the summary values will be out of date
soon. Often I find it's just a case of choosing the right class to
make the query from.
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
More information about the ClassDBI