[Dbix-class] DBIx HAVING and COUNT error

[Rajeev Prasad]

hello all,
thanks for response. sorry if i sent email directly to you. (i think i just did a replay all)
i tried that and many other way, but i am going crazy that it just wont work????
SQL I 'hope' to implement is:
SELECT *
FROM itemlist
WHERE t_id IN (1,2,3)
GROUP BY i_id
HAVING COUNT(1) = 3
CODE:................$tStr is 55  (has only one element)
$tCount = 1   (no. of items above.)

> my $obj_rs = $schema->resultset('itemList')->serach(
>                { t_id => { -in => [ $tStr ] }},
>                {
>                    group_by => [ qw(i_id) ],
>                    having => { 'count 1' => $tCount }, 
>                }
>            );

i get this error:
 [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...


which i guess converts to:
SELECT me.i_id, me.t_id FROM itemList me 
WHERE ( t_id IN ( 55 ) ) 
GROUP BY i_id 
HAVING count 1 = 1
 what is wrong in this SQL??..... (I am assuming that by now DBIx syntax is good)


 

    On Saturday, September 3, 2016 4:17 AM, Stefan Hornburg (Racke) <racke at linuxia.de> wrote:
 

 On 09/03/2016 03:38 AM, Rajeev Prasad wrote:
> DBIx error in webserver log:
> 
> [cgi:error] [pid 29640] ... AH01215: Can't locate object method "HAVING" via package "COUNT" (perhaps you forgot to load
> "COUNT"?)
> 
> 
> the query:
> 
> my $obj_rs = $schema->resultset('itemList')->serach(
>                { t_id => { -in => [ $tStr ] }},
>                {
>                    group_by => [ qw(i_id) ],
>                    HAVING COUNT('t_id') = $tCount
>                }
>            );
> 
> 
> currently database has no records which match the query. but i was not expecting such an error.
> 
> what is wrong in my statement?

You can't just drop in literal SQL like that and expect it to work - because Perl now assumes
"HAVING COUNT" being Perl code. I suppose "use strict; use warnings" would have alerted you of
that.

It is possible to you use literal SQL with DBIx::Class, please refer to the docs.

I would suggest a different approach - using subqueries. Please take a look at the excellent
advent calendar post from fREW:

http://www.perladvent.org/2012/2012-12-21.html

This is still accurate.

I covered a few use cases in my presentation at the Alpine Perl Workshop:

https://www.linuxia.de/talks/alpine2016/dbic-pr-en-beamer.pdf

Regards
        Racke

> 
> thank you.
> Rajeev
> 
> 
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk
> 


-- 
Ecommerce and Linux consulting + Perl and web application programming.


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk


   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20160905/d18d051e/attachment.htm>