<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
code
{mso-style-priority:99;
font-family:"Courier New";}
pre
{mso-style-priority:99;
mso-style-link:"HTML Preformatted Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:10.0pt;
font-family:"Courier New";
color:black;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;
color:black;}
span.HTMLPreformattedChar
{mso-style-name:"HTML Preformatted Char";
mso-style-priority:99;
mso-style-link:"HTML Preformatted";
font-family:Consolas;
color:black;}
span.EmailStyle22
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body bgcolor="white" lang="FR-CH" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Thanks for the help. I tryied<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> $schema->resultset('Abo')->search_rs(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> {
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> 'me.noabt' => $value_aref->[0],
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> },<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> { select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> join => ['abojrnabt'],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> group_by => ['me.noabt'],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> having => { 'count_abo' => { '>' => 1 } },<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> });<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Which failed with<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">*** DBIx::Class::Storage::DBI::_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-DbTools/lib/Gtk2/Ex/DbLinker/DbcDataManager.pm line 403<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">But this<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> $schema->resultset('Abo')->search_rs(<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> {
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> 'me.noabt' => $value_aref->[0],
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> },<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> { select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> join => ['abojrnabt'],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> group_by => ['me.noabt'],<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> having => \[ 'count(abojrnabt.noabt) > ?', 1 ] ,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> });<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">works.<o:p></o:p></span></p>
<p class="MsoNormal"><a name="_MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></a></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Thanks<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">François<o:p></o:p></span></span></p>
<p class="MsoNormal"><span style="mso-bookmark:_MailEndCompose"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></span></p>
<span style="mso-bookmark:_MailEndCompose"></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:gjungwirth@sipwise.com]
<br>
<b>Sent:</b> 13 January 2017 17:12<br>
<b>To:</b> dbix-class@lists.scsys.co.uk<br>
<b>Subject:</b> Re: [Dbix-class] Count + having<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>One think to have in mind would be from the documentation:<o:p></o:p></p>
<p>"The <a href="https://metacpan.org/pod/DBIx::Class::ResultSet#as">"as"</a> attribute has
<b>nothing to do</b> with the SQL-side identifier aliasing AS."<o:p></o:p></p>
<p>instead you should write<o:p></o:p></p>
<div>
<pre><code> select => ['me.noabt', { count</code> <code>=> 'abojrnabt.noabt', -as</code> <code>=> 'count_abo'</code> <code>} ]<o:p></o:p></code></pre>
<pre><code><o:p> </o:p></code></pre>
<pre><code>(untested)<o:p></o:p></code></pre>
</div>
<p> <o:p></o:p></p>
<div>
<p class="MsoNormal">On 2017-01-13 15:35, RAPPAZ Francois wrote:<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<pre>I'm trying to use count and having <o:p></o:p></pre>
<pre>I have <o:p></o:p></pre>
<pre>Abo a table of rows describing orders (primary key: noabt)<o:p></o:p></pre>
<pre>abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>I would like to know if a specific order has 2 or more corresponding rows in Jrnabt<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>I'm trying<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>$schema->resultset('Abo')->search_rs(<o:p></o:p></pre>
<pre> {<o:p></o:p></pre>
<pre> 'me.noabt' => $value,<o:p></o:p></pre>
<pre> },<o:p></o:p></pre>
<pre> { select => ['me.noabt', {count => 'abojrnabt.noabt'}],<o:p></o:p></pre>
<pre> as => [qw/noabt count_abo/],<o:p></o:p></pre>
<pre> join => ['abojrnabt'],<o:p></o:p></pre>
<pre> group_by => ['noabt'],<o:p></o:p></pre>
<pre> having => [ { 'count_abo' => { '>' => 1 } } ],<o:p></o:p></pre>
<pre> },<o:p></o:p></pre>
<pre>);<o:p></o:p></pre>
<pre>But this fails with<o:p></o:p></pre>
<pre>*** DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: <o:p></o:p></pre>
<pre> Unknown column 'count_abo' in 'having clause' [for Statement <o:p></o:p></pre>
<pre> "SELECT me.noabt FROM abo me WHERE ( me.noabt = ? ) GROUP BY noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>Thanks for any suggestion<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>François<o:p></o:p></pre>
<pre><o:p> </o:p></pre>
<pre>_______________________________________________<o:p></o:p></pre>
<pre>List: <a href="http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class">http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class</a><o:p></o:p></pre>
<pre>IRC: irc.perl.org#dbix-class<o:p></o:p></pre>
<pre>SVN: <a href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><o:p></o:p></pre>
<pre>Searchable Archive: <a href="http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk">http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk</a><o:p></o:p></pre>
</blockquote>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>