SQL::Maker::Condition - condition object for SQL::Maker
my $condition = SQL::Maker::Condition->new( name_sep => '.', quote_char => '`', ); $condition->add('foo_id' => 3); $condition->add('bar_id' => 4); $sql = $condition->as_sql(); # (`foo_id`=?) AND (`bar_id`=?) @bind = $condition->bind(); # (3, 4) # add_raw my $condition = SQL::Maker::Condition->new( name_sep => '.', quote_char => '`', ); $condition->add_raw('EXISTS(SELECT * FROM bar WHERE name = ?)' => ['john']); $condition->add_raw('type IS NOT NULL'); $sql = $condition->as_sql(); # (EXISTS(SELECT * FROM bar WHERE name = ?)) AND (type IS NOT NULL) @bind = $condition->bind(); # ('john') # composite and my $other = SQL::Maker::Condition->new( name_sep => '.', quote_char => '`', ); $other->add('name' => 'john'); my $comp_and = $condition & $other; $sql = $comp_and->as_sql(); # ((`foo_id`=?) AND (`bar_id`=?)) AND (`name`=?) @bind = $comp_and->bind(); # (3, 4, 'john') # composite or my $comp_or = $condition | $other; $sql = $comp_and->as_sql(); # ((`foo_id`=?) AND (`bar_id`=?)) OR (`name`=?) @bind = $comp_and->bind(); # (3, 4, 'john')
Here is a cheat sheet for conditions.
IN: ['foo','bar'] OUT QUERY: '`foo` = ?' OUT BIND: ('bar') IN: ['foo',['bar','baz']] OUT QUERY: '`foo` IN (?, ?)' OUT BIND: ('bar','baz') IN: ['foo',{'IN' => ['bar','baz']}] OUT QUERY: '`foo` IN (?, ?)' OUT BIND: ('bar','baz') IN: ['foo',{'not IN' => ['bar','baz']}] OUT QUERY: '`foo` NOT IN (?, ?)' OUT BIND: ('bar','baz') IN: ['foo',{'!=' => 'bar'}] OUT QUERY: '`foo` != ?' OUT BIND: ('bar') IN: ['foo',\'IS NOT NULL'] OUT QUERY: '`foo` IS NOT NULL' OUT BIND: () IN: ['foo',{'between' => ['1','2']}] OUT QUERY: '`foo` BETWEEN ? AND ?' OUT BIND: ('1','2') IN: ['foo',{'like' => 'xaic%'}] OUT QUERY: '`foo` LIKE ?' OUT BIND: ('xaic%') IN: ['foo',[{'>' => 'bar'},{'<' => 'baz'}]] OUT QUERY: '(`foo` > ?) OR (`foo` < ?)' OUT BIND: ('bar','baz') IN: ['foo',['-and',{'>' => 'bar'},{'<' => 'baz'}]] OUT QUERY: '(`foo` > ?) AND (`foo` < ?)' OUT BIND: ('bar','baz') IN: ['foo',['-and','foo','bar','baz']] OUT QUERY: '(`foo` = ?) AND (`foo` = ?) AND (`foo` = ?)' OUT BIND: ('foo','bar','baz') IN: ['foo_id',\['IN (SELECT foo_id FROM bar WHERE t=?)',44]] OUT QUERY: '`foo_id` IN (SELECT foo_id FROM bar WHERE t=?)' OUT BIND: ('44') IN: ['foo_id', {IN => \['SELECT foo_id FROM bar WHERE t=?',44]}] OUT QUERY: '`foo_id` IN (SELECT foo_id FROM bar WHERE t=?)' OUT BIND: ('44') IN: ['foo_id',\['MATCH (col1, col2) AGAINST (?)','apples']] OUT QUERY: '`foo_id` MATCH (col1, col2) AGAINST (?)' OUT BIND: ('apples') IN: ['foo_id',undef] OUT QUERY: '`foo_id` IS NULL' OUT BIND: () IN: ['foo_id',{'IN' => []}] OUT QUERY: '0=1' OUT BIND: () IN: ['foo_id',{'NOT IN' => []}] OUT QUERY: '1=1' OUT BIND: () IN: ['foo_id', [123,sql_type(\3, SQL_INTEGER)]] OUT QUERY: '`foo_id` IN (?, ?)' OUT BIND: (123, sql_type(\3, SQL_INTEGER)) IN: ['foo_id', sql_type(\3, SQL_INTEGER)] OUT QUERY: '`foo_id` = ?' OUT BIND: sql_type(\3, SQL_INTEGER) IN: ['created_on', { '>', \'DATE_SUB(NOW(), INTERVAL 1 DAY)' }] OUT QUERY: '`created_on` > DATE_SUB(NOW(), INTERVAL 1 DAY)' OUT BIND:
It is also possible to use the functions exported by SQL::QueryMaker to define the conditions.
SQL::QueryMaker
IN: ['foo' => sql_in(['bar','baz'])] OUT QUERY: '`foo` IN (?,?)' OUT BIND: ('bar','baz') IN: ['foo' => sql_lt(3)] OUT QUERY: '`foo` < ?' OUT BIND: (3) IN: ['foo' => sql_not_in(['bar','baz'])] OUT QUERY: '`foo` NOT IN (?,?)' OUT BIND: ('bar','baz') IN: ['foo' => sql_ne('bar')] OUT QUERY: '`foo` != ?' OUT BIND: ('bar') IN: ['foo' => sql_is_not_null()] OUT QUERY: '`foo` IS NOT NULL' OUT BIND: () IN: ['foo' => sql_between('1','2')] OUT QUERY: '`foo` BETWEEN ? AND ?' OUT BIND: ('1','2') IN: ['foo' => sql_like('xaic%')] OUT QUERY: '`foo` LIKE ?' OUT BIND: ('xaic%') IN: ['foo' => sql_or([sql_gt('bar'), sql_lt('baz')])] OUT QUERY: '(`foo` > ?) OR (`foo` < ?)' OUT BIND: ('bar','baz') IN: ['foo' => sql_and([sql_gt('bar'), sql_lt('baz')])] OUT QUERY: '(`foo` > ?) AND (`foo` < ?)' OUT BIND: ('bar','baz') IN: ['foo_id' => sql_op('IN (SELECT foo_id FROM bar WHERE t=?)',[44])] OUT QUERY: '`foo_id` IN (SELECT foo_id FROM bar WHERE t=?)' OUT BIND: ('44') IN: ['foo_id' => sql_in([sql_raw('SELECT foo_id FROM bar WHERE t=?',44)])] OUT QUERY: '`foo_id` IN ((SELECT foo_id FROM bar WHERE t=?))' OUT BIND: ('44') IN: ['foo_id', => sql_op('MATCH (@) AGAINST (?)',['apples'])] OUT QUERY: 'MATCH (`foo_id`) AGAINST (?)' OUT BIND: ('apples') IN: ['foo_id',undef] OUT QUERY: '`foo_id` IS NULL' OUT BIND: () IN: ['foo_id',sql_in([])] OUT QUERY: '0=1' OUT BIND: () IN: ['foo_id',sql_not_in([])] OUT QUERY: '1=1' OUT BIND: () IN: ['foo_id', sql_type(\3, SQL_INTEGER)] OUT QUERY: '`foo_id` = ?' OUT BIND: sql_type(\3, SQL_INTEGER) IN: ['foo_id', sql_in([sql_type(\3, SQL_INTEGER)])] OUT QUERY: '`foo_id` IN (?)' OUT BIND: sql_type(\3, SQL_INTEGER) IN: ['created_on', sql_gt(sql_raw('DATE_SUB(NOW(), INTERVAL 1 DAY)')) ] OUT QUERY: '`created_on` > DATE_SUB(NOW(), INTERVAL 1 DAY)' OUT BIND:
SQL::Maker
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.