Re: [Dbix-class] Count + having

2017-01-24 Thread Matt S Trout 
On Tue, Jan 17, 2017 at 08:06:42AM +, 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@lists.scsys.co.uk

Re: [Dbix-class] Count + having

2017-01-17 Thread 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 
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:gjungwi...@sipwise.com]
> *Sent:* 13 January 2017 17:12
> *To:* dbix-class@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@lists.scsys.co.uk
>



-- 
Len Jaffe - Information Technology Smoke Jumper - lenja...@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.
___
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

Re: [Dbix-class] Count + having

2017-01-17 Thread 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:gjungwi...@sipwise.com]
Sent: 13 January 2017 17:12
To: dbix-class@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@lists.scsys.co.uk

Re: [Dbix-class] Count + having

2017-01-13 Thread 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@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@lists.scsys.co.uk