It took me a few days (after following the cdbi mail-list for months) to wrap my head around how to do this, but after much gnashing of teeth, here's a nice snippet to add to your Class::DBI base classes. It irked me that there wasn't a builtin way to do this - at least not without loading every maching record into memory just to get a count. If I'm wrong on that count, please enlighten.

Code shamelessly adapted from Class::DBI::AbstractSearch - many thanks to Tatsuhiko Miyagawa for that simple but ever so useful module. My next challenge is to learn how to make a mixin/plugin for Class::DBI out of this. (Any suggestions?)

package MusicDBI;
use base 'Class::DBI';
use SQL::Abstract;

__PACKAGE__->set_sql(
  count_where => 'SELECT COUNT(*) FROM __TABLE__ %s'
);

sub count_where { 
    my $class = shift;
    my $where = (ref $_[0]) ? $_[0]          : { @_ };
    my $attr  = (ref $_[0]) ? $_[1]          : undef;
    my $order = ($attr)     ? delete($attr->{order_by}) : undef;

    $class->can('sql_count_where') or do {
        require Carp;
        Carp::croak("$class should set_sql(count_where => ...)");
    };
    my $sql = SQL::Abstract->new(%$attr);
    my($phrase, @bind) = $sql->where($where, $order);
    my $count = $class->sql_count_where($phrase)->select_val(@bind);

}

[download]

Replies are listed 'Best First'.
Re: Class::DBI count_where method
by dragonchild (Archbishop) on May 19, 2004 at 17:56 UTC
    Make your count_where() method in the Class::DBI namespace. Put the following in Class/DBI/Extension.pm 
    package Class::DBI;

sub count_where
{
}

1;

    [download]

    Of course, you should really provide this as a patch to Class::DBI ... (you did email this back to the author, right?)

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
[d/l]

      Not yet. I wasn't sure how to go about the __PACKAGE__->set_sql part as a mixin. I knew how to get count_where mixed in, but not the set_sql call. I guess I could use caller (?) to determine who was 'use'ing my module, and run the set_sql in that namespace... I think. Or am I missing something more basic?

      Well, I got it all working as a mixin. Now I'm working on adding the other aggregate functions (min/max/etc). Then I'll see if I can get that working, and submit to cdbi list for comment.

[reply]
[d/l]
[select]
      You probably want to check out Class::DBI::Plugin::CountSearch on CPAN, which does this "properly" - i.e. it has the same syntax as search() rather than requiring raw SQL.

      Tony

[reply]

Back to Cool Uses for Perl