Bug #119814 for SQL-Abstract-More: count syntax

Wed Jan 11 10:48:57 2017 francois.rappaz [...] unifr.ch - Ticket created

Subject:	count syntax
Date:	Wed, 11 Jan 2017 15:40:24 +0000
To:	"bug-SQL-Abstract-More [...] rt.cpan.org" <bug-SQL-Abstract-More [...] rt.cpan.org>
From:	RAPPAZ Francois <francois.rappaz [...] unifr.ch>

Hi there I use SQL ::Abstract ::More version 1.28 I call select with the following parameters '-from' => [ '-join', 'jrnabt', 'noabt=noabt', 'abo' ], '-having' => { '-count' => { 'jrnabt.noabt' => { '>' => 1 } }, 'abo.noabt' => '919' }, '-columns' => [ 'abo.noabt' ], '-group_by' => 'abo.noabt' }; The generated sql (with the value added) is SELECT abo.noabt FROM jrnabt INNER JOIN abo ON ( jrnabt.noabt = abo.noabt ) GROUP BY abo.noabt HAVING ( ( (COUNT jrnabt.noabt > '1') AND abo.noabt = '919') ) This fails (r You have an error in your SQL syntax; check the manual ... ) The correct sql should be SELECT abo.noabt FROM jrnabt INNER JOIN abo ON ( jrnabt.noabt = abo.noabt ) GROUP BY abo.noabt HAVING ( ( COUNT(jrnabt.noabt > '1') AND abo.noabt = '919' ) ) Note that there must be no space after count. Am I missing something in the way I call select or is it a bug ? Thanks François

Sat Jan 14 03:11:24 2017 DAMI [...] cpan.org - Correspondence added

Hi François, SQL::Abstract::More has no special support for SQL functions in WHERE clauses. It just inherits from SQL::Abstract, which does not either have special support. The syntax -function => value is used by DBIx::Class, through some extensions to SQL::Abstract provided by DBIx::Class::SQLMaker. Within SQL::Abstract, the way to use SQL functions is just through strings, like -having => { "count(jrnabt.noabt)" => {">=" => 1} } Hope this helps.

Sat Jan 14 03:11:25 2017 The RT System itself - Status changed from 'new' to 'open'

Sat Jan 14 03:11:35 2017 DAMI [...] cpan.org - Status changed from 'open' to 'rejected'

Sat Jan 14 03:11:46 2017 DAMI [...] cpan.org - Correspondence added

Hi François, SQL::Abstract::More has no special support for SQL functions in WHERE clauses. It just inherits from SQL::Abstract, which does not either have special support. The syntax -function => value is used by DBIx::Class, through some extensions to SQL::Abstract provided by DBIx::Class::SQLMaker. Within SQL::Abstract, the way to use SQL functions is just through strings, like -having => { "count(jrnabt.noabt)" => {">=" => 1} } Hope this helps.

Tue Jan 17 03:30:59 2017 francois.rappaz [...] unifr.ch - Correspondence added

Subject:	RE: [rt.cpan.org #119814] count syntax
Date:	Tue, 17 Jan 2017 08:20:55 +0000
To:	"bug-SQL-Abstract-More [...] rt.cpan.org" <bug-SQL-Abstract-More [...] rt.cpan.org>
From:	RAPPAZ Francois <francois.rappaz [...] unifr.ch>

Hi Laurent Thanks for helping Finally this works $sqla->select( -columns => [qw/abo.noabt count(jrnabt.noabt)/], -from => [ -join => qw/jrnabt noabt=noabt abo/ ], -having => 'count(jrnabt.noabt) > 1 AND abo.noabt='.$value_aref->[0] , -group_by => "abo.noabt", ); François Show quoted text

-----Original Message----- From: Laurent Dami via RT [mailto:bug-SQL-Abstract-More@rt.cpan.org] Sent: 14 January 2017 09:12 To: RAPPAZ Francois <francois.rappaz@unifr.ch> Subject: [rt.cpan.org #119814] count syntax <URL: https://rt.cpan.org/Ticket/Display.html?id=119814 > Hi François, SQL::Abstract::More has no special support for SQL functions in WHERE clauses. It just inherits from SQL::Abstract, which does not either have special support. The syntax -function => value is used by DBIx::Class, through some extensions to SQL::Abstract provided by DBIx::Class::SQLMaker. Within SQL::Abstract, the way to use SQL functions is just through strings, like -having => { "count(jrnabt.noabt)" => {">=" => 1} } Hope this helps.