<div dir="ltr">I don't remember using an RDMS that allowed the column alias in the HAVING clause. <div><br></div><div>It sure would be nice though.</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Jan 17, 2017 at 3:06 AM, RAPPAZ Francois <span dir="ltr"><<a href="mailto:francois.rappaz@unifr.ch" target="_blank">francois.rappaz@unifr.ch</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="white" lang="FR-CH" link="blue" vlink="purple">
<div class="m_2001800381766994830WordSection1">
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks for the help. I tryied<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> $schema->resultset('Abo')-><wbr>search_rs(<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> {
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> 'me.noabt' => $value_aref->[0],
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> },<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> { select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> join => ['abojrnabt'],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> group_by => ['me.noabt'],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> having => { 'count_abo' => { '>' => 1 } },<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> });<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Which failed with<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">*** DBIx::Class::Storage::DBI::_<wbr>dbh_execute(): DBI Exception: DBD::mysql::st execute failed: Unknown column 'count_abo'
in 'having clause' [for Statement "SELECT me.noabt FROM abo me WHERE ( me.noabt = ? ) GROUP BY me.noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ../mod//hg_Gtk2-Ex-DbLinker-<wbr>DbTools/lib/Gtk2/Ex/DbLinker/<wbr>DbcDataManager.pm line 403<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">But this<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> $schema->resultset('Abo')-><wbr>search_rs(<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> {
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> 'me.noabt' => $value_aref->[0],
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> },<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> { select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> join => ['abojrnabt'],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> group_by => ['me.noabt'],<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> having => \[ 'count(abojrnabt.noabt) > ?', 1 ] ,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> });<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">works.<u></u><u></u></span></p>
<p class="MsoNormal"><a name="m_2001800381766994830__MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></a></p>
<p class="MsoNormal"><span><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks<u></u><u></u></span></span></p>
<p class="MsoNormal"><span><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></span></p>
<p class="MsoNormal"><span><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">François<u></u><u></u></span></span></p>
<p class="MsoNormal"><span><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></span></p>
<span></span>
<div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:windowtext"> Gerhard Jungwirth [mailto:<a href="mailto:gjungwirth@sipwise.com" target="_blank">gjungwirth@sipwise.com</a><wbr>]
<br>
<b>Sent:</b> 13 January 2017 17:12<br>
<b>To:</b> <a href="mailto:dbix-class@lists.scsys.co.uk" target="_blank">dbix-class@lists.scsys.co.uk</a><br>
<b>Subject:</b> Re: [Dbix-class] Count + having<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<p>One think to have in mind would be from the documentation:<u></u><u></u></p>
<p>"The <a href="https://metacpan.org/pod/DBIx::Class::ResultSet#as" target="_blank">"as"</a> attribute has
<b>nothing to do</b> with the SQL-side identifier aliasing AS."<u></u><u></u></p>
<p>instead you should write<u></u><u></u></p>
<div>
<pre><code> select => ['me.noabt', { count</code> <code>=> 'abojrnabt.noabt', -as</code> <code>=> 'count_abo'</code> <code>} ]<u></u><u></u></code></pre>
<pre><code><u></u> <u></u></code></pre>
<pre><code>(untested)<u></u><u></u></code></pre>
</div>
<p> <u></u><u></u></p>
<div>
<p class="MsoNormal">On 2017-01-13 15:35, RAPPAZ Francois wrote:<u></u><u></u></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<pre>I'm trying to use count and having <u></u><u></u></pre>
<pre>I have <u></u><u></u></pre>
<pre>Abo a table of rows describing orders (primary key: noabt)<u></u><u></u></pre>
<pre>abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>I would like to know if a specific order has 2 or more corresponding rows in Jrnabt<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>I'm trying<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>$schema->resultset('Abo')-><wbr>search_rs(<u></u><u></u></pre>
<pre> {<u></u><u></u></pre>
<pre> 'me.noabt' => $value,<u></u><u></u></pre>
<pre> },<u></u><u></u></pre>
<pre> { select => ['me.noabt', {count => 'abojrnabt.noabt'}],<u></u><u></u></pre>
<pre> as => [qw/noabt count_abo/],<u></u><u></u></pre>
<pre> join => ['abojrnabt'],<u></u><u></u></pre>
<pre> group_by => ['noabt'],<u></u><u></u></pre>
<pre> having => [ { 'count_abo' => { '>' => 1 } } ],<u></u><u></u></pre>
<pre> },<u></u><u></u></pre>
<pre>);<u></u><u></u></pre>
<pre>But this fails with<u></u><u></u></pre>
<pre>*** DBIx::Class::Storage::DBI::_<wbr>dbh_execute(): DBI Exception: DBD::mysql::st execute failed: <u></u><u></u></pre>
<pre> Unknown column 'count_abo' in 'having clause' [for Statement <u></u><u></u></pre>
<pre> "SELECT me.noabt FROM abo me WHERE ( me.noabt = ? ) GROUP BY noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>Thanks for any suggestion<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>François<u></u><u></u></pre>
<pre><u></u> <u></u></pre>
<pre>______________________________<wbr>_________________<u></u><u></u></pre>
<pre>List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" target="_blank">http://lists.scsys.co.uk/cgi-<wbr>bin/mailman/listinfo/dbix-<wbr>class</a><u></u><u></u></pre>
<pre>IRC: <a href="http://irc.perl.org#dbix-class" target="_blank">irc.perl.org#dbix-class</a><u></u><u></u></pre>
<pre>SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/<wbr>repos/bast/DBIx-Class/</a><u></u><u></u></pre>
<pre>Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" target="_blank">http://www.grokbase.com/group/<wbr>dbix-class@lists.scsys.co.uk</a><u></u><u></u></pre>
</blockquote>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>______________________________<wbr>_________________<br>
List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class" rel="noreferrer" target="_blank">http://lists.scsys.co.uk/cgi-<wbr>bin/mailman/listinfo/dbix-<wbr>class</a><br>
IRC: <a href="http://irc.perl.org#dbix-class" rel="noreferrer" target="_blank">irc.perl.org#dbix-class</a><br>
SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" rel="noreferrer" target="_blank">http://dev.catalyst.perl.org/<wbr>repos/bast/DBIx-Class/</a><br>
Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk" rel="noreferrer" target="_blank">http://www.grokbase.com/group/<wbr>dbix-class@lists.scsys.co.uk</a><br></blockquote></div><br><br clear="all"><div><br></div>-- <br><div class="gmail_signature" data-smartmail="gmail_signature"><div dir="ltr"><div><div dir="ltr"><div><div dir="ltr"><div>Len Jaffe - Information Technology Smoke Jumper - <a href="mailto:lenjaffe@jaffesystems.com" target="_blank">lenjaffe@jaffesystems.com</a> </div><div>614-404-4214 <a href="https://www.twitter.com/lenJaffe" target="_blank">@LenJaffe</a> <a href="http://www.lenjaffe.com/" target="_blank">www.lenjaffe.com</a><br></div><div>Host of <a href="http://www.meetup.com/techlifecolumbus/" target="_blank">Code Jam Columbus</a> - <a href="https://www.twitter.com/CodeJamCMH" target="_blank">@CodeJamCMH</a></div><div><div>Curator of <a href="http://www.lenjaffe.com/AdventPlanet/" target="_blank">Advent Planet</a> - An Aggregation of Online Advent Calendars.<div><br></div></div></div></div></div></div></div></div></div>
</div>