NAME
SQL::Abstract::Query::Select - An object that represents a SQL SELECT.
SYNOPSIS
use SQL::Abstract::Query;
my $query = SQL::Abstract::Query->new();
# Find all cities:
my ($sql, @bind_values) = $query->select( 'city_id', 'city' );
my $rows = $dbh->selectall_arrayref( $sql, undef, @bind_values );
# Find all cities in a particular country:
my ($sql, @bind_values) = $query->select(
[ 'city_id', 'city' ],
'city',
{ country_id => $country_id },
);
my $rows = $dbh->selectall_arrayref( $sql, undef, @bind_values );
# Use the OO interface to re-use the query and select cities from
# multiple countries:
my $select = $query->select(
[ 'city_id', 'city' ],
'city',
{ country_id => 'id' },
);
my $sth = $dbh->prepare( $select->sql() );
$sth->execute( $select->values({ id => $country1_id }) );
my $country1_cities = $sth->fetchall_arrayref();
$sth->execute( $select->values({ id => $country2_id }) );
my $country2_cities = $sth->fetchall_arrayref();DESCRIPTION
The select query wraps around SQL::Abstract's select() method and provides extra SQL syntax for table joins, grouping, ordering, and limiting results.
Instances of this class should be created using "select" in SQL::Abstract::Query.
This class applies the SQL::Abstract::Query::Statement role.
ARGUMENTS
fields
An arrayref or a plain string of field names.
from
The FROM section of the SELECT query. Can be either a Scalar which will be quoted, an array ref of either scalars or arrays, or a scalar ref wich will not be quoted.
A single table, quoted:
from => 'users'
FROM "users"An arbitrary string, not quoted:
from => \'users'
FROM usersA list of table names, some may be quoted, some not, separated by commas:
from => ['users', \'user_emails']
FROM "users", user_emailsA list of table names, the first one with an alias:
from => [ {name => users, as => 'u'}, 'user_emails' ]
FROM "users" "u", "user_emails"A join with aliases:
from => [ {users => 'u'}, {user_emails => e, using => 'user_id'} ]
FROM "users" "u" JOIN "user_emails" "e" ON ( "e"."user_id" = "u"."user_id" )Another join but using "on" instead of "using", and adding another non-join table:
from => [ {users => 'u'}, {user_emails => 'e', on=>{ 'e.user_id' => \'= u.user_id' }}, 'logs' ]
FROM "users" "u" JOIN "user_emails" "e" ON ( "e"."user_id" = u.user_id ), logsNote that the FROM part of the SELECT is not handled by SQL::Abstract at all.
where
Optional. See "Where" in SQL::Abstract::Query::Statement.
group_by
The optional GROUP BY clause which can be a scalar or an array reference. SQL::Abstract does not natively support GROUP BY so this module generates the SQL itself. Here are some samples:
Group by a single column:
group_by => 'foo'
GROUP BY "foo"Group by several columns:
group_by => ['foo', 'bar']
GROUP BY "foo", "bar"order_by
The optional ORDER BY clause which can be a scalar or an array reference. This order_by is not processed by SQL::Abstract at all and is instead handled by this module completely. Here are some samples of valid input and what the SQL would look like:
Order by a single column:
order_by => 'foo'
ORDER BY "foo"Order by several columns:
order_by => ['foo', 'bar']
ORDER BY "foo", "bar"Order by several columns, setting the ordering direction:
order_by => [ {foo => 'asc'}, 'bar' ]
ORDER BY "foo" ASC, "bar"limit
The optional LIMIT clause which limits the number of rows that will be returned. This can be either an integer or a string for use with values().
offset
The optional offeset for the LIMIT clause which changes the sarting position of the limit. For example, if you set the limit to 10 and the offset to 5 you'd get rows 5 through 14 (where row 1 is the first row). The setting of offset will be ignored if the limit is not also set.
This can be either an integer or a string for use with values().
AUTHOR
Aran Clary Deltac <bluefeet@gmail.com>
LICENSE
This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.