FAQ
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!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110309/3e4975ac/attachment.htm

Search Discussions

  • Rob Kinyon at Mar 9, 2011 at 11:00 pm
    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 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
  • Justin Hunter at Mar 10, 2011 at 12:06 am
    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 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 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

    _______________________________________________
    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
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110309/ba81dc2b/attachment.htm
  • Dennis Daupert at Mar 10, 2011 at 2:22 am
    Thanks guys, both forms you provided do the job. I appreciate the info on
    'as.' Helps me understand.

    /dennis
    -------------- next part --------------
    An HTML attachment was scrubbed...
    URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20110309/405c2f3a/attachment.htm

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbix-class @
categoriesperl, catalyst
postedMar 9, '11 at 10:49p
activeMar 10, '11 at 2:22a
posts4
users3
websitedbix-class.org
irc#dbix-class

People

Translate

site design / logo © 2021 Grokbase