NAME SQL::QueryBuilder::Flex - Yet another flexible SQL builder SYNOPSIS use SQL::QueryBuilder::Flex 'SQL'; my ($stmt, @bind) = SQL ->select(qw/user_id name/) ->from('user') ->where('user_id = ?', 1) ->to_sql ; # $stmt: SELECT user_id, name FROM user # @bind: (1) my ($stmt, @bind) = SQL ->select( 'user_id', 'now() AS now', ['LEFT(name, ?)', 'name', 5], { name => 'user_name', group_id => 'group_id', }, ) ->from(qw/user u/) ->left_join( SQL ->select('user_id', 'SUM(balance) AS balance') ->from('balance') ->where ->or('group_id = ?', 1) ->or('group_id = ?', 2) ->group_by('user_id') , 'b' )->on ->and('u.user_id = b.user_id') ->and('b.balance > 0') ->where ->and('u.user_id = ?', 7) ->and('b.balance BETWEEN ? AND ?', 100, 200) ->and_exp ->or('group_id = ?', 1) ->or('group_id = ?', 2) ->or_in('group_id', 5, 6) ->parent ->group_by('LEFT(name, ?)', undef, 2) ->order_by(qw/name desc/) ->limit(10) ->to_sql(1) ; # SELECT # user_id, # now() AS now, # LEFT(name, ?) AS name, # group_id AS group_id, # name AS user_name # FROM # user u # LEFT JOIN ( # SELECT # user_id, # SUM(balance) AS balance # FROM # balance # WHERE # group_id = ? # OR group_id = ? # GROUP BY user_id # ) AS b # ON # u.user_id = b.user_id # AND b.balance > 0 # WHERE # u.user_id = ? # AND b.balance BETWEEN ? AND ? # AND ( # group_id = ? # OR group_id = ? # OR group_id IN(?,?) # ) # GROUP BY LEFT(name, ?) # ORDER BY name desc # LIMIT ? # @bind: (5, 1, 2, 7, 100, 200, 1, 2, 5, 6, 2, 10) my ($stmt, @bind) = SQL ->select('name') ->from('user1') ->union( SQL->select('name')->from('user2') ) ->to_sql ; # SELECT name FROM user1 UNION SELECT name FROM user2 my ($stmt, @bind) = SQL ->update('user') ->set( status => 'expired', updated => \'NOW()', ) ->where('(last_activity + INTERVAL ? DAY) < NOW()', 30) ->to_sql ; # UPDATE user SET status=?, updated=NOW() WHERE (last_activity + INTERVAL ? DAY) < NOW() # @bind: ('expired', 30) my ($stmt, @bind) = SQL ->insert('user', name => 'User', status => 'active', updated => \'NOW()', ) ->options(qw/LOW_PRIORITY IGNORE/) ->to_sql ; # INSERT LOW_PRIORITY IGNORE user SET name=?, status=?, updated=NOW() # @bind: ('User', 'active') my $q1 = SQL ->select(qw/user_id balance/) ->from('balance') ; my $q2 = SQL ->select(qw/ name b.balance /) ->from('user') ->left_join($q1, 'b')->using('user_id')->parent ; $q2->where('b.balance < 0') if 1; my ($stmt, @bind) = $q2->to_sql(1); # SELECT # name, # b.balance # FROM # user # LEFT JOIN ( # SELECT # user_id, # balance # FROM # balance # ) AS b # USING (user_id) # WHERE # b.balance < 0 DESCRIPTION SQL::QueryBuilder::Flex is yet another flexible SQL builder. METHODS new(<%options>) Create instance. my $query = SQL::QueryBuilder::Flex->new() to_sql() Build SQL. my ($stmt, @bind) = $query->to_sql() AUTHOR Yuriy Ustushenko, <> COPYRIGHT AND LICENSE Copyright (C) 2014 Yuriy Ustushenko This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.