Re: [CDBI] Aggregate functions in Sweet?

> Bill Moseley wrote:

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  
> about
> 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  
> kind
> of job. Any kind of reporting or aggregation is best done with  
> straight
> 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  
something.

best

will

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