NAME

SQL::Maker::Select - dynamic SQL generator

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 bind variables.

$stmt->add_select('*')

$stmt->add_select($col => $alias)

$stmt->add_select(\'COUNT(*)' => 'cnt')

Add new select term. It's quote automatically.

$stmt->add_from($table :Str | $select :SQL::Maker::Select) : SQL::Maker::Select

Add new from clause. You can specify the table name or instance of SQL::Maker::Select for 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_id']});

Add new JOIN clause. If you pass arrayref for 'condition' then it uses 'USING'.

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();
$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']});

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 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 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 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 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->add_having(cnt => 2)

Add 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