1Rose::DB::Object::QueryUBsueirldCeorn(t3r)ibuted Perl DoRcousmee:n:tDaBt:i:oOnbject::QueryBuilder(3)
2
6 Rose::DB::Object::QueryBuilder - Build SQL queries on behalf of
7 Rose::DB::Object::Manager.
8
10 use Rose::DB::Object::QueryBuilder qw(build_select);
11 # Build simple query
13 $sql = build_select
14 (
15 dbh => $dbh,
16 select => 'COUNT(*)',
17 tables => [ 'articles' ],
18 columns => { articles => [ qw(id category type title date) ] },
19 query =>
20 [
21 category => [ 'sports', 'science' ],
22 type => 'news',
23 title => { like => [ '%million%',
24 '%resident%' ] },
25 ],
26 query_is_sql => 1);
27 $sth = $dbh->prepare($sql);
29 $sth->execute;
30 $count = $sth->fetchrow_array;
31 ...
33 # Return query with placeholders, plus bind values
35 ($sql, $bind) = build_select
36 (
37 dbh => $dbh,
38 tables => [ 'articles' ],
39 columns => { articles => [ qw(id category type title date) ] },
40 query =>
41 [
42 category => [ 'sports', 'science' ],
43 type => 'news',
44 title => { like => [ '%million%',
45 '%resident%' ] },
46 ],
47 query_is_sql => 1,
48 sort_by => 'title DESC, category',
49 limit => 5);
50 $sth = $dbh->prepare($sql);
52 $sth->execute(@$bind);
53 while($row = $sth->fetchrow_hashref) { ... }
55 ...
57 # Coerce query values into the right format
59 ($sql, $bind) = build_select
60 (
61 db => $db,
62 tables => [ 'articles' ],
63 columns => { articles => [ qw(id category type title date) ] },
64 classes => { articles => 'Article' },
65 query =>
66 [
67 type => 'news',
68 date => { lt => 'now' },
69 date => { gt => DateTime->new(...) },
70 ],
71 sort_by => 'title DESC, category',
72 limit => 5);
73 $sth = $dbh->prepare($sql);
75 $sth->execute(@$bind);
76
78 Rose::DB::Object::QueryBuilder is used to build SQL queries, primarily
79 in service of the Rose::DB::Object::Manager class. It (optionally)
80 exports two functions: build_select() and build_where_clause().
81
83 build_select PARAMS
84 Returns an SQL "select" query string (in scalar context) or an SQL
85 "select" query string with placeholders and a reference to an array
86 of bind values (in list context) constructed based on PARAMS.
87 Valid PARAMS are described below.
88 clauses CLAUSES
90 A reference to an array of extra SQL clauses to add to the
91 "WHERE" portion of the query string. This is the obligatory
92 "escape hatch" for clauses that are not supported by arguments
93 to the query parameter.
94 columns HASHREF
96 A reference to a hash keyed by table name, each of which points
97 to a reference to an array of the names of the columns in that
98 table. Example:
99 $sql = build_select(columns =>
101 {
102 table1 => [ 'col1', 'col2', ... ],
103 table2 => [ 'col1', 'col2', ... ],
104 ...
105 });
106 This argument is required.
108 db DB
110 A Rose::DB-derived object. This argument is required if
111 query_is_sql is false or omitted.
112 dbh DBH
114 A DBI database handle already connected to the correct
115 database. If this argument is omitted, an attempt will be made
116 to extract a database handle from the db argument. If this
117 fails, or if there is no db argument, a fatal error will occur.
118 group_by CLAUSE
120 A fully formed SQL "GROUP BY ..." clause, sans the words "GROUP
121 BY", or a reference to an array of strings to be joined with a
122 comma and appended to the "GROUP BY" clause.
123 limit NUMBER
125 A number to use in the "LIMIT ..." clause.
126 logic LOGIC
128 A string indicating the logic that will be used to join the
129 statements in the WHERE clause. Valid values for LOGIC are
130 "AND" and "OR". If omitted, it defaults to "AND".
131 pretty BOOL
133 If true, the SQL returned will have slightly nicer formatting.
134 query PARAMS
136 The query parameters, passed as a reference to an array of
137 name/value pairs, scalar references, or array references.
138 PARAMS may include an arbitrary list of selection parameters
139 used to modify the "WHERE" clause of the SQL select statement.
140 Any query parameter that is not in one of the forms described
141 below will cause a fatal error.
142 Valid selection parameters are described below, along with the
144 SQL clause they add to the select statement.
145 Simple equality:
147 'NAME' => "foo" # COLUMN = 'foo'
149 '!NAME' => "foo" # NOT(COLUMN = 'foo')
150 'NAME' => [ "a", "b" ] # COLUMN IN ('a', 'b')
152 '!NAME' => [ "a", "b" ] # COLUMN NOT(IN ('a', 'b'))
153 Is/is not null:
155 'NAME' => undef # COLUMN IS NULL
157 '!NAME' => undef # COLUMN IS NOT NULL
158 'NAME' => { eq => undef } # COLUMN IS NULL
160 'NAME' => { ne => undef } # COLUMN IS NOT NULL
161 Comparisons:
163 NAME => { OP => "foo" } # COLUMN OP 'foo'
165 # (COLUMN OP 'foo' OR COLUMN OP 'goo')
167 NAME => { OP => [ "foo", "goo" ] }
168 "OP" can be any of the following:
170 OP SQL operator
172 ------------- ------------
173 similar SIMILAR TO
174 match ~
175 imatch ~*
176 regex, regexp REGEXP
177 like LIKE
178 ilike ILIKE
179 rlike RLIKE
180 is IS
181 is_not IS NOT
182 ne <>
183 eq =
184 lt <
185 gt >
186 le <=
187 ge >=
188 Ranges:
190 NAME => { between => [ 1, 99 ] } # COLUMN BETWEEN 1 AND 99
192 NAME => { gt_lt => [ 1, 99 ] } # (COLUMN > 1 AND < 99)
194 NAME => { gt_le => [ 1, 99 ] } # (COLUMN > 1 AND <= 99)
195 NAME => { ge_lt => [ 1, 99 ] } # (COLUMN >= 1 AND < 99)
196 NAME => { ge_le => [ 1, 99 ] } # (COLUMN >= 1 AND <= 99)
197 If a value is a reference to a scalar, that scalar is "inlined"
199 without any quoting.
200 'NAME' => \"foo" # COLUMN = foo
202 'NAME' => [ "a", \"b" ] # COLUMN IN ('a', b)
203 Undefined values are translated to the keyword NULL when
205 included in a multi-value comparison.
206 'NAME' => [ "a", undef ] # COLUMN IN ('a', NULL)
208 Set operations:
210 ### Informix (default) ###
212 # A IN COLUMN
214 'NAME' => { in_set => 'A' }
215 # NOT(A IN COLUMN)
217 '!NAME' => { in_set => 'A' }
218 # (A IN COLUMN OR B IN COLUMN)
220 'NAME' => { in_set => [ 'A', 'B'] }
221 'NAME' => { any_in_set => [ 'A', 'B'] }
222 # NOT(A IN COLUMN) AND NOT(B IN COLUMN)
224 '!NAME' => { in_set => [ 'A', 'B'] }
225 '!NAME' => { any_in_set => [ 'A', 'B'] }
226 # (A IN COLUMN AND B IN COLUMN)
228 'NAME' => { all_in_set => [ 'A', 'B'] }
229 # NOT(A IN COLUMN AND B IN COLUMN)
231 '!NAME' => { all_in_set => [ 'A', 'B'] }
232 ### MySQL (requires db parameter) ###
234 # FIND_IN_SET(A, COLUMN) > 0
236 'NAME' => { in_set => 'A' }
237 # NOT(FIND_IN_SET(A, COLUMN) > 0)
239 '!NAME' => { in_set => 'A' }
240 # (FIND_IN_SET(A, COLUMN) > 0 OR FIND_IN_SET(B, COLUMN) > 0)
242 'NAME' => { in_set => [ 'A', 'B'] }
243 'NAME' => { any_in_set => [ 'A', 'B'] }
244 # NOT(FIND_IN_SET(A, COLUMN) > 0) AND NOT(FIND_IN_SET(B, COLUMN) > 0)
246 '!NAME' => { in_set => [ 'A', 'B'] }
247 '!NAME' => { any_in_set => [ 'A', 'B'] }
248 # (FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
250 'NAME' => { all_in_set => [ 'A', 'B'] }
251 # NOT(FIND_IN_SET(A, COLUMN) > 0 AND FIND_IN_SET(B, COLUMN) > 0)
253 '!NAME' => { all_in_set => [ 'A', 'B'] }
254 Array operations:
256 # A = ANY(COLUMN)
258 'NAME' => { in_array => 'A' }
259 # NOT(A = ANY(COLUMN))
261 '!NAME' => { in_array => 'A' }
262 # (A = ANY(COLUMN) OR B = ANY(COLUMN))
264 'NAME' => { in_array => [ 'A', 'B'] }
265 'NAME' => { any_in_array => [ 'A', 'B'] }
266 # NOT(A = ANY(COLUMN) OR B = ANY(COLUMN))
268 '!NAME' => { in_array => [ 'A', 'B'] }
269 '!NAME' => { any_in_array => [ 'A', 'B'] }
270 # (A = ANY(COLUMN) AND B = ANY(COLUMN))
272 'NAME' => { all_in_array => [ 'A', 'B'] }
273 # NOT(A = ANY(COLUMN) AND B = ANY(COLUMN))
275 '!NAME' => { all_in_array => [ 'A', 'B'] }
276 PostgreSQL ltree operations:
278 OP SQL operator
280 ------------- ------------
281 ltree_ancestor @>
282 ltree_descendant <@
283 ltree_query ~
284 ltree_ltxtquery @
285 ltree_concat ||
286 Any of the operations described above can have "_sql" appended
288 to indicate that the corresponding values are to be "inlined"
289 (i.e., included in the SQL query as-is, with no quoting of any
290 kind). This is useful for comparing two columns. For example,
291 this query:
292 query => [ legs => { gt_sql => 'eyes' } ]
294 would produce this SQL:
296 SELECT ... FROM animals WHERE legs > eyes
298 where "legs" and "eyes" are both left unquoted.
300 The same NAME string may be repeated multiple times. (This is
302 the primary reason that the query is a reference to an array of
303 name/value pairs, rather than a reference to a hash, which
304 would only allow each NAME once.) Example:
305 query =>
307 [
308 age => { gt => 10 },
309 age => { lt => 20 },
310 ]
311 The string "NAME" can take many forms, each of which eventually
313 resolves to a database column (COLUMN in the examples above).
314 Literal SQL can be included by providing a reference to a
316 scalar:
317 \'mycol > 123'
319 To use placeholders and bind values, pass a reference to an
321 array containing a scalar reference to the literal SQL with
322 placeholders as the first item, followed by a list of values to
323 bind:
324 [ \'mycol > ?' => 123 ]
326 "column"
328 A bare column name. If the query includes more than one
329 table, the column name may be ambiguous if it appears in
330 two or more tables. In that case, a fatal error will
331 occur. To solve this, use one of the less ambiguous forms
332 below.
333 "table.column"
335 A column name and a table name joined by a dot. This is
336 the "fully qualified" column name.
337 "tN.column"
339 A column name and a table alias joined by a dot. The table
340 alias is in the form "tN", where "N" is a number starting
341 from 1. See the documentation for tables parameter below
342 to learn how table aliases are assigned to tables.
343 Any of the above prefixed with "!"
345 This indicates the negation of the specified condition.
346 If query_is_sql is false or omitted, then NAME can also take on
348 these additional forms:
349 "method"
351 A get_set column method name from a
352 Rose::DB::Object-derived class fronting one of the tables
353 being queried. There may be ambiguity here if the same
354 method name is defined on more than one of the classes
355 involved in the query. In such a case, the method will be
356 mapped to the first Rose::DB::Object-derived class that
357 contains a method by that name, considered in the order
358 that the tables are provided in the tables parameter.
359 "!method"
361 This indicates the negation of the specified condition.
362 Un-prefixed column or method names that are ambiguous (i.e.,
364 exist in more than one of the tables being queried) are
365 considered to be part of the primary table ("t1").
366 Finally, in the case of apparently intractable ambiguity, like
368 when a table name is the same as another table's alias,
369 remember that you can always use the "tn_"-prefixed column name
370 aliases, which are unique within a given query.
371 All of these clauses are joined by logic (default: "AND") in
373 the final query. Example:
374 $sql = build_select
376 (
377 dbh => $dbh,
378 select => 'id, title',
379 tables => [ 'articles' ],
380 columns => { articles => [ qw(id category type title) ] },
381 query =>
382 [
383 category => [ 'sports', 'science' ],
384 type => 'news',
385 title => { like => [ '%million%',
386 '%resident%' ] },
387 ],
388 query_is_sql => 1);
389 The above returns an SQL statement something like this:
391 SELECT id, title FROM articles WHERE
393 category IN ('sports', 'science')
394 AND
395 type = 'news'
396 AND
397 (title LIKE '%million%' OR title LIKE '%resident%')
398 LIMIT 5
399 Nested boolean logic is possible using the special keywords
401 "and" and "or" (case insensitive). Example:
402 $sql = build_select
404 (
405 dbh => $dbh,
406 select => 'id, title',
407 tables => [ 'articles' ],
408 columns => { articles => [ qw(id category type title) ] },
409 query =>
410 [
411 or =>
412 [
413 and => [ category => undef, type => 'aux' ],
414 category => [ 'sports', 'science' ],
415 ],
416 type => 'news',
417 title => { like => [ '%million%',
418 '%resident%' ] },
419 ],
420 query_is_sql => 1);
421 which returns an SQL statement something like this:
423 SELECT id, title FROM articles WHERE
425 (
426 (
427 category IS NULL AND
428 type = 'aux'
429 )
430 OR category IN ('sports', 'science')
431 )
432 AND
433 type = 'news'
434 AND
435 (title LIKE '%million%' OR title LIKE '%resident%')
436 The "and" and "or" keywords can be used multiple times within a
438 query (just like all other NAME specifiers described earlier)
439 and can be arbitrarily nested.
440 If you have a column named "and" or "or", you'll have to use
442 the fully-qualified (table.column) or alias-qualified
443 (tN.column) forms in order to address that column.
444 If query_is_sql is false or omitted, all of the parameter
446 values are passed through the "parse_value()" and
447 "format_value()" methods of their corresponding
448 Rose::DB::Object::Metadata::Column-derived column objects.
449 If a column object returns true from its manager_uses_method()
451 method, then its parameter value is passed through the
452 corresponding Rose::DB::Object-derived object method instead.
453 Example:
455 $dt = DateTime->new(year => 2001, month => 1, day => 31);
457 $sql = build_select
459 (
460 db => $db,
461 select => 'id, category',
462 tables => [ 'articles' ],
463 columns => { articles => [ qw(id category type date) ] },
464 classes => { articles => 'Article' },
465 query =>
466 [
467 type => 'news',
468 date => { lt => '12/25/2003 8pm' },
469 date => { gt => $dt },
470 ],
471 sort_by => 'id DESC, category',
472 limit => 5);
473 The above returns an SQL statement something like this:
475 SELECT id, category FROM articles WHERE
477 type = 'news'
478 AND
479 date < '2003-12-25 20:00:00'
480 AND
481 date > '2001-01-31 00:00:00'
482 ORDER BY id DESC, category
483 LIMIT 5
484 Finally, here's an example using more than one table:
486 $dt = DateTime->new(year => 2001, month => 1, day => 31);
488 $sql = build_select
490 (
491 db => $db,
492 tables => [ 'articles', 'categories' ],
493 columns =>
494 {
495 articles => [ qw(id name category_id date) ],
496 categories => [ qw(id name description) ],
497 },
498 classes =>
499 {
500 articles => 'Article',
501 categories => 'Category',
502 },
503 query =>
504 [
505 '!t1.name' => { like => '%foo%' },
506 t2.name => 'news',
507 date => { lt => '12/25/2003 8pm' },
508 date => { gt => $dt },
509 ],
510 clauses =>
511 [
512 't1.category_id = t2.id',
513 ],
514 sort_by => 'articles.name DESC, t2.name',
515 limit => 5);
516 The above returns an SQL statement something like this:
518 SELECT
520 t1.id,
521 t1.name,
522 t1.category_id,
523 t1.date,
524 t2.id,
525 t2.name,
526 t2.description
527 FROM
528 articles t1,
529 categories t2
530 WHERE
531 t1.category_id = t2.id
532 AND
533 NOT(t1.name LIKE '%foo%')
534 AND
535 t2.name = 'news'
536 AND
537 t1.date < '2003-12-25 20:00:00'
538 AND
539 t1.date > '2001-01-31 00:00:00'
540 ORDER BY articles.name DESC, t2.name
541 LIMIT 5
542 query_is_sql BOOL
544 If omitted, this boolean flag is false. If true, then the
545 values of the query parameters are taken as literal strings
546 that are suitable for direct use in SQL queries. Example:
547 $sql = build_select
549 (
550 query_is_sql => 1,
551 query =>
552 [
553 date => { lt => '2003-12-25 20:00:00' },
554 ],
555 ...
556 );
557 Here the date value "2003-12-25 20:00:00" must be in the format
559 that the current database expects for columns of that data
560 type.
561 But if query_is_sql is false or omitted, then any query value
563 that can be handled by the Rose::DB::Object-derived object
564 method that services the corresponding database column is
565 valid. (Note that this is only possible when this method is
566 called from one of the built-in Rose::DB::Object::Manager
567 methods, e.g., get_objects().)
568 Example:
570 $dt = DateTime->new(year => 2001, month => 1, day => 31);
572 $sql = build_select
574 (
575 query =>
576 [
577 date => { gt => $dt },
578 date => { lt => '12/25/2003 8pm' },
579 ],
580 ...
581 );
582 Here a DateTime object and a loosely formatted date are passed
584 as values. Provided the Rose::DB::Object-derived object method
585 that services the "date" column can handle such values, they
586 will be parsed and formatted as appropriate for the current
587 database.
588 The advantage of this approach is that the query values do not
590 have to be so rigorously specified, nor do they have to be in a
591 database-specific format.
592 The disadvantage is that all of this parsing and formatting is
594 done for every query value, and that adds additional overhead
595 to each call.
596 Usually, this overhead is dwarfed by the time required for the
598 database to service the query, and, perhaps more importantly,
599 the reduced maintenance headache and busywork required to
600 properly format all query values.
601 select COLUMNS
603 The names of the columns to select from the table. COLUMNS may
604 be a string of comma-separated column names, or a reference to
605 an array of column names. If this parameter is omitted, it
606 defaults to all of the columns in all of the tables
607 participating in the query (according to the value of the
608 columns argument).
609 sort_by [ CLAUSE | ARRAYREF ]
611 A fully formed SQL "ORDER BY ..." clause, sans the words "ORDER
612 BY", or a reference to an array of strings to be joined with a
613 comma and appended to the "ORDER BY" clause.
614 If an item in the referenced array is itself a reference to a
616 scalar, then that item will be dereferenced and passed through
617 unmodified.
618 tables TABLES
620 A reference to an array of table names. This argument is
621 required. A fatal error will occur if it is omitted.
622 If more than one table is in the list, then each table is
624 aliased to "tN", where N is an ascending number starting with
625 1. The tables are numbered according to their order in TABLES.
626 Example:
627 $sql = build_select(tables => [ 'foo', 'bar', 'baz' ], ...);
629 print $sql;
631 # SELECT ... FROM
633 # foo AS t1,
634 # bar AS t2,
635 # baz AS t3
636 # ...
637 Furthermore, if there is no explicit value for the select
639 parameter and if the unique_aliases parameter is set to true,
640 then each selected column is aliased with a "tN_" prefix in a
641 multi-table query. Example:
642 SELECT
644 t1.id AS t1_id,
645 t1.name AS t1_name,
646 t2.id AS t2_id,
647 t2.name AS t2_name
648 FROM
649 foo AS t1,
650 bar AS t2
651 WHERE
652 ...
653 These unique aliases provide a technique of last resort for
655 unambiguously addressing a column in a query clause.
656 unique_aliases BOOL
658 If true, then each selected column will be given a unique alias
659 by prefixing it with its table alias and an underscore. The
660 default value is false. See the documentation for the tables
661 parameter above for an example.
662 build_where_clause PARAMS
664 This works the same as the build_select() function, except that it
665 only returns the "WHERE" clause of the SQL query, sans the word
666 "WHERE" and prefixed with a single space.
667
669 John C. Siracusa (siracusa@gmail.com)
670
672 Copyright (c) 2010 by John C. Siracusa. All rights reserved. This
673 program is free software; you can redistribute it and/or modify it
674 under the same terms as Perl itself.
675perl v5.32.0 2020-07-28 Rose::DB::Object::QueryBuilder(3)