[Dbix-class] Re: [CDBI] Aggregate functions in Sweet?

[Matt S Trout]

On Mon, Sep 19, 2005 at 10:09:11AM -0400, Michael Peters wrote:
> > 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.

Class::DBI::View doesn't do a bad job, and if you used that and either the
AbstractSearch and DeepAbstractSearch plugins you could get at least the
Abstract-isation that way.

I've been thinking about adding support in DBIx::Class for more than one
resultset against a table (bearing in mind that dbic orients itself around
resultset/row more than table/record) such that you could define e.g.

(mainview, default)

SELECT foo, bar, baz, quux FROM tbl WHERE ...

(by_foo)

SELECT foo, COUNT(baz) FROM tbl WHERE ... GROUP BY foo

(foo_bar_info)

SELECT DISTINCT(foo, bar) FROM tbl WHERE ...

etc. etc. with an API like

$class->view('by_foo')->resultset

rather than the primitive

$class->resultset

Suggestions and use-cases are very welcome; I've cross-posted this reply
to the dbix-class list so we can have a record of it over there as well.

-- 
     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/ +