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; # []
};