according to the cookbook, something like
select => [ 'sym', { count => '*', -as => 'stc_count' } ],
as => [ qw/ stc stc_count / ],
would work.
http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures,
starting at "Note that the as attribute *has absolutely nothing to do* with
the SQL syntax SELECT foo AS bar"
Justin
On Wed, Mar 9, 2011 at 6:00 PM, Rob Kinyon <[email protected]> wrote:
> 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 <[email protected]> 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/[email protected]
> >
>
>
>
> --
> Thanks,
> Rob Kinyon
>
> _______________________________________________
> 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/[email protected]
>
_______________________________________________
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/[email protected]
