according to the cookbook, something like<div><br></div><div><font class="Apple-style-span" face="'courier new', monospace">select => [ 'sym', <span class="Apple-style-span" style="white-space: pre; font-size: medium; ">{ count => '*', -as => 'stc_count' } ],</span></font></div>
<div><font class="Apple-style-span" size="3" face="'courier new', monospace"><span class="Apple-style-span" style="white-space: pre; ">as => [ qw/ stc stc_count / ],</span></font></div><div><font class="Apple-style-span" face="monospace" size="3"><span class="Apple-style-span" style="white-space: pre;"><br>
</span></font></div><div><font class="Apple-style-span" face="tahoma, sans-serif"><span class="Apple-style-span" style="white-space: pre; ">would work.</span></font></div><div><font class="Apple-style-span" face="monospace" size="3"><span class="Apple-style-span" style="white-space: pre;"><br>
</span></font></div><div><font class="Apple-style-span" face="monospace" size="3"><span class="Apple-style-span" style="white-space: pre;"><a href="http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures">http://search.cpan.org/dist/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod#Using_database_functions_or_stored_procedures</a>, starting at "<span class="Apple-style-span" style="font-family: arial, sans-serif; white-space: normal; ">Note that the <code>as</code> attribute <b>has absolutely nothing to do</b> with the SQL syntax <code>SELECT foo AS bar"</code></span></span></font></div>
<div><font class="Apple-style-span" face="monospace" size="3"><br></font></div><div><font class="Apple-style-span" face="monospace" size="3"><span class="Apple-style-span" style="font-family: arial, sans-serif; "><code></code></span>Justin<br>
</font><br><div class="gmail_quote">On Wed, Mar 9, 2011 at 6:00 PM, Rob Kinyon <span dir="ltr"><<a href="mailto:rob.kinyon@gmail.com">rob.kinyon@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
The as parameter doesn't set the AS keyword in SQL. You want:<br>
select => [ 'sym', \'COUNT(*) AS stc_count' ],<br>
as => [qw/ stc stc_count /],<br>
<br>
Rob<br>
<br>
On Wed, Mar 9, 2011 at 17:49, Dennis Daupert <<a href="mailto:ddaupert@gmail.com">ddaupert@gmail.com</a>> wrote:<br>
> I'm trying to code a simple query in DBIC. I get an error,<br>
> and don't understand why. What am I doing wrong?<br>
><br>
> QUERY:<br>
> SELECT sym as stc, count(*) as stc_count<br>
> FROM i1tickets<br>
> GROUP BY stc<br>
> ORDER BY stc_count DESC;<br>
><br>
> CODE:<br>
> $i1_tic_rs<br>
> = $schema_pg->resultset('I1tickets')->search(<br>
> {},<br>
> {<br>
> select => [<br>
> 'sym',<br>
> { count => '*' },<br>
> ],<br>
> as => [qw/<br>
> stc<br>
> stc_count<br>
> /],<br>
> group_by => [qw/ stc /],<br>
> order_by => { -desc => [qw/ stc_count /] },<br>
> }<br>
> );<br>
><br>
> ROW:<br>
> while ( $row = $i1_tic_rs->next ) {<br>
> $stc = $row->stc;<br>
> $stc_count = $row->stc_count;<br>
> ....<br>
><br>
> ERROR:<br>
> DBIx::Class::ResultSet::next(): DBI Exception: DBD::Pg::st execute failed:<br>
> ERROR: column "stc_count" does not exist<br>
> LINE 1: ...OUNT( * ) FROM i1tickets me GROUP BY stc ORDER BY stc_count ...<br>
> ^ [for<br>
> Statement "SELECT sym, COUNT( * ) FROM i1tickets me GROUP BY stc ORDER BY<br>
> stc_count DESC"]<br>
><br>
> I don't see the alias declarations in the error msg...<br>
><br>
> BTW, is there a DBIC helper tool you can feed an sql<br>
> query and have it spit out Perl code? I could use one ;-)<br>
><br>
> Thanks!<br>
><br>
> _______________________________________________<br>
> List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
> IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
> SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
> Searchable Archive:<br>
> <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
><br>
<br>
<br>
<br>
--<br>
Thanks,<br>
Rob Kinyon<br>
<br>
_______________________________________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><br>
</blockquote></div><br></div>