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 users

A list of table names, some may be quoted, some not, separated by commas:

    from => ['users', \'user_emails']
    FROM "users", user_emails

A 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 ), logs

Note 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.

cpanm SQL::Abstract::Query

perl -MCPAN -e shell
install SQL::Abstract::Query