SQL::Abstract::Clauses - HAVING and GROUP BY clauses for SQL::Abstract
my ($sql,@bind) = $s->select($table,[ 'listened', 'count(*) as col_count' ], where => { artist => 'Jamiroquai', mime_type => 'audio/mpeg' }, group_by => 'listened', order_by => "col_count desc", having => { col_count => {'<' => 5 }}, );
will generate the SQL statement
SELECT listened, count(*) as col_count FROM files WHERE ( artist = ? AND mime_type = ? ) GROUP BY listened HAVING ( col_count < ? ) ORDER BY col_count desc
and @bind
will contain the appropriate bind values:
# @bind = ('Jamiroquai', 'audio/mpeg', 5)
This module provides objects that have a slightly different syntax from SQL::Abstract. This is the price you pay for wanting HAVING
and GROUP BY
.
The LIMIT
clause is also recognized, but I haven't thought about integrating it yet. The module should reuse the work already done in SQL::Abstract::Limit, but that module extends the SQL::Abstract syntax in its own way.
$sql-
having WHERE>Returns a SQL string and bind parameters containing the appropriate HAVING
clause.
$sql->limit COUNT
=head2 $sql->limit [COUNT, OFFSET]
Returns a SQL string and bind parameters containing the appropriate LIMIT
clause. You pass in either a single scalar, specifying the number of rows to return, or an array ref to an array of two elements.
NOT IMPLEMENTED
$sql->order ORDER
ORDER is either a scalar, giving the column (and direction) to order or an array reference, if you want to order according to more than a single column.
If you want to order in a certain direction, you need to mash the direction together with the column name:
$sql->order( "listened desc" )
This is ugly and will maybe change.
$sql->group GROUP
GROUP is either a scalar, giving the column (and direction) to group by or an array reference, if you want to group by more than a single column.
$sql->select TABLE, COLUMNS, %CLAUSES
Returns the SQL statement and bind values for the clauses. This is different from how SQL::Abstract does it. The code tries to recognize old usage and fall back to SQL::Abstract, but the fallback doesn't always work.
my ($sql,@bind) = $s->select('files',[ 'listened', 'count(*) as col_count' ], where => { artist => 'Jamiroquai', mime_type => 'audio/mpeg' }, group_by => 'listened', order_by => "col_count desc", having => { col_count => {'<' => 5 }},
Max Maischein, <corion@cpan.org>