GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages


Manual Reference Pages  -  SQL::MAKER::SELECT (3)

.ds Aq ’

NAME

SQL::Maker::Select - dynamic SQL generator

CONTENTS

SYNOPSIS



    my $sql = SQL::Maker::Select->new()
                                  ->add_select(foo)
                                  ->add_select(bar)
                                  ->add_select(baz)
                                  ->add_from(table_name)
                                  ->as_sql;
    # => "SELECT foo, bar, baz FROM table_name"



DESCRIPTION

METHODS

my $sql = $stmt->as_sql(); Render the SQL string.
my @bind = $stmt->bind(); Get the bind variables.
$stmt->add_select(*)
$stmt->add_select($col => $alias)
$stmt->add_select(\COUNT(*) => cnt) Add a new select term. It’s automatically quoted.
$stmt->add_from($table :Str | $select :SQL::Maker::Select) : SQL::Maker::Select Add a new FROM clause. You can specify the table name or an instance of SQL::Maker::Select for a sub-query.

Return: $stmt itself.

$stmt->add_join(user => {type => inner, table => config, condition => user.user_id = config.user_id});
$stmt->add_join(user => {type => inner, table => config, condition => {user.user_id => config.user_id});
$stmt->add_join(user => {type => inner, table => config, condition => [user_id]}); Add a new JOIN clause. If you pass an arrayref for ’condition’ then it uses ’USING’. If ’type’ is omitted it falls back to plain JOIN.



    my $stmt = SQL::Maker::Select->new();
    $stmt->add_join(
        user => {
            type      => inner,
            table     => config,
            condition => user.user_id = config.user_id,
        }
    );
    $stmt->as_sql();
    # => FROM user INNER JOIN config ON user.user_id = config.user_id

    my $stmt = SQL::Maker::Select->new(quote_char => `, name_sep => .);
    $stmt->add_join(
        user => {
            type      => inner,
            table     => config,
            condition => {user.user_id => config.user_id},
        }
    );
    $stmt->as_sql();
    # => FROM `user` INNER JOIN `config` ON `user`.`user_id` = `config`.`user_id`

    my $stmt = SQL::Maker::Select->new();
    $stmt->add_select(name);
    $stmt->add_join(
        user => {
            type      => inner,
            table     => config,
            condition => [user_id],
        }
    );
    $stmt->as_sql();
    # => SELECT name FROM user INNER JOIN config USING (user_id)

    my $subquery = SQL::Maker::Select->new();
    $subquery->add_select(*);
    $subquery->add_from( foo );
    $subquery->add_where( hoge => fuga );
    my $stmt = SQL::Maker::Select->new();
    $stmt->add_join(
        [ $subquery, bar ] => {
            type      => inner,
            table     => baz,
            alias     => b1,
            condition => bar.baz_id = b1.baz_id
        },
    );
    $stmt->as_sql;
    # => "FROM (SELECT * FROM foo WHERE (hoge = ?)) bar INNER JOIN baz b1 ON bar.baz_id = b1.baz_id";



$stmt->add_index_hint(foo => {type => USE, list => [index_hint]});
$stmt->add_index_hint(foo => index_hint);
$stmt->add_index_hint(foo => [index_hint]);


    my $stmt = SQL::Maker::Select->new();
    $stmt->add_select(name);
    $stmt->add_from(user);
    $stmt->add_index_hint(user => {type => USE, list => [index_hint]});
    $stmt->as_sql();
    # => "SELECT name FROM user USE INDEX (index_hint)"



$stmt->add_where(foo_id => bar); Add a new WHERE clause.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->add_where(name => john)
                                   ->add_where(type => {IN => [qw/1 2 3/]})
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE (name = ?) AND (type IN (?, ?, ?))"



$stmt->add_where_raw(id = ?, [1]) Add a new WHERE clause from raw placeholder string and bind variables.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->add_where_raw(EXISTS(SELECT * FROM bar WHERE name = ?) => [john])
                                   ->add_where_raw(type IS NOT NULL)
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE (EXISTS(SELECT * FROM bar WHERE name = ?)) AND (type IS NOT NULL)"



$stmt->set_where($condition) Set the WHERE clause.

$condition should be instance of SQL::Maker::Condition.



    my $cond1 = SQL::Maker::Condition->new()
                                       ->add("name" => "john");
    my $cond2 = SQL::Maker::Condition->new()
                                       ->add("type" => {IN => [qw/1 2 3/]});
    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->set_where($cond1 & $cond2)
                                   ->as_sql();
    # => "SELECT c FROM foo WHERE ((name = ?)) AND ((type IN (?, ?, ?)))"



$stmt->add_order_by(foo);
$stmt->add_order_by({foo => DESC}); Add a new ORDER BY clause.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->add_order_by(name => DESC)
                                   ->add_order_by(id)
                                   ->as_sql();
    # => "SELECT c FROM foo ORDER BY name DESC, id"



$stmt->add_group_by(foo); Add a new GROUP BY clause.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->add_group_by(id)
                                   ->as_sql();
    # => "SELECT c FROM foo GROUP BY id"

    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->add_group_by(id => DESC)
                                   ->as_sql();
    # => "SELECT c FROM foo GROUP BY id DESC"



$stmt->limit(30)
$stmt->offset(5) Add LIMIT and OFFSET.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_select(c)
                                   ->add_from(foo)
                                   ->limit(30)
                                   ->offset(5)
                                   ->as_sql();
    # => "SELECT c FROM foo LIMIT 30 OFFSET 5"



$stmt->add_having(cnt => 2) Add a HAVING clause.



    my $stmt = SQL::Maker::Select->new()
                                   ->add_from(foo)
                                   ->add_select(\COUNT(*) => cnt)
                                   ->add_having(cnt => 2)
                                   ->as_sql();
    # => "SELECT COUNT(*) AS cnt FROM foo HAVING (COUNT(*) = ?)"



SEE ALSO

Data::ObjectDriver::SQL
Search for    or go to Top of page |  Section 3 |  Main Index


perl v5.20.3 SQL::MAKER::SELECT (3) 2014-12-22

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with manServer 1.07.