- query PARAMS
- The query parameters, passed as a reference to an array of
name/value pairs, scalar references, or array references. PARAMS may
include an arbitrary list of selection parameters used to modify the
"WHERE" clause of the SQL select statement. Any query parameter
that is not in one of the forms described below will cause a fatal error.
Valid selection parameters are described below, along with the SQL clause
they add to the select statement.
Simple equality:
'NAME' => "foo" # COLUMN = 'foo'
'!NAME' => "foo" # NOT(COLUMN = 'foo')
'NAME' => [ "a", "b" ] # COLUMN IN ('a', 'b')
'!NAME' => [ "a", "b" ] # COLUMN NOT(IN ('a', 'b'))
Is/is not null:
'NAME' => undef # COLUMN IS NULL
'!NAME' => undef # COLUMN IS NOT NULL
'NAME' => { eq => undef } # COLUMN IS NULL
'NAME' => { ne => undef } # COLUMN IS NOT NULL
Comparisons:
NAME => { OP => "foo" } # COLUMN OP 'foo'
# (COLUMN OP 'foo' OR COLUMN OP 'goo')
NAME => { OP => [ "foo", "goo" ] }
"OP" can be any of the following:
OP SQL operator
------------- ------------
similar SIMILAR TO
match ~
imatch ~*
regex, regexp REGEXP
like LIKE
ilike ILIKE
rlike RLIKE
is IS
is_not IS NOT
ne <>
eq =
lt <
gt >
le <=
ge >=
Ranges:
NAME => { between => [ 1, 99 ] } # COLUMN BETWEEN 1 AND 99
NAME => { gt_lt => [ 1, 99 ] } # (COLUMN > 1 AND < 99)
NAME => { gt_le => [ 1, 99 ] } # (COLUMN > 1 AND <= 99)
NAME => { ge_lt => [ 1, 99 ] } # (COLUMN >= 1 AND < 99)
NAME => { ge_le => [ 1, 99 ] } # (COLUMN >= 1 AND <= 99)
If a value is a reference to a scalar, that scalar is "inlined"
without any quoting.
'NAME' => \"foo" # COLUMN = foo
'NAME' => [ "a", \"b" ] # COLUMN IN ('a', b)
Undefined values are translated to the keyword NULL when included in a
multi-value comparison.
'NAME' => [ "a", undef ] # COLUMN IN ('a', NULL)
Set operations:
### Informix (default) ###
# A IN COLUMN
'NAME' => { in_set => 'A' }
# NOT(A IN COLUMN)
'!NAME' => { in_set => 'A' }
# (A IN COLUMN OR B IN COLUMN)
'NAME' => { in_set => [ 'A', 'B'] }
'NAME' => { any_in_set => [ 'A', 'B'] }
# NOT(A IN COLUMN) AND NOT(B IN COLUMN)
'!NAME' => { in_set => [ 'A', 'B'] }
'!NAME' => { any_in_set => [ 'A', 'B'] }
# (A IN COLUMN AND B IN COLUMN)
'NAME' => { all_in_set => [ 'A', 'B'] }
# NOT(A IN COLUMN AND B IN COLUMN)
'!NAME' => { all_in_set => [ 'A', 'B'] }
### MySQL (requires db parameter) ###
# FIND_IN_SET(A, COLUMN) > 0
'NAME' => { in_set => 'A' }
# NOT(FIND_IN_SET(A, COLUMN) > 0)
'!NAME' => { in_set => 'A' }
# (FIND_IN_SET(A, COLUMN) > 0 OR FIND_IN_SET(B, COLUMN) > 0)
'NAME' => { in_set => [ 'A', 'B'] }
'NAME' => { any_in_set => [ 'A', 'B'] }
# NOT(FIND_IN_SET(A, COLUMN) > 0) AND NOT(FIND_IN_SET(B, COLUMN) > 0)
'!NAME' => { in_set => [ 'A', 'B'] }
'!NAME' => { any_in_set => [ 'A', 'B'] }
# (FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
'NAME' => { all_in_set => [ 'A', 'B'] }
# NOT(FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
'!NAME' => { all_in_set => [ 'A', 'B'] }
Array operations:
# A = ANY(COLUMN)
'NAME' => { in_array => 'A' }
# NOT(A = ANY(COLUMN))
'!NAME' => { in_array => 'A' }
# (A = ANY(COLUMN) OR B = ANY(COLUMN))
'NAME' => { in_array => [ 'A', 'B'] }
'NAME' => { any_in_array => [ 'A', 'B'] }
# NOT(A = ANY(COLUMN) OR B = ANY(COLUMN))
'!NAME' => { in_array => [ 'A', 'B'] }
'!NAME' => { any_in_array => [ 'A', 'B'] }
# (A = ANY(COLUMN) AND B = ANY(COLUMN))
'NAME' => { all_in_array => [ 'A', 'B'] }
# NOT(A = ANY(COLUMN) AND B = ANY(COLUMN))
'!NAME' => { all_in_array => [ 'A', 'B'] }
PostgreSQL ltree operations:
OP SQL operator
------------- ------------
ltree_ancestor @>
ltree_descendant <@
ltree_query ~
ltree_ltxtquery @
ltree_concat ||
Any of the operations described above can have "_sql" appended to
indicate that the corresponding values are to be "inlined"
(i.e., included in the SQL query as-is, with no quoting of any kind). This
is useful for comparing two columns. For example, this query:
query => [ legs => { gt_sql => 'eyes' } ]
would produce this SQL:
SELECT ... FROM animals WHERE legs > eyes
where "legs" and "eyes" are both left unquoted.
The same NAME string may be repeated multiple times. (This is the primary
reason that the query is a reference to an array of name/value
pairs, rather than a reference to a hash, which would only allow each NAME
once.) Example:
query =>
[
age => { gt => 10 },
age => { lt => 20 },
]
The string "NAME" can take many forms, each of which eventually
resolves to a database column (COLUMN in the examples above).
Literal SQL can be included by providing a reference to a scalar:
\'mycol > 123'
To use placeholders and bind values, pass a reference to an array containing
a scalar reference to the literal SQL with placeholders as the first item,
followed by a list of values to bind:
[ \'mycol > ?' => 123 ]
Un-prefixed column or method names that are ambiguous (i.e., exist in more than
one of the tables being queried) are considered to be part of the primary
table ("t1").
Finally, in the case of apparently intractable ambiguity, like when a table name
is the same as another table's alias, remember that you can always use the
"tn_"-prefixed column name aliases, which are unique within a given
query.
All of these clauses are joined by logic (default: "AND") in the final
query. Example:
$sql = build_select
(
dbh => $dbh,
select => 'id, title',
tables => [ 'articles' ],
columns => { articles => [ qw(id category type title) ] },
query =>
[
category => [ 'sports', 'science' ],
type => 'news',
title => { like => [ '%million%',
'%resident%' ] },
],
query_is_sql => 1);
The above returns an SQL statement something like this:
SELECT id, title FROM articles WHERE
category IN ('sports', 'science')
AND
type = 'news'
AND
(title LIKE '%million%' OR title LIKE '%resident%')
LIMIT 5
Nested boolean logic is possible using the special keywords "and" and
"or" (case insensitive). Example:
$sql = build_select
(
dbh => $dbh,
select => 'id, title',
tables => [ 'articles' ],
columns => { articles => [ qw(id category type title) ] },
query =>
[
or =>
[
and => [ category => undef, type => 'aux' ],
category => [ 'sports', 'science' ],
],
type => 'news',
title => { like => [ '%million%',
'%resident%' ] },
],
query_is_sql => 1);
which returns an SQL statement something like this:
SELECT id, title FROM articles WHERE
(
(
category IS NULL AND
type = 'aux'
)
OR category IN ('sports', 'science')
)
AND
type = 'news'
AND
(title LIKE '%million%' OR title LIKE '%resident%')
The "and" and "or" keywords can be used multiple times
within a query (just like all other NAME specifiers described earlier) and can
be arbitrarily nested.
If you have a column named "and" or "or", you'll have to use
the fully-qualified (table.column) or alias-qualified (tN.column) forms in
order to address that column.
If query_is_sql is false or omitted, all of the parameter values are passed
through the "parse_value()" and "format_value()" methods
of their corresponding Rose::DB::Object::Metadata::Column-dervied column
objects.
If a column object returns true from its
manager_uses_method() method,
then its parameter value is passed through the corresponding
Rose::DB::Object-derived object method instead.
Example:
$dt = DateTime->new(year => 2001, month => 1, day => 31);
$sql = build_select
(
db => $db,
select => 'id, category',
tables => [ 'articles' ],
columns => { articles => [ qw(id category type date) ] },
classes => { articles => 'Article' },
query =>
[
type => 'news',
date => { lt => '12/25/2003 8pm' },
date => { gt => $dt },
],
sort_by => 'id DESC, category',
limit => 5);
The above returns an SQL statement something like this:
SELECT id, category FROM articles WHERE
type = 'news'
AND
date < '2003-12-25 20:00:00'
AND
date > '2001-01-31 00:00:00'
ORDER BY id DESC, category
LIMIT 5
Finally, here's an example using more than one table:
$dt = DateTime->new(year => 2001, month => 1, day => 31);
$sql = build_select
(
db => $db,
tables => [ 'articles', 'categories' ],
columns =>
{
articles => [ qw(id name category_id date) ],
categories => [ qw(id name description) ],
},
classes =>
{
articles => 'Article',
categories => 'Category',
},
query =>
[
'!t1.name' => { like => '%foo%' },
t2.name => 'news',
date => { lt => '12/25/2003 8pm' },
date => { gt => $dt },
],
clauses =>
[
't1.category_id = t2.id',
],
sort_by => 'articles.name DESC, t2.name',
limit => 5);
The above returns an SQL statement something like this:
SELECT
t1.id,
t1.name,
t1.category_id,
t1.date,
t2.id,
t2.name,
t2.description
FROM
articles t1,
categories t2
WHERE
t1.category_id = t2.id
AND
NOT(t1.name LIKE '%foo%')
AND
t2.name = 'news'
AND
t1.date < '2003-12-25 20:00:00'
AND
t1.date > '2001-01-31 00:00:00'
ORDER BY articles.name DESC, t2.name
LIMIT 5