SQL::Composer::Select - SELECT statement

SYNOPSIS

    my $expr =
      SQL::Composer::Select->new(from => 'table', columns => ['a', 'b']);

    my $sql = $expr->to_sql;        # 'SELECT `table`.`a`,`table`.`b` FROM `table`'
    my @bind = $expr->to_bind;      # []

    $expr->from_rows([['c', 'd']]); # [{a => 'c', b => 'd'}]

DESCRIPTION

Builds SELECT statement and converts (from_rows()) received arrayref data to hashref with appropriate column names as keys and joins as nested values.

Select column with AS

    my $expr = SQL::Composer::Select->new(
        from    => 'table',
        columns => [{-col => 'foo' => -as => 'bar'}]
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`foo` AS `bar` FROM `table`'
    my @bind = $expr->to_bind; # []
    $expr->from_rows([['c']]); # [{bar => 'c'}]

Select column with raw SQL

    my $expr =
      SQL::Composer::Select->new(from => 'table', columns => [\'COUNT(*)']);

    my $sql = $expr->to_sql;   # 'SELECT COUNT(*) FROM `table`'
    my @bind = $expr->to_bind; # [];
    $expr->from_rows([['c']]); # [{'COUNT(*)' => 'c'}]

Select with WHERE

For more details see SQL::Composer::Expression.

    my $expr = SQL::Composer::Select->new(
        from    => 'table',
        columns => ['a', 'b'],
        where   => [a => 'b']
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #        FROM `table` WHERE `table`.`a` = ?'
    my @bind = $expr->to_bind; # ['b']

GROUP BY

    my $expr = SQL::Composer::Select->new(
        from    => 'table',
        columns => ['a', 'b'],
        group_by => 'a'
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #        FROM `table` GROUP BY `table`.`a`'
    my @bind = $expr->to_bind; # []

ORDER BY

    my $expr = SQL::Composer::Select->new(
        from     => 'table',
        columns  => ['a', 'b'],
        order_by => 'foo'
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #        FROM `table` ORDER BY `foo`'
    my @bind = $expr->to_bind; # []

ORDER BY with sorting order

    my $expr = SQL::Composer::Select->new(
        from     => 'table',
        columns  => ['a', 'b'],
        order_by => [foo => 'desc', bar => 'asc']
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #      FROM `table`
                               #      ORDER BY `table`.`foo` DESC,
                               #               `table`.`bar` ASC'
    my @bind = $expr->to_bind; # []

LIMIT and OFFSET

    my $expr = SQL::Composer::Select->new(
        from    => 'table',
        columns => ['a', 'b'],
        limit   => 5,
        offset  => 10
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #        FROM `table` LIMIT 5 OFFSET 10'
    my @bind = $expr->to_bind; # [];

JOIN

For more details see SQL::Composer::Join.

    my $expr = SQL::Composer::Select->new(
        from    => 'table',
        columns => ['a'],
        join    => [
            {
                source  => 'table2',
                columns => ['b'],
                on      => [a => '1'],
                join    => [
                    {
                        source  => 'table3',
                        columns => ['c'],
                        on      => [b => '2']
                    }
                ]
            }
        ]
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table2`.`b`,`table3`.`c
                               #    FROM `table`
                               #    JOIN `table2` ON `table2`.`a` = ?
                               #    JOIN `table3` ON `table3`.`b` = ?'
    my @bind = $expr->to_bind; # ['1', '2'];

    $expr->from_rows([['c', 'd', 'e']]);
    # [{a => 'c', table2 => {b => 'd', table3 => {c => 'e'}}}];

FOR UPDATE

    my $expr = SQL::Composer::Select->new(
        from       => 'table',
        columns    => ['a', 'b'],
        for_update => 1
    );

    my $sql = $expr->to_sql;   # 'SELECT `table`.`a`,`table`.`b`
                               #    FROM `table` FOR UPDATE'
    my @bind = $expr->to_bind; # []
};