<html><head></head><body><div style="color:#000; background-color:#fff; font-family:HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif;font-size:14px"><div dir="ltr" id="yui_3_16_0_ym19_1_1473041155785_17571"><span id="yui_3_16_0_ym19_1_1473041155785_17651">hello all,</span></div><div id="yui_3_16_0_ym19_1_1473041155785_17761" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17651"><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_17762" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17651">thanks for response. sorry if i sent email directly to you. (i think i just did a replay all)</span></div><div id="yui_3_16_0_ym19_1_1473041155785_17763" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17651"><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_17603" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_18101">i tried that and many other way, but i am going crazy that it just wont work????</span></div><div id="yui_3_16_0_ym19_1_1473041155785_17793" dir="ltr"><span><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_18135" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_18890">SQL I 'hope' to implement is:</span><code id="yui_3_16_0_ym19_1_1473041155785_18622"><span id="yui_3_16_0_ym19_1_1473041155785_18623"></span></code><br><code id="yui_3_16_0_ym19_1_1473041155785_18622"><span id="yui_3_16_0_ym19_1_1473041155785_18623"></span></code></div><pre id="yui_3_16_0_ym19_1_1473041155785_18621" style=""><code id="yui_3_16_0_ym19_1_1473041155785_18622"><span id="yui_3_16_0_ym19_1_1473041155785_18623">SELECT *</span><span id="yui_3_16_0_ym19_1_1473041155785_18624"></span><span id="yui_3_16_0_ym19_1_1473041155785_18625">
FROM itemlist
</span><span id="yui_3_16_0_ym19_1_1473041155785_18631">WHERE </span><span id="yui_3_16_0_ym19_1_1473041155785_18632"></span><span id="yui_3_16_0_ym19_1_1473041155785_18633">t_id IN </span><span id="yui_3_16_0_ym19_1_1473041155785_18634">(</span><span id="yui_3_16_0_ym19_1_1473041155785_18635">1</span><span id="yui_3_16_0_ym19_1_1473041155785_18636">,</span><span id="yui_3_16_0_ym19_1_1473041155785_18637">2,3</span><span id="yui_3_16_0_ym19_1_1473041155785_18638"></span><span id="yui_3_16_0_ym19_1_1473041155785_18639"></span><span id="yui_3_16_0_ym19_1_1473041155785_18640">)</span><span id="yui_3_16_0_ym19_1_1473041155785_18641">
GROUP BY i</span><span id="yui_3_16_0_ym19_1_1473041155785_18643">_id
HAVING COUNT</span><span id="yui_3_16_0_ym19_1_1473041155785_18644">(</span><span id="yui_3_16_0_ym19_1_1473041155785_18645">1</span><span id="yui_3_16_0_ym19_1_1473041155785_18646">)</span><span id="yui_3_16_0_ym19_1_1473041155785_18647"> </span><span id="yui_3_16_0_ym19_1_1473041155785_18648">=</span><span id="yui_3_16_0_ym19_1_1473041155785_18649"> </span><span id="yui_3_16_0_ym19_1_1473041155785_18650">3</span></code></pre><div id="yui_3_16_0_ym19_1_1473041155785_18841" dir="ltr"><span><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_18403" dir="ltr"><span>CODE:................</span></div><div id="yui_3_16_0_ym19_1_1473041155785_18404" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_18513">$tStr is 55 (has only one element)<br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_18315" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_19319">$tCount = 1 (no. of items above.)<br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_18405" dir="ltr"><span><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_18258" dir="ltr">> my $obj_rs = $schema->resultset('itemList')->serach(<br id="yui_3_16_0_ym19_1_1473041155785_18208" clear="none">> { t_id => { -in => [ $tStr ] }},<br id="yui_3_16_0_ym19_1_1473041155785_18209" clear="none">> {<br id="yui_3_16_0_ym19_1_1473041155785_18210" clear="none">> group_by => [ qw(i_id) ],<br id="yui_3_16_0_ym19_1_1473041155785_18211" clear="none">> <span id="yui_3_16_0_ym19_1_1473041155785_18265"> having => { 'count 1' => $tCount },</span> <br id="yui_3_16_0_ym19_1_1473041155785_18212" clear="none">> }<br id="yui_3_16_0_ym19_1_1473041155785_18213" clear="none">> );</div><div id="yui_3_16_0_ym19_1_1473041155785_18262" dir="ltr"><br></div><br><div id="yui_3_16_0_ym19_1_1473041155785_17808" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_18087">i get this error:</span></div><div id="yui_3_16_0_ym19_1_1473041155785_17809" dir="ltr"><span><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_17797" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17796"> [cgi:error] [pid 772] [client ....] AH01215: DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1 = '1'' at line 1 [for Statement "SELECT me.t_id, me.i_id FROM itemList me WHERE ( t_id IN ( ? ) ) GROUP BY o_id HAVING count 1 = ?" with ParamValues: 0='55', 1=1] at /var/ww...<br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_17999" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17796"><br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_21444" dir="ltr"><br></div><div id="yui_3_16_0_ym19_1_1473041155785_21436" dir="ltr">which i guess converts to:</div><div id="yui_3_16_0_ym19_1_1473041155785_21437" dir="ltr"><br id="yui_3_16_0_ym19_1_1473041155785_21438"></div><div id="yui_3_16_0_ym19_1_1473041155785_21439" dir="ltr">SELECT me.i_id, me.t_id FROM itemList me <br id="yui_3_16_0_ym19_1_1473041155785_21440">WHERE ( t_id IN ( 55 ) ) <br id="yui_3_16_0_ym19_1_1473041155785_21441">GROUP BY i_id <br id="yui_3_16_0_ym19_1_1473041155785_21442">HAVING count 1 = 1</div><div id="yui_3_16_0_ym19_1_1473041155785_21445" dir="ltr"><br id="yui_3_16_0_ym19_1_1473041155785_21443"></div><div id="yui_3_16_0_ym19_1_1473041155785_18000" dir="ltr"><span id="yui_3_16_0_ym19_1_1473041155785_17796"> what is wrong in this SQL??..... (I am assuming that by now DBIx syntax is good)<br></span></div><div id="yui_3_16_0_ym19_1_1473041155785_17890"><br></div><div id="yui_3_16_0_ym19_1_1473041155785_17928"><br></div><div id="yui_3_16_0_ym19_1_1473041155785_17602" dir="ltr"><span></span></div> <div id="yui_3_16_0_ym19_1_1473041155785_21269" class="qtdSeparateBR"><br><br></div><div style="display: block;" id="yui_3_16_0_ym19_1_1473041155785_21323" class="yahoo_quoted"> <div id="yui_3_16_0_ym19_1_1473041155785_21322" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 14px;"> <div id="yui_3_16_0_ym19_1_1473041155785_21321" style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div id="yui_3_16_0_ym19_1_1473041155785_21467" dir="ltr"><font face="Arial" size="2"> On Saturday, September 3, 2016 4:17 AM, Stefan Hornburg (Racke) <racke@linuxia.de> wrote:<br></font></div> <br><br> <div id="yui_3_16_0_ym19_1_1473041155785_21320" class="y_msg_container">On 09/03/2016 03:38 AM, Rajeev Prasad wrote:<br clear="none">> DBIx error in webserver log:<br clear="none">> <br clear="none">> [cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING" via package "COUNT" (perhaps you forgot to load<br clear="none">> "COUNT"?)<br clear="none">> <br clear="none">> <br clear="none">> the query:<br clear="none">> <br clear="none">> my $obj_rs = $schema->resultset('itemList')->serach(<br clear="none">> { t_id => { -in => [ $tStr ] }},<br clear="none">> {<br clear="none">> group_by => [ qw(i_id) ],<br clear="none">> HAVING COUNT('t_id') = $tCount<br clear="none">> }<br clear="none">> );<br clear="none">> <br clear="none">> <br clear="none">> currently database has no records which match the query. but i was not expecting such an error.<br clear="none">> <br clear="none">> what is wrong in my statement?<br clear="none"><br clear="none">You can't just drop in literal SQL like that and expect it to work - because Perl now assumes<br clear="none">"HAVING COUNT" being Perl code. I suppose "use strict; use warnings" would have alerted you of<br clear="none">that.<br clear="none"><br clear="none">It is possible to you use literal SQL with DBIx::Class, please refer to the docs.<br clear="none"><br clear="none">I would suggest a different approach - using subqueries. Please take a look at the excellent<br clear="none">advent calendar post from fREW:<br clear="none"><br clear="none"><a shape="rect" href="http://www.perladvent.org/2012/2012-12-21.html" target="_blank">http://www.perladvent.org/2012/2012-12-21.html</a><br clear="none"><br clear="none">This is still accurate.<br clear="none"><br clear="none">I covered a few use cases in my presentation at the Alpine Perl Workshop:<br clear="none"><br clear="none"><a shape="rect" href="https://www.linuxia.de/talks/alpine2016/dbic-pr-en-beamer.pdf" target="_blank">https://www.linuxia.de/talks/alpine2016/dbic-pr-en-beamer.pdf</a><br clear="none"><br clear="none">Regards<br clear="none"> Racke<br clear="none"><br clear="none">> <br clear="none">> thank you.<br clear="none">> Rajeev<br clear="none">> <br clear="none">> <br clear="none">> _______________________________________________<br clear="none">> List: <a shape="rect" 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 clear="none">> IRC: irc.perl.org#dbix-class<br clear="none">> SVN: <a shape="rect" href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br clear="none">> Searchable Archive: <a shape="rect" 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 clear="none">> <br clear="none"><br clear="none"><br clear="none">-- <br clear="none">Ecommerce and Linux consulting + Perl and web application programming.<div class="yqt2386186295" id="yqtfd90142"><br clear="none"><br clear="none"><br clear="none">_______________________________________________<br clear="none">List: </div><a shape="rect" 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 clear="none">IRC: irc.perl.org#dbix-class<br clear="none">SVN: <a shape="rect" href="http://dev.catalyst.perl.org/repos/bast/DBIx-Class/" target="_blank">http://dev.catalyst.perl.org/repos/bast/DBIx-Class/</a><br clear="none">Searchable Archive: <a shape="rect" 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><div class="yqt2386186295" id="yqtfd28015"><br clear="none"></div><br><br></div> </div> </div> </div></div></body></html>