[Catalyst] Problem to use set_sql CDBI

[Perrin Harkins]

Omid Houshyar wrote:
> Hi
> 
> I have problem to get the result of set_sql like the one below:
> 
> ---------------------------------------------------------------------
> package panel::Model::CDBI::SvpnUsers;
> 
> use strict;
> 
> __PACKAGE__->set_sql( allUsersCounts => qq{
>                     SELECT count(*) as cnt
>                     FROM   svpn_users
>                     });
> --------------------------------------------------------------------
> package panel::Controller::user;
> 
> use strict;
> use warnings;
> use base 'Catalyst::Controller';
> 
> sub allUsersCounts : Local {
>    my ( $self, $c ) = @_;
>    my @usersCounts = ();
>      my $svpnUsers = "panel::Model::CDBI::SvpnUsers";
>    @usersCounts = $svpnUsers->search_allUsersCounts();
>    foreach (@usersCounts) {
>        $c->log->info($_->cnt);
>    }
>   #    return @usersCounts;
> }

That doesn't make sense.  Your query returns a single number and is not 
related to any object.  Why are you using it as if it were a property of 
an object?

Just call the SQL directly:

my $sth = panel::Model::CDBI::SvpnUsers->sql_allUserCounts();
$sth->execute();
my $count = $sth->select_val();

You could also try the Class::DBI::Plugin::CountSearch module.  That's 
what I use.

- Perrin