Re: Trouble making set_sql work...

[prev] [thread] [next] [Date index for 2004/07/13]

On Tue, Jul 13, 2004 at 10:28:05AM -0400, Andy Harrison wrote:
> 
> Maillog::Report::top_stats->show_top( $opt_top, $opt_top_limit );

set_sql doesn't work that way.

The set_sql will create a class method called 'sql_show_top' that will
return you a stamement handle for your SQL.  You need to use that
statement handle to fetch your results.  Class::DBI gives you shortcut
ways to write as little code as possible, e.g. :

  sub get_top_matches {
    my $class = shift;
    my $sth = $class->sql_show_top;
    $sth->execute($opt_top, $opt_top_limit);
    return $class->sth_to_objects($sth);
  }

This assumes that you want objects back (and that the first column of your
result set will be 'ids' for your objects).  If you just want raw
results in a list/hash/whatever then you can just get your $sth to use
one of the normal DBI methods.

You can see more about this in the section entitled 'Ima::DBI queries'
in the docs.

>     __PACKAGE__->set_sql( 'show_top' =>
>                                 qq { 
>                                     SELECT option_data, COUNT(*) 
>                                     FROM entry_options 
>                                     WHERE option = '?' 
>                                     GROUP BY option_data 
>                                     ORDER BY count DESC
>                                     LIMIT ?;
>                                    }
>                             );

You don't need the quoting of the first placeholder here or the
semi-colon at the end of the statement.  Also I'm pretty sure you
can't do 'LIMIT ?'.

Colm