[Dbix-class] 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 <francois.rappaz at unifr.ch>
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: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
>
>
>
> _______________________________________________
> 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 at lists.scsys.co.uk
>



-- 
Len Jaffe - Information Technology Smoke Jumper - lenjaffe at jaffesystems.com
614-404-4214    @LenJaffe <https://www.twitter.com/lenJaffe>
www.lenjaffe.com
Host of Code Jam Columbus <http://www.meetup.com/techlifecolumbus/>  -
@CodeJamCMH <https://www.twitter.com/CodeJamCMH>
Curator of Advent Planet <http://www.lenjaffe.com/AdventPlanet/> - An
Aggregation of Online Advent Calendars.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20170117/7bb5c1da/attachment.htm>