Count + having

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Count + having

RAPPAZ Francois
I'm trying to use count and having
I have
Abo a table of rows describing orders (primary key: noabt)
abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)

I would like to know if a specific order has 2 or more corresponding rows in Jrnabt

I'm trying

$schema->resultset('Abo')->search_rs(
                       {
                          'me.noabt' => $value,
                       },
                      {   select => ['me.noabt', {count => 'abojrnabt.noabt'}],
                          as     => [qw/noabt count_abo/],
                          join       => ['abojrnabt'],
                          group_by   => ['noabt'],
                          having => [ { 'count_abo' => { '>' => 1 } } ],
                      },
);
But this fails with
***   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 noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...

Thanks for any suggestion

François

_______________________________________________
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
|

Re: Count + having

Gerhard Jungwirth

One think to have in mind would be from the documentation:

"The "as" attribute has nothing to do with the SQL-side identifier aliasing AS."

instead you should write

    select => ['me.noabt', { count => 'abojrnabt.noabt', -as => 'count_abo' } ] (untested)

   

On 2017-01-13 15:35, RAPPAZ Francois wrote:
I'm trying to use count and having 
I have 
Abo a table of rows describing orders (primary key: noabt)
abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)

I would like to know if a specific order has 2 or more corresponding rows in Jrnabt

I'm trying

$schema->resultset('Abo')->search_rs(
                       {
                          'me.noabt' => $value,
                       },
                      {   select => ['me.noabt', {count => 'abojrnabt.noabt'}],
                          as     => [qw/noabt count_abo/],
                          join       => ['abojrnabt'],
                          group_by   => ['noabt'],
                          having => [ { 'count_abo' => { '>' => 1 } } ],
                      },
);
But this fails with
***   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 noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...

Thanks for any suggestion

François

_______________________________________________
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
|

Re: Count + having

RAPPAZ Francois

Thanks for the help. I tryied

    $schema->resultset('Abo')->search_rs(

            { 

                'me.noabt' => $value_aref->[0],

            },

            {   select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],

                join       => ['abojrnabt'],

                group_by   => ['me.noabt'],

                having => { 'count_abo' => { '>' => 1 } },

            });

Which failed with

***   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

 

But this

    $schema->resultset('Abo')->search_rs(

            { 

                'me.noabt' => $value_aref->[0],

            },

            {   select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],

                join       => ['abojrnabt'],

                group_by   => ['me.noabt'],

                having => \[ 'count(abojrnabt.noabt) > ?', 1 ] ,

            });

 

works.

 

Thanks

 

François

 

From: Gerhard Jungwirth [mailto:[hidden email]]
Sent: 13 January 2017 17:12
To: [hidden email]
Subject: Re: [Dbix-class] Count + having

 

One think to have in mind would be from the documentation:

"The "as" attribute has nothing to do with the SQL-side identifier aliasing AS."

instead you should write

    select => ['me.noabt', { count => 'abojrnabt.noabt', -as => 'count_abo' } ]
 
(untested)

   

On 2017-01-13 15:35, RAPPAZ Francois wrote:

I'm trying to use count and having 
I have 
Abo a table of rows describing orders (primary key: noabt)
abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)
 
I would like to know if a specific order has 2 or more corresponding rows in Jrnabt
 
I'm trying
 
$schema->resultset('Abo')->search_rs(
                       {
                          'me.noabt' => $value,
                       },
                      {   select => ['me.noabt', {count => 'abojrnabt.noabt'}],
                          as     => [qw/noabt count_abo/],
                          join       => ['abojrnabt'],
                          group_by   => ['noabt'],
                          having => [ { 'count_abo' => { '>' => 1 } } ],
                      },
);
But this fails with
***   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 noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...
 
Thanks for any suggestion
 
François
 
_______________________________________________
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
|

Re: Count + having

Len Jaffe
I don't remember using an RDMS that allowed the column alias in the HAVING clause.  

It sure would be nice though.

On Tue, Jan 17, 2017 at 3:06 AM, RAPPAZ Francois <[hidden email]> wrote:

Thanks for the help. I tryied

    $schema->resultset('Abo')->search_rs(

            { 

                'me.noabt' => $value_aref->[0],

            },

            {   select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],

                join       => ['abojrnabt'],

                group_by   => ['me.noabt'],

                having => { 'count_abo' => { '>' => 1 } },

            });

Which failed with

***   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

 

But this

    $schema->resultset('Abo')->search_rs(

            { 

                'me.noabt' => $value_aref->[0],

            },

            {   select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],

                join       => ['abojrnabt'],

                group_by   => ['me.noabt'],

                having => \[ 'count(abojrnabt.noabt) > ?', 1 ] ,

            });

 

works.

 

Thanks

 

François

 

From: Gerhard Jungwirth [mailto:[hidden email]]
Sent: 13 January 2017 17:12
To: [hidden email]
Subject: Re: [Dbix-class] Count + having

 

One think to have in mind would be from the documentation:

"The "as" attribute has nothing to do with the SQL-side identifier aliasing AS."

instead you should write

    select => ['me.noabt', { count => 'abojrnabt.noabt', -as => 'count_abo' } ]
 
(untested)

   

On 2017-01-13 15:35, RAPPAZ Francois wrote:

I'm trying to use count and having 
I have 
Abo a table of rows describing orders (primary key: noabt)
abojrnabt a relationship between this Abo table a Jrnabt table of 2 columns (noabt, nofm)
 
I would like to know if a specific order has 2 or more corresponding rows in Jrnabt
 
I'm trying
 
$schema->resultset('Abo')->search_rs(
                       {
                          'me.noabt' => $value,
                       },
                      {   select => ['me.noabt', {count => 'abojrnabt.noabt'}],
                          as     => [qw/noabt count_abo/],
                          join       => ['abojrnabt'],
                          group_by   => ['noabt'],
                          having => [ { 'count_abo' => { '>' => 1 } } ],
                      },
);
But this fails with
***   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 noabt HAVING count_abo > ?" with ParamValues: 0="919", 1=1] at ...
 
Thanks for any suggestion
 
François
 
_______________________________________________
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@...



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

Re: Count + having

Matt S Trout-2
In reply to this post by RAPPAZ Francois
On Tue, Jan 17, 2017 at 08:06:42AM +0000, RAPPAZ Francois wrote:

> But this
>     $schema->resultset('Abo')->search_rs(
>             {
>                 'me.noabt' => $value_aref->[0],
>             },
>             {   select => ['me.noabt', {count => 'abojrnabt.noabt', -as => 'count_abo'}],
>                 join       => ['abojrnabt'],
>                 group_by   => ['me.noabt'],
>                 having => \[ 'count(abojrnabt.noabt) > ?', 1 ] ,
>             });
>
> works.

Unless I misremember, the SELECT list isn't necessarily in scope for HAVING.

Worth playing around at an SQL console to get a feel for how it actually
interacts; HAVING is awesome but something you tend to use rarely enough that
it's easy to forget how it works (I know I do, hence the 'unless' above ;)

--
Matt S Trout - Shadowcat Systems - Perl consulting with a commit bit and a clue

http://shadowcat.co.uk/blog/matt-s-trout/   http://twitter.com/shadowcat_mst/

Email me now on mst (at) shadowcat.co.uk and let's chat about how our CPAN
commercial support, training and consultancy packages could help your team.

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