<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_40172">> SELECT me.i_id, me.t_id FROM itemList me<br id="yui_3_16_0_ym19_1_1473041155785_40204" clear="none">> WHERE ( t_id IN ( 55 ) )<br id="yui_3_16_0_ym19_1_1473041155785_40205" clear="none">> GROUP BY i_id<br id="yui_3_16_0_ym19_1_1473041155785_40206" clear="none">> HAVING count 1 = 1<br id="yui_3_16_0_ym19_1_1473041155785_40207" clear="none">><br id="yui_3_16_0_ym19_1_1473041155785_40208" clear="none">> what is wrong in this SQL??..... (I am assuming that by now DBIx syntax is<br id="yui_3_16_0_ym19_1_1473041155785_40209" clear="none">> good)</div><div id="yui_3_16_0_ym19_1_1473041155785_40210" dir="ltr"><br></div><div id="yui_3_16_0_ym19_1_1473041155785_40243" dir="ltr">here it is only number (55) so count is also just 1. is this not correct?<br></div> <div class="qtdSeparateBR"><br><br></div><div style="display: block;" class="yahoo_quoted"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 14px;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div dir="ltr"><font face="Arial" size="2"> On Monday, September 5, 2016 12:02 AM, Dmitry L. <dim0xff@gmail.com> wrote:<br></font></div> <br><br> <div class="y_msg_container">HAVING COUNT(1) = 3<br clear="none">vs<br clear="none">HAVING count 1 = 1<br clear="none"><br clear="none">On 5 September 2016 at 06:13, Rajeev Prasad <<a shape="rect" ymailto="mailto:rp.neuli@yahoo.com" href="mailto:rp.neuli@yahoo.com">rp.neuli@yahoo.com</a>> wrote:<br clear="none">> hello all,<br clear="none">><br clear="none">> thanks for response. sorry if i sent email directly to you. (i think i just<br clear="none">> did a replay all)<br clear="none">><br clear="none">> i tried that and many other way, but i am going crazy that it just wont<br clear="none">> work????<br clear="none">><br clear="none">> SQL I 'hope' to implement is:<br clear="none">><br clear="none">> SELECT *<br clear="none">> FROM itemlist<br clear="none">> WHERE t_id IN (1,2,3)<br clear="none">> GROUP BY i_id<br clear="none">> HAVING COUNT(1) = 3<br clear="none">><br clear="none">><br clear="none">> CODE:................<br clear="none">> $tStr is 55 (has only one element)<br clear="none">> $tCount = 1 (no. of items above.)<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 1' => $tCount },<br clear="none">>> }<br clear="none">>> );<br clear="none">><br clear="none">><br clear="none">> i get this error:<br clear="none">><br clear="none">> [cgi:error] [pid 772] [client ....] AH01215:<br clear="none">> DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::mysql::st<br clear="none">> execute failed: You have an error in your SQL syntax; check the manual that<br clear="none">> corresponds to your MySQL server version for the right syntax to use near '1<br clear="none">> = '1'' at line 1 [for Statement "SELECT me.t_id, me.i_id FROM itemList me<br clear="none">> WHERE ( t_id IN ( ? ) ) GROUP BY o_id HAVING count 1 = ?" with ParamValues:<br clear="none">> 0='55', 1=1] at /var/ww...<br clear="none">><br clear="none">><br clear="none">> which i guess converts to:<br clear="none">><br clear="none">> SELECT me.i_id, me.t_id FROM itemList me<br clear="none">> WHERE ( t_id IN ( 55 ) )<br clear="none">> GROUP BY i_id<br clear="none">> HAVING count 1 = 1<br clear="none">><br clear="none">> what is wrong in this SQL??..... (I am assuming that by now DBIx syntax is<br clear="none">> good)<br clear="none">><br clear="none">><br clear="none">><br clear="none">><br clear="none">> On Saturday, September 3, 2016 4:17 AM, Stefan Hornburg (Racke)<br clear="none">> <<a shape="rect" ymailto="mailto:racke@linuxia.de" href="mailto:racke@linuxia.de">racke@linuxia.de</a>> wrote:<br clear="none">><br clear="none">><br clear="none">> 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"<br clear="none">>> 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<br clear="none">>> 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<br clear="none">> Perl now assumes<br clear="none">> "HAVING COUNT" being Perl code. I suppose "use strict; use warnings" would<br clear="none">> 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<br clear="none">> docs.<br clear="none">><br clear="none">> I would suggest a different approach - using subqueries. Please take a look<br clear="none">> 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:<br clear="none">>> <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="yqt4105112203" id="yqtfd70398"><br clear="none">><br clear="none">><br clear="none">><br clear="none">> _______________________________________________<br clear="none">> List:<br clear="none">> <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:<br clear="none">> <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">> _______________________________________________<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:<br clear="none">> <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><br clear="none"><br clear="none"><br clear="none"><br clear="none">-- <br clear="none">//wbr, Dmitry L.<div class="yqt4105112203" id="yqtfd19218"><br clear="none"></div><br><br></div> </div> </div> </div></div></body></html>