[Dbix-class] 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:gjungwirth at sipwise.com]
Sent: 13 January 2017 17:12
To: dbix-class at lists.scsys.co.uk
Subject: Re: [Dbix-class] Count + having


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

"The "as"<https://metacpan.org/pod/DBIx::Class::ResultSet#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@lists.scsys.co.uk

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