[Dbix-class] Why does this query not work?

[Rob Kinyon]

The as parameter doesn't set the AS keyword in SQL. You want:
     select => [ 'sym', \'COUNT(*) AS stc_count' ],
     as => [qw/ stc stc_count /],

Rob

On Wed, Mar 9, 2011 at 17:49, Dennis Daupert <ddaupert at gmail.com> wrote:
> I'm trying to code a simple query in DBIC. I get an error,
> and don't understand why. What am I doing wrong?
>
> QUERY:
> SELECT sym as stc, count(*) as stc_count
> FROM i1tickets
> GROUP BY stc
> ORDER BY stc_count DESC;
>
> CODE:
> $i1_tic_rs
>   = $schema_pg->resultset('I1tickets')->search(
>   {},
>   {
>     select => [
>       'sym',
>       { count => '*' },
>     ],
>     as => [qw/
>       stc
>       stc_count
>     /],
>     group_by => [qw/ stc /],
>     order_by => { -desc => [qw/ stc_count /] },
>   }
> );
>
> ROW:
> while ( $row = $i1_tic_rs->next ) {
>   $stc            = $row->stc;
>   $stc_count = $row->stc_count;
>   ....
>
> ERROR:
> DBIx::Class::ResultSet::next(): DBI Exception: DBD::Pg::st execute failed:
> ERROR:  column "stc_count" does not exist
> LINE 1: ...OUNT( * ) FROM i1tickets me GROUP BY stc ORDER BY stc_count ...
>                                                              ^ [for
> Statement "SELECT sym, COUNT( * ) FROM i1tickets me GROUP BY stc ORDER BY
> stc_count DESC"]
>
> I don't see the alias declarations in the error msg...
>
> BTW, is there a DBIC helper tool you can feed an sql
> query and have it spit out Perl code? I could use one ;-)
>
> Thanks!
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive:
> http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
>



-- 
Thanks,
Rob Kinyon