Re: Trouble making set_sql work...

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

On Tue, Jul 13, 2004 at 01:32:59PM -0400, Andy Harrison wrote:
> On Tue, 13 Jul 2004 11:50:04 -0500, Stuart Johnston
> <sjohnston@xxxxxxxxxx.xxx> wrote:
> >
> > You other option would be to use %s, sprintf style substitution:
> > 
> > http://groups.kasei.com/mail/arc/cdbi-talk/2004-06/msg00068.html
> > 
> 
> I just went and did it like this and it works fine.
> 
>         my $sql = qq {
>             SELECT option_data, COUNT(*) 
>             FROM entry_options 
>             WHERE option = ? 
>             GROUP BY option_data 
>             ORDER BY count DESC
>             LIMIT $main::opt_top_limit
>         };

I wouldn't recommend doing it that way.

In the spirit of Class::DBI IMO it would be best to wrap a method around
the auto-generated sql_show_top.

Your SQL implies you want raw data and not objects.  If so I haven't
tried it but this should be mostly right :

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

  sub get_top_matches {
    my ($self, $option, $limit) = @_;
    my $sth = $self->sql_show_top($limit);
    $sth->execute($option);
    return $sth->fetchall_arrayref();
  }

  # in a nearby script :

  my $top_matches = Your::Class->get_top_matches($option, $limit);
  foreach my $result ( @$top_matches ) {
    my ($option_data, $count) = @$result;
    printf("%5d => %s\n", $count, $option_data);
  }

#--

Colm