dcvr69 has asked for the wisdom of the Perl Monks concerning the following question:

Last Update:Thanks for the feedback, and the ++ votes. Once I get a couple of minor things figured out, I'll be subitting Class::DBI::Plugin::Aggregates to CPAN - a first for me.

Ok, I've gotten all my issues with Class::DBI count_where method worked out and am extending and polishing it up for submission to CPAN. Two questions.

  1. Class::DBI::AbstractSearch seems useful enough to have on CPAN. Would a module as described above (a set of methods for aggregate queries a la Class::DBI::AbstractSearch) be of worth/useful to others?

    Update:Ok - figured out handling columns for the agg function to operate on - thanks to CDBI::search.

    Ok, the other aggregate functions may take more work that I thought... grrr. I'm still pursuing it - just not as close as I thought - didn't think about the need for column names, etc, for the other aggregates. MIN(*) isn't very meaningful, is it?

    Update 2:Looks like I should just include a factory for agg functions that aren't SQL standards, since I'm not sure which agg. functions are unique across DB vendors - advice regarding such is welcome.

  2. Looks like Class::DBI::Plugin::Aggregates is where it belongs.

    What would be a good name for it? It's not really a search, since it only returns single values - not objects, so it wouldn't really fit as an add-on to AbstractSearch - IMHO. I should probably hunt down the CDBI bits on plug-ins/extensions, and see if there's a namespace already. Anyone know off the top of their head?

thanks

dcvr69

Replies are listed 'Best First'.
Re: Class::DBI::* module proposal
by diotalevi (Canon) on May 20, 2004 at 00:37 UTC
    This sounds like you're implementing a bit of a data cube (think OLAP). You may want to look and see what there is for calculating cubes on data via Class::DBI. If you were going to do this it'd help if you were aware of the space you're working with.
[reply]

      You're right - I'll go through the CDBI namespace (again - haven't done so recently... past month or so) and see if there's anything out there already... maybe I'll be lucky and there is. :)

      Update:

      Class::DBI::ConceptSearch, mentions aggregates in it's description, but isn't really about min/max/...

      Class::DBI::Extension (which another PM mentioned to me) almost does it - get_count_from_sql and get_range are nice, but don't allow for searches like AbstractSearch does - which is ironic, since they're from the same author.

      Class::DBI::Plugin::CountSearch is almost a winner - but isn't quite as flexible as AbstractSearch on the where clause logic. Had I seen this first though, I may have just lived with the limitation. It also only does COUNT(*), not any of the others (min/max/...). This (and CDBI::search) will be good reference for adding in column support on the min/max/... methods.

      If there is anyting that has all the functionality I'm looking for, it's not in the Class::DBI namespace.

[reply]
Re: Class::DBI::* module proposal
by perrin (Chancellor) on May 20, 2004 at 00:42 UTC
    Have you looked at the helpers for doing this in the latest release of Class::DBI? Check the "Single Value SELECTs" section.
[reply]

      Yes. Single Value selects are useful, but don't allow for parametric queries like AbstractSearch.

      I was hoping there was already a builtin way to allow for COUNT(*) ... WHERE, but I couldn't see a way to do it natively, and a generic fashion for my whole class tree.

      The ability to do other aggregates was just an afterthought when I started considering posting to CPAN.

      If I've missed something in the base CDBI class, please, enlighten me. (I've already promised to peruse the rest of the CDBI namespace for something that does this - though I'm pretty sure I looked before embarking on this).

      Update: Re: single value selects: I'd have to build the SQL myself for each variation of the where clause - which is where AbstractSearch came in, and the idea of a mixin/plugin to add things like count_where, min_where, etc, to my base class. I'm most definitely not trying to reinvent the wheel.

[reply]

Back to Seekers of Perl Wisdom