NAME

SQL::Abstract::Clauses - HAVING and GROUP BY clauses for SQL::Abstract

SYNOPSIS

  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)

CAVEATS

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 }},

AUTHOR

Max Maischein, <corion@cpan.org>

SEE ALSO

SQL::Abstract, SQL::Abstract::Limit