SQL::Maker::Select - dynamic SQL generator
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"
Render the SQL string.
Get bind variables.
Add new select term. It's quote automatically.
Add new from clause. You can specify the table name or instance of SQL::Maker::Select for sub-query.
Return: $stmt itself.
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";
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)"
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 (?, ?, ?))"
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 (?, ?, ?)))"
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"
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"
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(*) = ?)"
Data::ObjectDriver::SQL
To install SQL::Maker, copy and paste the appropriate command in to your terminal.
cpanm
cpanm SQL::Maker
CPAN shell
perl -MCPAN -e shell install SQL::Maker
For more information on module installation, please visit the detailed CPAN module installation guide.