DBIx HAVING and COUNT error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

DBIx HAVING and COUNT error

Rajeev Prasad
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?

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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Stefan Hornburg (Racke)
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@...
>


--
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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) <[hidden email]> 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@...
>


--
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@...




_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Dmitry Latin
HAVING COUNT(1) = 3
vs
HAVING count 1 = 1

On 5 September 2016 at 06:13, Rajeev Prasad <[hidden email]> wrote:

> 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)
> <[hidden email]> 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@...
>>
>
>
> --
> 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@...
>
>
>
>
> _______________________________________________
> 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@...



--
//wbr, Dmitry L.

_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Rajeev Prasad
> 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)

here it is only number (55) so count is also just 1. is this not correct?


On Monday, September 5, 2016 12:02 AM, Dmitry L. <[hidden email]> wrote:


HAVING COUNT(1) = 3
vs
HAVING count 1 = 1

On 5 September 2016 at 06:13, Rajeev Prasad <[hidden email]> wrote:

> 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)
> <[hidden email]> 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@...
>>
>
>
> --
> 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@...
>
>
>
>
> _______________________________________________
> 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@...



--
//wbr, Dmitry L.




_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Len Jaffe
In reply to this post by Rajeev Prasad

On Sun, Sep 4, 2016 at 11:13 PM, Rajeev Prasad <[hidden email]> wrote:
having => { 'count 1' => $tCount },

having => { 'count(1)' => $tCount }, 


--
Len Jaffe - Information Technology Smoke Jumper - [hidden email] 
614-404-4214    @LenJaffe  www.lenjaffe.com
Curator of Advent Planet - An Aggregation of Online Advent Calendars.


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Dagfinn Ilmari Mannsåker
Len Jaffe <[hidden email]> writes:

> On Sun, Sep 4, 2016 at 11:13 PM, Rajeev Prasad <[hidden email]> wrote:
>
>> having => { 'count 1' => $tCount },
>
> having => { 'count(1)' => $tCount },

That breaks if you enable name quoting, as the hash keys are interpreted
as column names.  You can use the arrayrefref syntax for arbitrary SQL
with placeholders:

  having => \['count(1) = ?', $tCount ],

--
"The surreality of the universe tends towards a maximum" -- Skud's Law
"Never formulate a law or axiom that you're not prepared to live with
 the consequences of."                              -- Skud's Meta-Law


_______________________________________________
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@...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DBIx HAVING and COUNT error

Len Jaffe


On Tue, Sep 6, 2016 at 5:01 AM, Dagfinn Ilmari Mannsåker <[hidden email]> wrote:
Len Jaffe <[hidden email]> writes:

> On Sun, Sep 4, 2016 at 11:13 PM, Rajeev Prasad <[hidden email]> wrote:
>> having => { 'count 1' => $tCount },
> having => { 'count(1)' => $tCount },

That breaks if you enable name quoting, as the hash keys are interpreted
as column names.  You can use the arrayrefref syntax for arbitrary SQL
with placeholders:

  having => \['count(1) = ?', $tCount ],

Yup.  My point however was the lack of valid 'count' syntax in the having has key/value.


 
--
Len Jaffe - Information Technology Smoke Jumper - [hidden email] 
614-404-4214    @LenJaffe  www.lenjaffe.com
Curator of Advent Planet - An Aggregation of Online Advent Calendars.


_______________________________________________
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@...
Loading...