From Code to Community: Sponsoring The Perl and Raku Conference 2025 Learn more

 / 
SQL-QueryMaker-0.03
River stage two • 5 direct dependents • 46 total dependents
/ SQL::QueryMaker

NAME

SQL::QueryMaker - helper functions for SQL query generation

SYNOPSIS


            

              
              
my $query = sql_eq(foo => $v);
$query->as_sql;                 # `foo`=?
$query->bind;                   # ($v)
my $query = sql_lt(foo => $v);
$query->as_sql;                 # `foo`<?
$query->bind;                   # ($v)
my $query = sql_in(foo => [
    $v1, $v2, $v3,
]);
$query->as_sql;                 # `foo` IN (?,?,?)
$query->bind;                   # ($v1,$v2,$v3)
my $query = sql_and(foo => [
    sql_ge($min),
    sql_lt($max)
]);
$query->as_sql;                 # `foo`>=? AND `foo`<?
$query->bind;                   # ($min,$max)
my $query = sql_and([
    sql_eq(foo => $v1),
    sql_eq(bar => $v2)
];
$query->as_sql;                 # `foo`=? AND `bar`=?
$query->bind;                   # ($v1,$v2)
my $query = sql_and([
    foo => $v1,
    bar => sql_lt($v2),
]);
$query->as_sql;                 # `foo`=? AND `bar`<?
$query->bind;                   # ($v1,$v2)

DESCRIPTION

This module concentrates on providing an expressive, concise way to declare SQL expressions by exporting carefully-designed functions. It is possible to use the module to generate SQL query conditions and pass them as arguments to other more versatile query builders such as SQL::Maker.

The functions exported by the module instantiate comparator objects that build SQL expressions when their as_sql method are being invoked. There are two ways to specify the names of the columns to the comparator; to pass in the names as argument or to specify then as an argument to the as_sql method.

FUNCTIONS

sql_eq([$column,] $value)

sql_ne([$column,] $value)

sql_lt([$column,] $value)

sql_gt([$column,] $value)

sql_le([$column,] $value)

sql_ge([$column,] $value)

sql_like([$column,] $value)

sql_is_null([$column])

sql_is_not_null([$column])

sql_not([$column])

sql_between([$column,] $min_value, $max_value)

sql_not_between([$column,] $min_value, $max_value)

sql_in([$column,] \@values)

sql_not_in([$column,] \@values)

Instantiates a comparator object that tests a column against given value(s).

sql_and([$column,] \@conditions)

sql_or([$ column,] \@conditions)

Aggregates given comparator objects into a logical expression.

If specified, the column name is pushed down to the arguments when the as_sql method is being called, as show in the second example below.


            

              
              
sql_and([                   # => `foo`=? AND `bar`<?
    sql_eq("foo" => $v1),
    sql_lt("bar" => $v2)
])
sql_and("foo" => [          # => `foo`>=$min OR `foo`<$max
    sql_ge($min),
    sql_lt($max),
])

sql_and(\%conditions)

sql_or(\%conditions)

Aggregates given pairs of column names and comparators into a logical expression.

The value part is composed of as the argument to the = operator if it is not a blessed reference.


            

              
              
my $query = sql_and({
    foo => 'abc',
    bar => sql_lt(123),
});
$query->as_sql;             # => `foo`=? AND bar<?
$query->bind;               # => ('abc', 123)

sql_op([$column,] $op_sql, \@bind_values)

Generates a comparator object that tests a column using the given SQL and values. <@> in the given SQL are replaced by the column name (specified either by the argument to the function or later by the call to the <as_sql> method), and <?> are substituted by the given bind values.

sql_raw($sql, @bind_values)

Generates a comparator object from raw SQL and bind values. <?> in the given SQL are replaced by the bind values.

$obj->as_sql()

$obj->as_sql($column_name)

$obj->as_sql($column_name, $quote_identifier_cb)

Compiles given comparator object and returns an SQL expression. Corresponding bind values should be obtained by calling the bind method.

The function optionally accepts a column name to which the comparator object should be bound; an error is thrown if the comparator object is already bound to another column.

The function also accepts a callback for quoting the identifiers. If omitted, the identifiers are quoted using ` after being splitted using .; i.e. a column designated as foo.bar is quoted as `foo`.`bar`.

$obj->bind()

Returns a list of bind values corresponding to the SQL expression returned by the as_sql method.

CHEAT SHEET


            

              
              
IN:        sql_eq('foo' => 'bar')
OUT QUERY: '`foo` = ?'
OUT BIND:  ('bar')
IN:        sql_ne('foo' => 'bar')
OUT QUERY: '`foo` != ?'
OUT BIND:  ('bar')
IN:        sql_in('foo' => ['bar', 'baz'])
OUT QUERY: '`foo` IN (?,?)'
OUT BIND:  ('bar','baz')
IN:        sql_and([sql_eq('foo' => 'bar'), sql_eq('baz' => 123)])
OUT QUERY: '(`foo` = ?) AND (`baz` = ?)'
OUT BIND:  ('bar',123)
IN:        sql_and('foo' => [sql_ge(3), sql_lt(5)])
OUT QUERY: '(`foo` >= ?) AND (`foo` < ?)'
OUT BIND:  (3,5)
IN:        sql_or([sql_eq('foo' => 'bar'), sql_eq('baz' => 123)])
OUT QUERY: '(`foo` = ?) OR (`baz` = ?)'
OUT BIND:  ('bar',123)
IN:        sql_or('foo' => ['bar', 'baz'])
OUT QUERY: '(`foo` = ?) OR (`foo` = ?)'
OUT BIND:  ('bar','baz')
IN:        sql_is_null('foo')
OUT QUERY: '`foo` IS NULL'
OUT BIND:  ()
IN:        sql_is_not_null('foo')
OUT QUERY: '`foo` IS NOT NULL'
OUT BIND:  ()
IN:        sql_between('foo', 1, 2)
OUT QUERY: '`foo` BETWEEN ? AND ?'
OUT BIND:  (1,2)
IN:        sql_not('foo')
OUT QUERY: 'NOT `foo`'
OUT BIND:  ()
IN:        sql_op('apples', 'MATCH (@) AGAINST (?)', ['oranges'])
OUT QUERY: 'MATCH (`apples`) AGAINST (?)'
OUT BIND:  ('oranges')
IN:        sql_raw('SELECT * FROM t WHERE id=?',123)
OUT QUERY: 'SELECT * FROM t WHERE id=?'
OUT BIND:  (123)
IN:        sql_in('foo', => [123,sql_raw('SELECT id FROM t WHERE cat=?',5)])
OUT QUERY: '`foo` IN (?,(SELECT id FROM t WHERE cat=?))'
OUT BIND:  (123,5)

AUTHOR

Kazuho Oku

SEE ALSO

SQL::Abstract SQL::Maker

LICENSE

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, or under the MIT License.