1Rose::DB::Object::ManagUesre(r3)Contributed Perl DocumenRtoastei:o:nDB::Object::Manager(3)
2
6 Rose::DB::Object::Manager - Fetch multiple Rose::DB::Object-derived
7 objects from the database using complex queries.
8
10 ##
11 ## Given the following Rose::DB::Object-derived classes...
12 ##
13 package Category;
15 use base 'Rose::DB::Object';
17 __PACKAGE__->meta->setup
19 (
20 table => 'categories',
21 columns =>
22 [
23 id => { type => 'int', primary_key => 1 },
24 name => { type => 'varchar', length => 255 },
25 description => { type => 'text' },
26 ],
27 unique_key => 'name',
29 );
30 ...
32 package CodeName;
34 use base 'Rose::DB::Object';
36 __PACKAGE__->meta->setup
38 (
39 table => 'code_names',
40 columns =>
41 [
42 id => { type => 'int', primary_key => 1 },
43 product_id => { type => 'int' },
44 name => { type => 'varchar', length => 255 },
45 applied => { type => 'date', not_null => 1 },
46 ],
47 foreign_keys =>
49 [
50 product =>
51 {
52 class => 'Product',
53 key_columns => { product_id => 'id' },
54 },
55 ],
56 );
57 ...
59 package Product;
61 use base 'Rose::DB::Object';
63 __PACKAGE__->meta->setup
65 (
66 table => 'products',
67 columns =>
68 [
69 id => { type => 'int', primary_key => 1 },
70 name => { type => 'varchar', length => 255 },
71 description => { type => 'text' },
72 category_id => { type => 'int' },
73 region_num => { type => 'int' },
74 status =>
76 {
77 type => 'varchar',
78 check_in => [ 'active', 'inactive' ],
79 default => 'inactive',
80 },
81 start_date => { type => 'datetime' },
83 end_date => { type => 'datetime' },
84 date_created => { type => 'timestamp', default => 'now' },
86 last_modified => { type => 'timestamp', default => 'now' },
87 ],
88 unique_key => 'name',
90 foreign_keys =>
92 [
93 category =>
94 {
95 class => 'Category',
96 key_columns =>
97 {
98 category_id => 'id',
99 }
100 },
101 ],
102 relationships =>
104 [
105 code_names =>
106 {
107 type => 'one to many',
108 class => 'CodeName',
109 column_map => { id => 'product_id' },
110 manager_args =>
111 {
112 sort_by => CodeName->meta->table . '.applied DESC',
113 },
114 },
115 ],
116 );
117 ...
119 ##
121 ## Create a manager class
122 ##
123 package Product::Manager;
125 use base 'Rose::DB::Object::Manager';
127 sub object_class { 'Product' }
129 __PACKAGE__->make_manager_methods('products');
131 # The call above creates the methods shown below. (The actual
133 # method bodies vary slightly, but this is the gist of it...)
134 #
135 # sub get_products
136 # {
137 # shift->get_objects(@_, object_class => 'Product');
138 # }
139 #
140 # sub get_products_iterator
141 # {
142 # shift->get_objects_iterator(@_, object_class => 'Product');
143 # }
144 #
145 # sub get_products_count
146 # {
147 # shift->get_objects_count(@_, object_class => 'Product');
148 # }
149 #
150 # sub update_products
151 # {
152 # shift->update_objects(@_, object_class => 'Product');
153 # }
154 #
155 # sub delete_products
156 # {
157 # shift->delete_objects(@_, object_class => 'Product');
158 # }
159 ...
161 ##
163 ## Use the manager class
164 ##
165 #
167 # Get a reference to an array of objects
168 #
169 $products =
171 Product::Manager->get_products
172 (
173 query =>
174 [
175 category_id => [ 5, 7, 22 ],
176 status => 'active',
177 start_date => { lt => '15/12/2005 6:30 p.m.' },
178 name => { like => [ '%foo%', '%bar%' ] },
179 ],
180 sort_by => 'category_id, start_date DESC',
181 limit => 100,
182 offset => 80,
183 );
184 foreach my $product (@$products)
186 {
187 print $product->id, ' ', $product->name, "\n";
188 }
189 #
191 # Get objects iterator
192 #
193 $iterator =
195 Product::Manager->get_products_iterator
196 (
197 query =>
198 [
199 category_id => [ 5, 7, 22 ],
200 status => 'active',
201 start_date => { lt => '15/12/2005 6:30 p.m.' },
202 name => { like => [ '%foo%', '%bar%' ] },
203 ],
204 sort_by => 'category_id, start_date DESC',
205 limit => 100,
206 offset => 80,
207 );
208 while($product = $iterator->next)
210 {
211 print $product->id, ' ', $product->name, "\n";
212 }
213 print $iterator->total;
215 #
217 # Get objects count
218 #
219 $count =
221 Product::Manager->get_products_count
222 (
223 query =>
224 [
225 category_id => [ 5, 7, 22 ],
226 status => 'active',
227 start_date => { lt => '15/12/2005 6:30 p.m.' },
228 name => { like => [ '%foo%', '%bar%' ] },
229 ],
230 );
231 die Product::Manager->error unless(defined $count);
233 print $count; # or Product::Manager->total()
235 #
237 # Get objects and sub-objects in a single query
238 #
239 $products =
241 Product::Manager->get_products
242 (
243 with_objects => [ 'category', 'code_names' ],
244 query =>
245 [
246 category_id => [ 5, 7, 22 ],
247 status => 'active',
248 start_date => { lt => '15/12/2005 6:30 p.m.' },
249 # We need to disambiguate the "name" column below since it
251 # appears in more than one table referenced by this query.
252 # When more than one table is queried, the tables have numbered
253 # aliases starting from the "main" table ("products"). The
254 # "products" table is t1, "categories" is t2, and "code_names"
255 # is t3. You can read more about automatic table aliasing in
256 # the documentation for the get_objects() method below.
257 #
258 # "category.name" and "categories.name" would work too, since
259 # table and relationship names are also valid prefixes.
260 't2.name' => { like => [ '%foo%', '%bar%' ] },
262 ],
263 sort_by => 'category_id, start_date DESC',
264 limit => 100,
265 offset => 80,
266 );
267 foreach my $product (@$products)
269 {
270 # The call to $product->category does not hit the database
271 print $product->name, ': ', $product->category->name, "\n";
272 # The call to $product->code_names does not hit the database
274 foreach my $code_name ($product->code_names)
275 {
276 # This call doesn't hit the database either
277 print $code_name->name, "\n";
278 }
279 }
280 #
282 # Update objects
283 #
284 $num_rows_updated =
286 Product::Manager->update_products(
287 set =>
288 {
289 end_date => DateTime->now,
290 region_num => { sql => 'region_num * -1' }
291 status => 'defunct',
292 },
293 where =>
294 [
295 start_date => { lt => '1/1/1980' },
296 status => [ 'active', 'pending' ],
297 ]);
298 #
300 # Delete objects
301 #
302 $num_rows_deleted =
304 Product::Manager->delete_products(
305 where =>
306 [
307 status => [ 'stale', 'old' ],
308 name => { like => 'Wax%' },
309 or =>
310 [
311 start_date => { gt => '2008-12-30' },
312 end_date => { gt => 'now' },
313 ],
314 ]);
315
317 Rose::DB::Object::Manager is a base class for classes that select rows
318 from tables fronted by Rose::DB::Object-derived classes. Each row in
319 the table(s) queried is converted into the equivalent
320 Rose::DB::Object-derived object.
321 Class methods are provided for fetching objects all at once, one at a
323 time through the use of an iterator, or just getting the object count.
324 Subclasses are expected to create syntactically pleasing wrappers for
325 Rose::DB::Object::Manager class methods, either manually or with the
326 make_manager_methods method. A very minimal example is shown in the
327 synopsis above.
328
330 dbi_prepare_cached [BOOL]
331 Get or set a boolean value that indicates whether or not this class
332 will use DBI's prepare_cached method by default (instead of the
333 prepare method) when preparing SQL queries. The default value is
334 false.
335 default_limit_with_subselect [BOOL]
337 Get or set a boolean value that determines whether or not this
338 class will consider using a sub-query to express "limit"/"offset"
339 constraints when fetching sub-objects related through one of the
340 "...-to-many" relationship types. Not all databases support this
341 syntax, and not all queries can use it even in supported databases.
342 If this parameter is true, the feature will be used when possible,
343 by default. The default value is true.
344 default_manager_method_types [ LIST | ARRAYREF ]
346 Get or set the default list of method types used by the
347 make_manager_methods method. The default list is "objects",
348 "iterator", "count", "delete", and "update".
349 default_nested_joins [BOOL]
351 Get or set a boolean value that determines whether or not this
352 class will consider using nested JOIN syntax when fetching related
353 objects. Not all databases support this syntax, and not all
354 queries can use it even in supported databases. If this parameter
355 is true, the feature will be used when possible, by default. The
356 default value is true.
357 default_objects_per_page [NUM]
359 Get or set the default number of items per page, as returned by the
360 get_objects method when used with the "page" and/or "per_page"
361 parameters. The default value is 20.
362 delete_objects [ PARAMS | ARRAYREF | HASHREF ]
364 Delete rows from a table fronted by a Rose::DB::Object-derived
365 class based on PARAMS, where PARAMS are name/value pairs. Returns
366 the number of rows deleted, or undef if there was an error.
367 If the first argument is a reference to a hash or array, it is
369 converted to a reference to an array (if necessary) and taken as
370 the value of the "where" parameter.
371 Valid parameters are:
373 all BOOL
375 If set to a true value, this parameter indicates an explicit
376 request to delete all rows from the table. If both the "all"
377 and the "where" parameters are passed, a fatal error will
378 occur.
379 db DB
381 A Rose::DB-derived object used to access the database. If
382 omitted, one will be created by calling the init_db method of
383 the object_class.
384 prepare_cached BOOL
386 If true, then DBI's prepare_cached method will be used (instead
387 of the prepare method) when preparing the SQL statement that
388 will delete the objects. If omitted, the default value is
389 determined by the dbi_prepare_cached class method.
390 object_class CLASS
392 The name of the Rose::DB::Object-derived class that fronts the
393 table from which rows are to be deleted. This parameter is
394 required; a fatal error will occur if it is omitted. Defaults
395 to the value returned by the object_class class method.
396 where ARRAYREF
398 The query parameters, passed as a reference to an array of
399 name/value pairs. These pairs are used to formulate the
400 "where" clause of the SQL query that is used to delete the rows
401 from the table. Arbitrarily nested boolean logic is supported.
402 For the complete list of valid parameter names and values, see
404 the documentation for the "query" parameter of the build_select
405 function in the Rose::DB::Object::QueryBuilder module.
406 If this parameter is omitted, this method will refuse to delete
408 all rows from the table and a fatal error will occur. To
409 delete all rows from a table, you must pass the "all" parameter
410 with a true value. If both the "all" and the "where"
411 parameters are passed, a fatal error will occur.
412 error
414 Returns the text message associated with the last error, or false
415 if there was no error.
416 error_mode [MODE]
418 Get or set the error mode for this class. The error mode
419 determines what happens when a method of this class encounters an
420 error. The default setting is "fatal", which means that methods
421 will croak if they encounter an error.
422 PLEASE NOTE: The error return values described in the method
424 documentation in the rest of this document are only relevant when
425 the error mode is set to something "non-fatal." In other words, if
426 an error occurs, you'll never see any of those return values if the
427 selected error mode dies or croaks or otherwise throws an exception
428 when an error occurs.
429 Valid values of MODE are:
431 carp
433 Call Carp::carp with the value of the object error as an
434 argument.
435 cluck
437 Call Carp::cluck with the value of the object error as an
438 argument.
439 confess
441 Call Carp::confess with the value of the object error as an
442 argument.
443 croak
445 Call Carp::croak with the value of the object error as an
446 argument.
447 fatal
449 An alias for the "croak" mode.
450 return
452 Return a value that indicates that an error has occurred, as
453 described in the documentation for each method.
454 In all cases, the class's "error" attribute will also contain the
456 error message.
457 get_objects [ PARAMS | HASHREF | ARRAYREF ]
459 Get Rose::DB::Object-derived objects based on PARAMS, where PARAMS
460 are name/value pairs. Returns a reference to a (possibly empty)
461 array, or undef if there was an error.
462 If the first argument is a reference to a hash or array, it is
464 converted to a reference to an array (if necessary) and taken as
465 the value of the "query" parameter.
466 Each table that participates in the query will be aliased. Each
468 alias is in the form "tN" where "N" is an ascending number starting
469 with 1. The tables are numbered as follows.
470 • The primary table is always "t1"
472 • The table(s) that correspond to each relationship or foreign
474 key named in the "with_objects" parameter are numbered in
475 order, starting with "t2"
476 • The table(s) that correspond to each relationship or foreign
478 key named in the "require_objects" parameter are numbered in
479 order, starting where the "with_objects" table aliases left
480 off.
481 "Many to many" relationships have two corresponding tables, and
483 therefore will use two "tN" numbers. All other supported of
484 relationship types only have just one table and will therefore use
485 a single "tN" number.
486 For example, imagine that the "Product" class shown in the synopsis
488 also has a "many to many" relationship named "colors." Now
489 consider this call:
490 $products =
492 Product::Manager->get_products(
493 require_objects => [ 'category' ],
494 with_objects => [ 'code_names', 'colors' ],
495 multi_many_ok => 1,
496 query => [ status => 'defunct' ],
497 sort_by => 't1.name');
498 The "products" table is "t1" since it's the primary table--the
500 table behind the "Product" class that "Product::Manager" manages.
501 Next, the "with_objects" tables are aliased. The "code_names"
502 table is "t2". Since "colors" is a "many to many" relationship, it
503 gets two numbers: "t3" and "t4". Finally, the "require_objects"
504 tables are numbered: the table behind the foreign key "category" is
505 "t5". Here's an annotated version of the example above:
506 # Table aliases in the comments
508 $products =
509 Product::Manager->get_products(
510 # t5
511 require_objects => [ 'category' ],
512 # t2 t3, t4
513 with_objects => [ 'code_names', 'colors' ],
514 multi_many_ok => 1,
515 query => [ status => 'defunct' ],
516 sort_by => 't1.name'); # "products" is "t1"
517 Also note that the "multi_many_ok" parameter was used in order to
519 suppress the warning that occurs when more than one "... to many"
520 relationship is included in the combination of "require_objects"
521 and "with_objects" ("code_names" (one to many) and "colors" (many
522 to many) in this case). See the documentation for "multi_many_ok"
523 below.
524 The "tN" table aliases are for convenience, and to isolate end-user
526 code from the actual table names. Ideally, the actual table names
527 should only exist in one place in the entire code base: in the
528 class definitions for each Rose::DB::OBject-derived class.
529 That said, when using Rose::DB::Object::Manager, the actual table
531 names can be used as well. But be aware that some databases don't
532 like a mix of table aliases and real table names in some kinds of
533 queries.
534 Valid parameters to get_objects are:
536 allow_empty_lists BOOL
538 If set to true, "query" parameters with empty lists as values
539 are allowed. For example:
540 @ids = (); # empty list
542 Product::Manager->get_products(
544 query =>
545 [
546 id => \@ids,
547 ...
548 ]);
549 By default, passing an empty list as a value will cause a fatal
551 error.
552 db DB
554 A Rose::DB-derived object used to access the database. If
555 omitted, one will be created by calling the init_db method of
556 the "object_class".
557 debug BOOL
559 If true, print the generated SQL to STDERR.
560 distinct [ BOOL | ARRAYREF ]
562 If set to any kind of true value, then the "DISTINCT" SQL
563 keyword will be added to the "SELECT" statement. Specific
564 values trigger the behaviors described below.
565 If set to a simple scalar value that is true, then only the
567 columns in the primary table ("t1") are fetched from the
568 database.
569 If set to a reference to an array of table names, "tN" table
571 aliases, or relationship or foreign key names, then only the
572 columns from the corresponding tables will be fetched. In the
573 case of relationships that involve more than one table, only
574 the "most distant" table is considered. (e.g., The map table
575 is ignored in a "many to many" relationship.) Columns from the
576 primary table ("t1") are always selected, regardless of whether
577 or not it appears in the list.
578 This parameter conflicts with the "fetch_only" parameter in the
580 case where both provide a list of table names or aliases. In
581 this case, if the value of the "distinct" parameter is also
582 reference to an array table names or aliases, then a fatal
583 error will occur.
584 fetch_only ARRAYREF
586 ARRAYREF should be a reference to an array of table names or
587 "tN" table aliases. Only the columns from the corresponding
588 tables will be fetched. In the case of relationships that
589 involve more than one table, only the "most distant" table is
590 considered. (e.g., The map table is ignored in a "many to
591 many" relationship.) Columns from the primary table ("t1") are
592 always selected, regardless of whether or not it appears in the
593 list.
594 This parameter conflicts with the "distinct" parameter in the
596 case where both provide a list of table names or aliases. In
597 this case, then a fatal error will occur.
598 for_update BOOL
600 If true, this parameter is translated to be the equivalent of
601 passing the lock parameter and setting the "type" to "for
602 update". For example, this:
603 for_update => 1
605 is equivalent to this:
607 lock => { type => 'for update' }
609 See the lock parameter below for more information.
611 hints HASHREF
613 A reference to a hash of hints that influence the SQL generated
614 to fetch the objects. Hints are just "suggestions" and may be
615 ignored, depending on the actual features of the database being
616 queried. Use the debug parameter to see the generated SQL.
617 Most of the current hints apply to MySQL only. See the
618 relevant documentation for more details:
619 <http://dev.mysql.com/doc/refman/5.0/en/select.html>
621 The hints hash is keyed by tN table aliases or relationship
623 names. The value of each key is a reference to a hash of hint
624 directives. In the absence of any key for "t1" or the name of
625 the primary table, the entire hints hash is considered
626 applicable to the primary table.
627 Valid hint directives are:
629 all_rows BOOL
631 If true, direct the database to choose the query plan that
632 returns all the records as quickly as possible.
633 big_result BOOL
635 If true, indicate to the database that the result set is
636 expected to be big.
637 buffer_result BOOL
639 If true, force the result to be put into a temporary table.
640 cache BOOL
642 If true, ask the database to store the result in its query
643 cache.
644 calc_found_rows BOOL
646 If true, ask the database to internally calculate the
647 number of rows found, ignoring any limit or offset
648 arguments.
649 comment TEXT
651 Add a comment after the "SELECT" keyword in the query.
652 TEXT should not be surrounded by any comment delimiters.
653 The appropriate delimiters will be added automatically.
654 first_rows BOOL
656 If true, direct the database to choose the query plan that
657 returns the first result record as soon as possible.
658 force_index [ INDEX | ARRAYREF ]
660 Force the use of the named indexes, specified by an index
661 name or a reference to an array of index names.
662 high_priority BOOL
664 If true, give this query higher priority.
665 ignore_index [ INDEX | ARRAYREF ]
667 Ignore the named indexes, specified by an index name or a
668 reference to an array of index names.
669 no_cache BOOL
671 If true, ask the database not to store the result in its
672 query cache.
673 small_result BOOL
675 If true, indicate to the database that the result set is
676 expected to be small.
677 straight_join BOOL
679 If true, ask the database to join the tables in the order
680 that they are listed in the "FROM" clause of the SQL
681 statement.
682 strict_ops BOOL
684 If true, any comparison operator used in the "query" that
685 is not listed in the Rose::DB::Object::QueryBuilder
686 documentation will cause a fatal error. The default value
687 is determined by the strict_ops class method.
688 use_index [ INDEX | ARRAYREF ]
690 Prefer to use the named indexes, specified by an index name
691 or a reference to an array of index names.
692 inject_results BOOL
694 If true, then the data returned from the database will be
695 directly "injected" into the objects returned by this method,
696 bypassing the constructor and column mutator methods for each
697 object class. The default is false. This parameter is ignored
698 (i.e., treated as if it were false) if the "select" parameter
699 is passed.
700 This parameter is useful for situations where the performance
702 of get_objects is limited by the speed at which
703 Rose::DB::Object-derived objects can be created. It's safe to
704 set this parameter to true only if the constructor and column
705 mutator methods for all of the classes involved do not have any
706 side-effects (or if it's is okay to bypass any side-effects).
707 The default Rose::DB::Object constructor and the column mutator
709 methods created by the column classes included in the
710 Rose::DB::Object module distribution do not have any side-
711 effects and should therefore be safe to use with this
712 parameter.
713 limit NUM
715 Return a maximum of NUM objects.
716 limit_with_subselect BOOL
718 This parameter controls whether or not this method will
719 consider using a sub-query to express "limit"/"offset"
720 constraints when fetching sub-objects related through one of
721 the "...-to-many" relationship types. Not all databases
722 support this syntax, and not all queries can use it even in
723 supported databases. If this parameter is true, the feature
724 will be used when possible.
725 The default value is determined by the
727 default_limit_with_subselect class method.
728 lock [ TYPE | HASHREF ]
730 Select the objects using some form of locking. These lock
731 directives have database-specific behavior and not all
732 directives are supported by all databases. Consult your
733 database's documentation to find out more. Use the debug
734 parameter to see the generated SQL.
735 The value should be a reference to a hash or a TYPE string,
737 which is equivalent to setting the value of the "type" key in
738 the hash reference form. For example, these are both
739 equivalent:
740 lock => 'for update'
742 lock => { type => 'for update' }
743 Valid hash keys are:
745 columns ARRAYREF
747 A reference to an array of column names to lock. The
748 columns may be prefixed with their table name or their "tN"
749 alias (e.g., "mytable.mycol" or "t2.mycol") or left
750 unadorned if they are not ambiguous. References to scalars
751 will be de-referenced and used as-is, included literally in
752 the SQL locking clause.
753 nowait BOOL
755 If true, do not wait to acquire the lock. If supported,
756 this is usually by adding a "NOWAIT" directive to the SQL.
757 on ARRAYREF
759 A reference to an array of items to lock. Depending on the
760 database, these may be column or tables. Both column and
761 table names should be specified using dot-separated
762 relationship paths.
763 For example, "vendor.region.name" would lock the "name"
765 column in the table arrived at by traversing the "vendor"
766 and then the "region" relationships, starting from the
767 primary table ("t1"). Lone column names may also be used,
768 provided they're not ambiguous.
769 For locking whole tables, "vendor.region" would lock the
771 table arrived at by traversing the "vendor" and then the
772 "region" relationships. (See the require_objects parameter
773 for more information on relationship traversal.)
774 Finally, references to scalars will be de-referenced and
776 used as-is, included literally in the SQL locking clause.
777 skip_locked BOOL
779 If true, skip any locked rows. If supported, this is
780 usually by adding a "SKIP LOCKED" clause to the SQL.
781 tables ARRAYREF
783 A reference to an array of tables to lock. Table named or
784 "tN" aliases may be used. References to scalars will be
785 de-referenced and used as-is, included literally in the SQL
786 locking clause.
787 type TYPE
789 The type of lock to acquire. Valid values for TYPE are
790 "for update" and "shared". This hash key is required
791 unless the for_update parameter was passed with a true
792 value.
793 wait TIME
795 Wait for the specified TIME (generally seconds) before
796 giving up acquiring the lock. If supported, this is usually
797 by adding a "WAIT ..." clause to the SQL.
798 You may pass only one of the parameters that specifies "what to
800 lock" (i.e., "columns", "on", or "tables").
801 nested_joins BOOL
803 This parameter controls whether or not this method will
804 consider using nested JOIN syntax when fetching related
805 objects. Not all databases support this syntax, and not all
806 queries will use it even in supported databases. If this
807 parameter is true, the feature will be used when possible.
808 The default value is determined by the default_nested_joins
810 class method.
811 multi_many_ok BOOL
813 If true, do not print a warning when attempting to do multiple
814 LEFT OUTER JOINs against tables related by "... to many"
815 relationships. See the documentation for the "with_objects"
816 parameter for more information.
817 nonlazy [ BOOL | ARRAYREF ]
819 By default, get_objects will honor all load-on-demand columns
820 when fetching objects. Use this parameter to override that
821 behavior and select all columns instead.
822 If the value is a true boolean value (typically "1"), then all
824 columns will be fetched for all participating classes (i.e.,
825 the main object class as well as any sub-object classes).
826 The value can also be a reference to an array of relationship
828 names. The sub-objects corresponding to each relationship name
829 will have all their columns selected. To refer to the main
830 class (the "t1" table), use the special name "self".
831 object_args HASHREF
833 A reference to a hash of name/value pairs to be passed to the
834 constructor of each "object_class" object fetched, in addition
835 to the values from the database.
836 object_class CLASS
838 The name of the Rose::DB::Object-derived objects to be fetched.
839 This parameter is required; a fatal error will occur if it is
840 omitted. Defaults to the value returned by the object_class
841 class method.
842 offset NUM
844 Skip the first NUM rows. If the database supports some sort of
845 "limit with offset" syntax (e.g., "LIMIT 10 OFFSET 20") then it
846 will be used. Otherwise, the first NUM rows will be fetched
847 and then discarded.
848 This parameter can only be used along with the "limit"
850 parameter, otherwise a fatal error will occur.
851 page NUM
853 Show page number NUM of objects. Pages are numbered starting
854 from 1. A page number less than or equal to zero causes the
855 page number to default to 1.
856 The number of objects per page can be set by the "per_page"
858 parameter. If the "per_page" parameter is supplied and this
859 parameter is omitted, it defaults to 1 (the first page).
860 If this parameter is included along with either of the "limit"
862 or <offset> parameters, a fatal error will occur.
863 per_page NUM
865 The number of objects per "page". Defaults to the value
866 returned by the default_objects_per_page class method (20, by
867 default).
868 If this parameter is included along with either of the "limit"
870 or <offset> parameters, a fatal error will occur.
871 prepare_cached BOOL
873 If true, then DBI's prepare_cached method will be used (instead
874 of the prepare method) when preparing the SQL statement that
875 will fetch the objects. If omitted, the default value is
876 determined by the dbi_prepare_cached class method.
877 query ARRAYREF
879 The query parameters, passed as a reference to an array of
880 name/value pairs. These pairs are used to formulate the
881 "where" clause of the SQL query that, in turn, is used to fetch
882 the objects from the database. Arbitrarily nested boolean
883 logic is supported.
884 For the complete list of valid parameter names and values, see
886 the documentation for the "query" parameter of the build_select
887 function in the Rose::DB::Object::QueryBuilder module.
888 This class also supports an extension to the query syntax
890 supported by Rose::DB::Object::QueryBuilder. In addition to
891 table names and aliases, column (or column method) names may be
892 prefixed with foreign key or relationship names. These names
893 may be chained, with dots (".") separating the components.
894 For example, imagine three tables, "products", "vendors", and
896 "regions", fronted by three Rose::DB::Object-derived classes,
897 "Product", "Vendor", and "Region", respectively. Each
898 "Product" has a "Vendor", and each "Vendor" has a "Region".
899 To select only products whose vendors are in the United States,
901 use a query argument like this:
902 query => [ 'vendor.region.name' => 'US' ],
904 This assumes that the "Product" class has a relationship or
906 foreign key named "vendor" that points to the product's
907 "Vendor", and that the "Vendor" class has a foreign key or
908 relationship named "region" that points to the vendor's
909 "Region", and that 'vendor.region' (or any foreign key or
910 relationship name chain that begins with 'vendor.region.') is
911 an argument to the "with_objects" or "require_objects"
912 parameters.
913 Please note that the "tN" table aliases are not allowed in
915 front of these kinds of chained relationship parameters. (The
916 chain of relationship names specifies the target table, so any
917 "tN" alias would be redundant at best, or present a conflict at
918 worst.)
919 require_objects ARRAYREF
921 Only fetch rows from the primary table that have all of the
922 associated sub-objects listed in ARRAYREF, a reference to an
923 array of foreign key or relationship names defined for
924 "object_class". The supported relationship types are "one to
925 one," "one to many," and "many to many".
926 For each foreign key or relationship name listed in ARRAYREF,
928 another table will be added to the query via an implicit inner
929 join. The join conditions will be constructed automatically
930 based on the foreign key or relationship definitions. Note
931 that each related table must have a Rose::DB::Object-derived
932 class fronting it.
933 Foreign key and relationship names may be chained, with dots
935 (".") separating each name. For example, imagine three tables,
936 "products", "vendors", and "regions", fronted by three
937 Rose::DB::Object-derived classes, "Product", "Vendor", and
938 "Region", respectively. Each "Product" has a "Vendor", and
939 each "Vendor" has a "Region".
940 To fetch "Product"s along with their "Vendor"s, and their
942 vendors' "Region"s, provide a "with_objects" argument like
943 this:
944 require_objects => [ 'vendor.region' ],
946 This assumes that the "Product" class has a relationship or
948 foreign key named "vendor" that points to the product's
949 "Vendor", and that the "Vendor" class has a foreign key or
950 relationship named "region" that points to the vendor's
951 "Region".
952 This chaining syntax can be used to traverse relationships of
954 any kind, including "one to many" and "many to many"
955 relationships, to an arbitrary depth.
956 The following optional suffixes may be added after any name in
958 the chain in order to override the join type used:
959 Suffix Join Type
961 ------ ----------
962 ! Inner join
963 ? Left outer join
964 Each link in a "require_objects" chain uses an inner join by
966 default. In other words, the following "require_objects"
967 parameters are all equivalent:
968 # These all mean the same thing
970 require_objects => [ 'vendor.region' ]
971 require_objects => [ 'vendor!.region!' ]
972 require_objects => [ 'vendor.region!' ]
973 require_objects => [ 'vendor!.region' ]
974 Thus, it is only really useful to use the "?" suffix in
976 "require_objects" parameters (though the "!" suffixes don't do
977 any harm). Here's a useful example of a call with hybrid join
978 chain:
979 $products =
981 Product::Manager->get_products(
982 require_objects => [ 'vendor.region?' ]);
983 All product objects returned would have associated vendor
985 objects, but those vendor objects may or may not have
986 associated region objects.
987 Note that inner joins may be implicit and nested_joins may or
989 may not be used. When in doubt, use the debug parameter to see
990 the generated SQL.
991 Warning: there may be a geometric explosion of redundant data
993 returned by the database if you include more than one "... to
994 many" relationship in ARRAYREF. Sometimes this may still be
995 more efficient than making additional queries to fetch these
996 sub-objects, but that all depends on the actual data. A
997 warning will be emitted (via Carp::cluck) if you include more
998 than one "... to many" relationship in ARRAYREF. If you're
999 sure you know what you're doing, you can silence this warning
1000 by passing the "multi_many_ok" parameter with a true value.
1001 Note: the "require_objects" list currently cannot be used to
1003 simultaneously fetch two objects that both front the same
1004 database table, but are of different classes. One workaround
1005 is to make one class use a synonym or alias for one of the
1006 tables. Another option is to make one table a trivial view of
1007 the other. The objective is to get the table names to be
1008 different for each different class (even if it's just a matter
1009 of letter case, if your database is not case-sensitive when it
1010 comes to table names).
1011 select [ CLAUSE | ARRAYREF ]
1013 Select only the columns specified in either a comma-separated
1014 string of column names or a reference to an array of column
1015 names. Strings are naively split between each comma. If you
1016 need more complex parsing, please use the array-reference
1017 argument format instead.
1018 Column names should be prefixed by the appropriate "tN" table
1020 alias, the table name, or the foreign key or relationship name.
1021 The prefix should be joined to the column name with a dot
1022 ("."). Examples: "t2.name", "vendors.age".
1023 Unprefixed columns are assumed to belong to the primary table
1025 ("t1") and are explicitly prefixed as such when selecting from
1026 more than one table. If a column name matches "/ AS \w+$/"
1027 then no prefix is applied.
1028 If the column name is "*" (e.g., "t1.*") then all columns from
1030 that table are selected.
1031 If an item in the referenced array is itself a reference to a
1033 scalar, then that item will be dereferenced and passed through
1034 unmodified.
1035 If selecting sub-objects via the "with_objects" or
1037 "require_objects" parameters, you must select the primary key
1038 columns from each sub-object table. Failure to do so will
1039 cause those sub-objects not to be created.
1040 Be warned that you should provide some way to determine which
1042 column or method and which class an item belongs to: a tN
1043 prefix, a column name, or at the very least an "... AS ..."
1044 alias clause.
1045 If any "with_objects" or "require_objects" arguments are
1047 included in this call, the "select" list must include at least
1048 the primary key column(s) from each table that contributes to
1049 the named relationships.
1050 This parameter conflicts with the "fetch_only" parameter. A
1052 fatal error will occur if both are used in the same call.
1053 If this parameter is omitted, then all columns from all
1055 participating tables are selected (optionally modified by the
1056 "nonlazy" parameter).
1057 share_db BOOL
1059 If true, "db" will be passed to each Rose::DB::Object-derived
1060 object when it is constructed. Defaults to true.
1061 sort_by [ CLAUSE | ARRAYREF ]
1063 A fully formed SQL "ORDER BY ..." clause, sans the words "ORDER
1064 BY", or a reference to an array of strings or scalar references
1065 to be de-referenced as needed, joined with a comma, and
1066 appended to the "ORDER BY" clause.
1067 If an argument is a reference to a scalar, then it is passed
1069 through to the ORDER BY clause unmodified.
1070 Otherwise, within each string, any instance of "NAME." will be
1072 replaced with the appropriate "tN." table alias, where NAME is
1073 a table, foreign key, or relationship name. All unprefixed
1074 simple column names are assumed to belong to the primary table
1075 ("t1").
1076 If selecting sub-objects (via "require_objects" or
1078 "with_objects") that are related through "one to many" or "many
1079 to many" relationships, the first condition in the sort order
1080 clause must be a column in the primary table (t1). If this
1081 condition is not met, the list of primary key columns will be
1082 added to the beginning of the sort order clause automatically.
1083 table_aliases BOOL
1085 When only a single table is used in q auery, this parameter
1086 controls whether or not the "tN" aliases are used. If the
1087 parameter is not passed, then tables are aliased. If it is
1088 passed with a false value, then tables are not aliased. When
1089 more than one table participates in a query, the "tN" table
1090 aliases are always used and this option is ignored.
1091 unique_aliases BOOL
1093 If true, and if there is no explicit value for the "select"
1094 parameter and more than one table is participating in the
1095 query, then each selected column will be given a unique alias
1096 by prefixing it with its table alias and an underscore. The
1097 default value is false. Example:
1098 SELECT
1100 t1.id AS t1_id,
1101 t1.name AS t1_name,
1102 t2.id AS t2_id,
1103 t2.name AS t2_name
1104 FROM
1105 foo AS t1,
1106 bar AS t2
1107 WHERE
1108 ...
1109 These unique aliases provide a technique of last resort for
1111 unambiguously addressing a column in a query clause.
1112 where ARRAYREF
1114 This is an alias for the "query" parameter (see above).
1115 with_map_records [ BOOL | METHOD | HASHREF ]
1117 When fetching related objects through a "many to many"
1118 relationship, objects of the map class are not retrieved by
1119 default. Use this parameter to override the default behavior.
1120 If the value is "1", then each object fetched through a mapping
1122 table will have its associated map record available through a
1123 "map_record()" attribute.
1124 If a method name is provided instead, then each object fetched
1126 through a mapping table will have its associated map record
1127 available through a method of that name.
1128 If the value is a reference to a hash, then the keys of the
1130 hash should be "many to many" relationship names, and the
1131 values should be the method names through which the maps
1132 records will be available for each relationship.
1133 with_objects ARRAYREF
1135 Also fetch sub-objects (if any) associated with rows in the
1136 primary table based on a reference to an array of foreign key
1137 or relationship names defined for "object_class". The
1138 supported relationship types are "one to one," "one to many,"
1139 and "many to many".
1140 For each foreign key or relationship name listed in ARRAYREF,
1142 another table will be added to the query via an explicit LEFT
1143 OUTER JOIN. (Foreign keys whose columns are all NOT NULL are
1144 the exception, however. They are always fetched via inner
1145 joins.) The join conditions will be constructed automatically
1146 based on the foreign key or relationship definitions. Note
1147 that each related table must have a Rose::DB::Object-derived
1148 class fronting it. See the synopsis for an example.
1149 "Many to many" relationships are a special case. They will add
1151 two tables to the query (the "map" table plus the table with
1152 the actual data), which will offset the "tN" table numbering by
1153 one extra table.
1154 Foreign key and relationship names may be chained, with dots
1156 (".") separating each name. For example, imagine three tables,
1157 "products", "vendors", and "regions", fronted by three
1158 Rose::DB::Object-derived classes, "Product", "Vendor", and
1159 "Region", respectively. Each "Product" has a "Vendor", and
1160 each "Vendor" has a "Region".
1161 To fetch "Product"s along with their "Vendor"s, and their
1163 vendors' "Region"s, provide a "with_objects" argument like
1164 this:
1165 with_objects => [ 'vendor.region' ],
1167 This assumes that the "Product" class has a relationship or
1169 foreign key named "vendor" that points to the product's
1170 "Vendor", and that the "Vendor" class has a foreign key or
1171 relationship named "region" that points to the vendor's
1172 "Region".
1173 This chaining syntax can be used to traverse relationships of
1175 any kind, including "one to many" and "many to many"
1176 relationships, to an arbitrary depth.
1177 The following optional suffixes may be added after any name in
1179 the chain in order to override the join type used:
1180 Suffix Join Type
1182 ------ ----------
1183 ! Inner join
1184 ? Left outer join
1185 Each link in a "with_objects" chain uses a left outer join by
1187 default. In other words, the following "with_objects"
1188 parameters are all equivalent:
1189 # These all mean the same thing
1191 with_objects => [ 'vendor.region' ]
1192 with_objects => [ 'vendor?.region?' ]
1193 with_objects => [ 'vendor.region?' ]
1194 with_objects => [ 'vendor?.region' ]
1195 Thus, it is only really useful to use the "!" suffix in
1197 "with_objects" parameters (though the "?" suffixes don't do any
1198 harm). Here's a useful example of a call with hybrid join
1199 chain:
1200 $products =
1202 Product::Manager->get_products(
1203 with_objects => [ 'vendor!.region' ]);
1204 All product objects returned would have associated vendor
1206 objects, but those vendor object may or may not have associated
1207 region objects.
1208 Note that inner joins may be implicit and nested_joins may or
1210 may not be used. When in doubt, use the debug parameter to see
1211 the generated SQL.
1212 Warning: there may be a geometric explosion of redundant data
1214 returned by the database if you include more than one "... to
1215 many" relationship in ARRAYREF. Sometimes this may still be
1216 more efficient than making additional queries to fetch these
1217 sub-objects, but that all depends on the actual data. A
1218 warning will be emitted (via Carp::cluck) if you include more
1219 than one "... to many" relationship in ARRAYREF. If you're
1220 sure you know what you're doing, you can silence this warning
1221 by passing the "multi_many_ok" parameter with a true value.
1222 Note: the "with_objects" list currently cannot be used to
1224 simultaneously fetch two objects that both front the same
1225 database table, but are of different classes. One workaround
1226 is to make one class use a synonym or alias for one of the
1227 tables. Another option is to make one table a trivial view of
1228 the other. The objective is to get the table names to be
1229 different for each different class (even if it's just a matter
1230 of letter case, if your database is not case-sensitive when it
1231 comes to table names).
1232 get_objects_count [PARAMS]
1234 Accepts the same arguments as get_objects, but just returns the
1235 number of objects that would have been fetched, or undef if there
1236 was an error.
1237 get_objects_from_sql [ SQL | PARAMS ]
1239 Fetch objects using a custom SQL query. Pass either a single SQL
1240 query string or name/value parameters as arguments. Valid
1241 parameters are:
1242 args ARRAYREF
1244 A reference to an array of arguments to be passed to DBI's
1245 execute method when the query is run. The number of items in
1246 this array must exactly match the number of placeholders in the
1247 SQL query.
1248 db DB
1250 A Rose::DB-derived object used to access the database. If
1251 omitted, one will be created by calling the init_db method of
1252 the "object_class".
1253 object_class CLASS
1255 The class name of the Rose::DB::Object-derived objects to be
1256 fetched. Defaults to the value returned by the object_class
1257 class method.
1258 prepare_cached BOOL
1260 If true, then DBI's prepare_cached method will be used (instead
1261 of the prepare method) when preparing the SQL statement that
1262 will fetch the objects. If omitted, the default value is
1263 determined by the dbi_prepare_cached class method.
1264 prepare_options HASHREF
1266 A reference to a hash of attributes to be passed to DBI's
1267 prepare or prepare_cached method when preparing the SQL
1268 statement.
1269 share_db BOOL
1271 If true, "db" will be passed to each Rose::DB::Object-derived
1272 object when it is constructed. Defaults to true.
1273 sql SQL
1275 The SQL query string. This parameter is required.
1276 Each column returned by the SQL query must be either a column or
1278 method name in "object_class". Column names take precedence in the
1279 case of a conflict.
1280 Returns a reference to an array of "object_class" objects.
1282 Examples:
1284 package Product::Manager;
1286 use Product;
1287 use base 'Rose::DB::Object::Manager';
1288 sub object_class { 'Product' }
1289 ...
1290 $products = Product::Manager->get_objects_from_sql(<<"EOF");
1292 SELECT * FROM products WHERE sku % 2 != 0 ORDER BY status, type
1293 EOF
1294 $products =
1296 Product::Manager->get_objects_from_sql(
1297 args => [ '2005-01-01' ],
1298 sql => 'SELECT * FROM products WHERE release_date > ?');
1299 get_objects_iterator [PARAMS]
1301 Accepts any valid get_objects arguments, but return a
1302 Rose::DB::Object::Iterator object, or undef if there was an error.
1303 get_objects_iterator_from_sql [PARAMS]
1305 Accepts any valid get_objects_from_sql arguments, but return a
1306 Rose::DB::Object::Iterator object, or undef if there was an error.
1307 get_objects_sql [PARAMS]
1309 Accepts the same arguments as get_objects, but return the SQL query
1310 string that would have been used to fetch the objects (in scalar
1311 context), or the SQL query string and a reference to an array of
1312 bind values (in list context).
1313 make_manager_methods PARAMS
1315 Create convenience wrappers for Rose::DB::Object::Manager's
1316 get_objects, get_objects_iterator, and get_objects_count class
1317 methods in the target class. These wrapper methods will not
1318 overwrite any existing methods in the target class. If there is an
1319 existing method with the same name, a fatal error will occur.
1320 PARAMS can take several forms, depending on the calling context.
1322 For a call to make_manager_methods to succeed, the following
1323 information must be determined:
1324 • object class
1326 The class of the Rose::DB::Object-derived objects to be fetched
1328 or counted.
1329 • base name or method name
1331 The base name is a string used as the basis of the method
1333 names. For example, the base name "products" might be used to
1334 create methods named "get_products", "get_products_count",
1335 "get_products_iterator", "delete_products", and
1336 "update_products".
1337 In the absence of a base name, an explicit method name may be
1339 provided instead. The method name will be used as is.
1340 • method types
1342 The types of methods that should be generated. Each method
1344 type is a wrapper for a Rose::DB::Object::Manager class method.
1345 The mapping of method type names to actual
1346 Rose::DB::Object::Manager class methods defaults to the
1347 following:
1348 Type Method
1350 -------- ----------------------
1351 objects get_objects()
1352 iterator get_objects_iterator()
1353 count get_objects_count()
1354 delete delete_objects()
1355 update update_objects()
1356 You may override the auto_manager_method_name method in the
1358 object_class's convention manager class to customize one or
1359 more of these names.
1360 • target class
1362 The class that the methods should be installed in.
1364 Here are all of the different ways that each of those pieces of
1366 information can be provided, either implicitly or explicitly as
1367 part of PARAMS.
1368 • object class
1370 If an "object_class" parameter is passed in PARAMS, then its
1372 value is used as the object class. Example:
1373 $class->make_manager_methods(object_class => 'Product', ...);
1375 If the "object_class" parameter is not passed, and if the
1377 target class inherits from Rose::DB::Object::Manager and has
1378 also defined an "object_class" method, then the return value of
1379 that method is used as the object class. Example:
1380 package Product::Manager;
1382 use Rose::DB::Object::Manager;
1384 our @ISA = qw(Rose::DB::Object::Manager);
1385 sub object_class { 'Product' }
1387 # Assume object_class parameter is not part of the ... below
1389 __PACKAGE__->make_manager_methods(...);
1390 In this case, the object class would be "Product".
1392 Finally, if none of the above conditions are met, one final
1394 option is considered. If the target class inherits from
1395 Rose::DB::Object, then the object class is set to the target
1396 class.
1397 If the object class cannot be determined in one of the ways
1399 described above, then a fatal error will occur.
1400 • base name or method name
1402 If a "base_name" parameter is passed in PARAMS, then its value
1404 is used as the base name for the generated methods. Example:
1405 $class->make_manager_methods(base_name => 'products', ...);
1407 If the "base_name" parameter is not passed, and if there is
1409 only one argument passed to the method, then the lone argument
1410 is used as the base name. Example:
1411 $class->make_manager_methods('products');
1413 (Note that, since the object class must be derived somehow,
1415 this will only work in one of the situations (described above)
1416 where the object class can be derived from the calling context
1417 or class.)
1418 If a "methods" parameter is passed with a hash ref value, then
1420 each key of the hash is used as the base name for the method
1421 types listed in the corresponding value. (See method types
1422 below for more information.)
1423 If a key of the "methods" hash ends in "()", then it is taken
1425 as the method name and is used as is. For example, the key
1426 "foo" will be used as a base name, but the key "foo()" will be
1427 used as a method name.
1428 If the base name cannot be determined in one of the ways
1430 described above, then the auto_manager_base_name method in the
1431 object_class's convention manager is called on to supply a base
1432 name.
1433 • method types
1435 If an explicit list of method types is not passed to the
1437 method, then all of the default_manager_method_types are
1438 created. Example:
1439 # Base name is determined by convention manager auto_manager_base_name()
1441 # method, all default method types created
1442 $class->make_manager_methods();
1443 # Base name is "products", all default method types created
1445 $class->make_manager_methods('products');
1446 # Base name is "products", all default method types created
1448 $class->make_manager_methods(base_name => products', ...);
1449 (Again, note that the object class must be derived somehow.)
1451 If a "methods" parameter is passed, then its value must be a
1453 reference to a hash whose keys are base names or method names,
1454 and whose values are method types or references to arrays of
1455 method types.
1456 If a key ends in "()", then it is taken as a method name and is
1458 used as is. Otherwise, it is used as a base name. For
1459 example, the key "foo" will be used as a base name, but the key
1460 "foo()" will be used as a method name.
1461 If a key is a method name and its value specifies more than one
1463 method type, then a fatal error will occur. (It's impossible
1464 to have more than one method with the same name.)
1465 Example:
1467 # Make the following methods:
1469 #
1470 # * Base name: products; method types: objects, iterators
1471 #
1472 # get_products()
1473 # get_products_iterator()
1474 #
1475 # * Method name: product_count; method type: count
1476 #
1477 # product_count()
1478 #
1479 $class->make_manager_methods(...,
1480 methods =>
1481 {
1482 'products' => [ qw(objects iterator) ],
1483 'product_count()' => 'count'
1484 });
1485 If the value of the "methods" parameter is not a reference to a
1487 hash, or if both the "methods" and "base_name" parameters are
1488 passed, then a fatal error will occur.
1489 • target class
1491 If a "target_class" parameter is passed in PARAMS, then its
1493 value is used as the target class. Example:
1494 $class->make_manager_methods(target_class => 'Product', ...);
1496 If a "target_class" parameter is not passed, and if the calling
1498 class is not Rose::DB::Object::Manager, then the calling class
1499 is used as the target class. Otherwise, the class from which
1500 the method was called is used as the target class. Examples:
1501 # Target class is Product, regardless of the calling
1503 # context or the value of $class
1504 $class->make_manager_methods(target_class => 'Product', ...);
1505 package Foo;
1507 # Target class is Foo: no target_class parameter is passed
1509 # and the calling class is Rose::DB::Object::Manager, so
1510 # the class from which the method was called (Foo) is used.
1511 Rose::DB::Object::Manager->make_manager_methods(
1512 object_class => 'Bar',
1513 base_name => 'Baz');
1514 package Bar;
1516 # Target class is Foo: no target_class parameter is passed
1518 # and the calling class is not Rose::DB::Object::Manager,
1519 # so the calling class (Foo) is used.
1520 Foo->make_manager_methods(object_class => 'Bar',
1521 base_name => 'Baz');
1522 There's a lot of flexibility in this method's arguments (although
1524 some might use the word "confusion" instead), but the examples can
1525 be pared down to a few common usage scenarios.
1526 The first is the recommended technique, as seen in the synopsis.
1528 Create a separate manager class that inherits from
1529 Rose::DB::Object::Manager, override the "object_class" method to
1530 specify the class of the objects being fetched, and then pass a
1531 lone base name argument to the call to make_manager_methods.
1532 package Product::Manager;
1534 use Rose::DB::Object::Manager;
1536 our @ISA = qw(Rose::DB::Object::Manager);
1537 sub object_class { 'Product' }
1539 __PACKAGE__->make_manager_methods('products');
1541 The second example is used to install object manager methods
1543 directly into a Rose::DB::Object-derived class. I do not recommend
1544 this practice; I consider it "semantically impure" for the class
1545 that represents a single object to also be the class that's used to
1546 fetch multiple objects. Inevitably, classes grow, and I'd like the
1547 "object manager" class to be separate from the object class itself
1548 so they can grow happily in isolation, with no potential clashes.
1549 Also, keep in mind that Rose::DB::Object and
1551 Rose::DB::Object::Manager have separate error_mode settings which
1552 must be synchronized or otherwise dealt with. Another advantage of
1553 using a separate Rose::DB::Object::Manager subclass (as described
1554 earlier) is that you can override the error_mode in your
1555 Rose::DB::Object::Manager subclass only, rather than overriding the
1556 base class Rose::DB::Object::Manager error_mode, which may affect
1557 other classes.
1558 If none of that dissuades you, here's how to do it:
1560 package Product;
1562 use Rose::DB::Object:;
1564 our @ISA = qw(Rose::DB::Object);
1565 __PACKAGE__->make_manager_methods('products');
1567 Finally, sometimes you don't want or need to use
1569 make_manager_methods at all. In fact, this method did not exist in
1570 earlier versions of this module. The formerly recommended way to
1571 use this class is still perfectly valid: subclass it and then call
1572 through to the base class methods.
1573 package Product::Manager;
1575 use Rose::DB::Object::Manager;
1577 our @ISA = qw(Rose::DB::Object::Manager);
1578 sub get_products
1580 {
1581 shift->get_objects(object_class => 'Product', @_);
1582 }
1583 sub get_products_iterator
1585 {
1586 shift->get_objects_iterator(object_class => 'Product', @_);
1587 }
1588 sub get_products_count
1590 {
1591 shift->get_objects_count(object_class => 'Product', @_);
1592 }
1593 sub delete_products
1595 {
1596 shift->delete_objects(object_class => 'Product', @_);
1597 }
1598 sub update_products
1600 {
1601 shift->update_objects(object_class => 'Product', @_);
1602 }
1603 Of course, these methods will all look very similar in each
1605 Rose::DB::Object::Manager-derived class. Creating these
1606 identically structured methods is exactly what make_manager_methods
1607 automates for you.
1608 But sometimes you want to customize these methods, in which case
1610 the "longhand" technique above becomes essential. For example,
1611 imagine that we want to extend the code in the synopsis, adding
1612 support for a "with_categories" parameter to the "get_products()"
1613 method.
1614 Product::Manager->get_products(date_created => '10/21/2001',
1616 with_categories => 1);
1617 ...
1619 sub get_products
1621 {
1622 my($class, %args) @_;
1623 if(delete $args{'with_categories'}) # boolean flag
1625 {
1626 push(@{$args{'with_objects'}}, 'category');
1627 }
1628 Rose::DB::Object::Manager->get_objects(
1630 %args, object_class => 'Product')
1631 }
1632 Here we've coerced the caller-friendly "with_categories" boolean
1634 flag parameter into the "with_objects => [ 'category' ]" pair that
1635 Rose::DB::Object::Manager's get_objects method can understand.
1636 This is the typical evolution of an object manager method. It
1638 starts out as being auto-generated by make_manager_methods, then
1639 becomes customized as new arguments are added.
1640 make_manager_method_from_sql [ NAME => SQL | PARAMS ]
1642 Create a class method in the calling class that will fetch objects
1643 using a custom SQL query. The method created will return a
1644 reference to an array of objects or a Rose::DB::Object::Iterator
1645 object, depending on whether the "iterator" parameter is set (see
1646 below).
1647 Pass either a method name and an SQL query string or name/value
1649 parameters as arguments. Valid parameters are:
1650 iterator BOOL
1652 If true, the method created will return a
1653 Rose::DB::Object::Iterator object.
1654 object_class CLASS
1656 The class name of the Rose::DB::Object-derived objects to be
1657 fetched. Defaults to the value returned by the object_class
1658 class method.
1659 params ARRAYREF
1661 To allow the method that will be created to accept named
1662 parameters (name/value pairs) instead of positional parameters,
1663 provide a reference to an array of parameter names in the order
1664 that they should be passed to the call to DBI's execute method.
1665 method NAME
1667 The name of the method to be created. This parameter is
1668 required.
1669 prepare_cached BOOL
1671 If true, then DBI's prepare_cached method will be used (instead
1672 of the prepare method) when preparing the SQL statement that
1673 will fetch the objects. If omitted, the default value is
1674 determined by the dbi_prepare_cached class method.
1675 share_db BOOL
1677 If true, "db" will be passed to each Rose::DB::Object-derived
1678 object when it is constructed. Defaults to true.
1679 sql SQL
1681 The SQL query string. This parameter is required.
1682 Each column returned by the SQL query must be either a column or
1684 method name in "object_class". Column names take precedence in the
1685 case of a conflict.
1686 Arguments passed to the created method will be passed to DBI's
1688 execute method when the query is run. The number of arguments must
1689 exactly match the number of placeholders in the SQL query.
1690 Positional parameters are required unless the "params" parameter is
1691 used. (See description above.)
1692 Returns a code reference to the method created.
1694 Examples:
1696 package Product::Manager;
1698 use base 'Rose::DB::Object::Manager';
1700 ...
1701 # Make method that takes no arguments
1703 __PACKAGE__->make_manager_method_from_sql(get_odd_products =><<"EOF");
1704 SELECT * FROM products WHERE sku % 2 != 0
1705 EOF
1706 # Make method that takes one positional parameter
1708 __PACKAGE__->make_manager_method_from_sql(get_new_products =><<"EOF");
1709 SELECT * FROM products WHERE release_date > ?
1710 EOF
1711 # Make method that takes named parameters
1713 __PACKAGE__->make_manager_method_from_sql(
1714 method => 'get_named_products',
1715 params => [ qw(type name) ],
1716 sql => <<"EOF");
1717 SELECT * FROM products WHERE type = ? AND name LIKE ?
1718 EOF
1719 ...
1721 $products = Product::Manager->get_odd_products();
1723 $products = Product::Manager->get_new_products('2005-01-01');
1725 $products =
1727 Product::Manager->get_named_products(
1728 name => 'Kite%',
1729 type => 'toy');
1730 # Make method that takes named parameters and returns an iterator
1732 __PACKAGE__->make_manager_method_from_sql(
1733 method => 'get_named_products_iterator',
1734 iterator => 1,
1735 params => [ qw(type name) ],
1736 sql => <<"EOF");
1737 SELECT * FROM products WHERE type = ? AND name LIKE ?
1738 EOF
1739 $iterator =
1741 Product::Manager->get_named_products_iterator(
1742 name => 'Kite%',
1743 type => 'toy');
1744 while(my $product = $iterator->next)
1746 {
1747 ... # do something with $product
1748 $iterator->finish if(...); # finish early?
1750 }
1751 normalize_get_objects_args [ARGS]
1753 This method takes ARGS in the forms accepted by get_objects (and
1754 other similar methods) and normalizes them into name/value pairs.
1755 Since get_objects can take arguments in many forms, this method is
1756 useful when overriding get_objects in a custom
1757 Rose::DB::Object::Manager subclass. Example:
1758 package Product::Manager;
1760 use base 'Rose::DB::Object::Manager';
1762 use Product;
1764 sub object_class { 'Product' }
1766 ...
1767 sub get_products
1769 {
1770 my($class, %args) = shift->normalize_get_objects_args(@_);
1771 # Detect, extract, and handle custom argument
1773 if(delete $args{'active_only'})
1774 {
1775 push(@{$args{'query'}}, status => 'active');
1776 }
1777 return $class->get_objects(%args); # call through to normal method
1779 }
1780 Now all of the following calls will work:
1782 $products =
1784 Product::Manager->get_products([ type => 'boat' ], sort_by => 'name');
1785 $products =
1787 Product::Manager->get_products({ name => { like => '%Dog%' } });
1788 $products =
1790 Product::Manager->get_products([ id => { gt => 123 } ], active_only => 1);
1791 object_class
1793 Returns the class name of the Rose::DB::Object-derived objects to
1794 be managed by this class. Override this method in your subclass.
1795 The default implementation returns undef.
1796 perl_class_definition
1798 Attempts to create the Perl source code that is equivalent to the
1799 current class. This works best for classes created via
1800 Rose::DB::Object::Metadata's make_manager_class method, but it will
1801 also work most of the time for classes whose methods were created
1802 using make_manager_methods.
1803 The Perl code is returned as a string. Here's an example:
1805 package My::Product::Manager;
1807 use My::Product;
1809 use Rose::DB::Object::Manager;
1811 our @ISA = qw(Rose::DB::Object::Manager);
1812 sub object_class { 'My::Product' }
1814 __PACKAGE__->make_manager_methods('products');
1816 1;
1818 update_objects [PARAMS]
1820 Update rows in a table fronted by a Rose::DB::Object-derived class
1821 based on PARAMS, where PARAMS are name/value pairs. Returns the
1822 number of rows updated, or undef if there was an error.
1823 Valid parameters are:
1825 all BOOL
1827 If set to a true value, this parameter indicates an explicit
1828 request to update all rows in the table. If both the "all" and
1829 the "where" parameters are passed, a fatal error will occur.
1830 db DB
1832 A Rose::DB-derived object used to access the database. If
1833 omitted, one will be created by calling the init_db method of
1834 the "object_class".
1835 object_class CLASS
1837 The class name of the Rose::DB::Object-derived class that
1838 fronts the table whose rows will to be updated. This parameter
1839 is required; a fatal error will occur if it is omitted.
1840 Defaults to the value returned by the object_class class
1841 method.
1842 set PARAMS
1844 The names and values of the columns to be updated. PARAMS
1845 should be a reference to a hash. Each key of the hash should
1846 be a column name or column get/set method name. If a value is
1847 a simple scalar, then it is passed through the get/set method
1848 that services the column before being incorporated into the SQL
1849 query.
1850 If a value is a reference to a scalar, then it is dereferenced
1852 and incorporated into the SQL query as-is.
1853 If a value is a reference to a hash, then it must contain a
1855 single key named "sql" and a corresponding value that will be
1856 incorporated into the SQL query as-is.
1857 Example:
1859 $num_rows_updated =
1861 Product::Manager->update_products(
1862 set =>
1863 {
1864 end_date => DateTime->now,
1865 region_num => { sql => 'region_num * -1' }
1866 count => \q(count + 1),
1867 status => 'defunct',
1868 },
1869 where =>
1870 [
1871 status => [ 'stale', 'old' ],
1872 name => { like => 'Wax%' }
1873 or =>
1874 [
1875 start_date => { gt => '2008-12-30' },
1876 end_date => { gt => 'now' },
1877 ],
1878 ]);
1879 The call above would execute an SQL statement something like
1881 the one shown below (depending on the database vendor, and
1882 assuming the current date was September 20th, 2005):
1883 UPDATE products SET
1885 end_date = '2005-09-20',
1886 region_num = region_num * -1,
1887 count = count + 1,
1888 status = 'defunct'
1889 WHERE
1890 status IN ('stale', 'old') AND
1891 name LIKE 'Wax%' AND
1892 (
1893 start_date > '2008-12-30' OR
1894 end_date > '2005-09-20'
1895 )
1896 where PARAMS
1898 The query parameters, passed as a reference to an array of
1899 name/value pairs. These PARAMS are used to formulate the
1900 "where" clause of the SQL query that is used to update the rows
1901 in the table. Arbitrarily nested boolean logic is supported.
1902 For the complete list of valid parameter names and values, see
1904 the documentation for the "query" parameter of the build_select
1905 function in the Rose::DB::Object::QueryBuilder module.
1906 If this parameter is omitted, this method will refuse to update
1908 all rows in the table and a fatal error will occur. To update
1909 all rows in a table, you must pass the "all" parameter with a
1910 true value. If both the "all" and the "where" parameters are
1911 passed, a fatal error will occur.
1912 strict_ops [BOOL]
1914 Get or set a boolean value that indicates whether using a
1915 comparison operator in the "query" that is not listed in the
1916 Rose::DB::Object::QueryBuilder documentation will cause a fatal
1917 error. The default value is false.
1918
1920 For an informal overview of Rose::DB::Object, including
1921 Rose::DB::Object::Manager, consult the Rose::DB::Object::Tutorial.
1922 perldoc Rose::DB::Object::Tutorial
1924 Any Rose::DB::Object::Manager questions or problems can be posted to
1926 the Rose::DB::Object mailing list. To subscribe to the list or view
1927 the archives, go here:
1928 <http://groups.google.com/group/rose-db-object>
1930 Although the mailing list is the preferred support mechanism, you can
1932 also email the author (see below) or file bugs using the CPAN bug
1933 tracking system:
1934 <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object>
1936 There's also a wiki and other resources linked from the Rose project
1938 home page:
1939 <http://rosecode.org>
1941
1943 John C. Siracusa (siracusa@gmail.com)
1944
1946 Copyright (c) 2010 by John C. Siracusa. All rights reserved. This
1947 program is free software; you can redistribute it and/or modify it
1948 under the same terms as Perl itself.
1949perl v5.32.1 2021-01-27 Rose::DB::Object::Manager(3)