Rose::...Manager(3pm)

Rose::DB::Object::Manager(3pm) is from the User Contributed Perl Documentation, and okay bestie, this page is literally the blueprint for a manager class that pulls multiple Rose::DB::Object-derived objects out of the database with complex queries. It’s giving database royalty. 👑

🌸 NAME 🌸

Rose::DB::Object::Manager — fetch multiple Rose::DB::Object-derived objects from the database using complex queries. 💅

✨ SYNOPSIS ✨

         ##
         ## Given the following Rose::DB::Object-derived classes...
         ##

         package Category;

         use base 'Rose::DB::Object';

         __PACKAGE__->meta->setup
         (
           table   => 'categories',
           columns =>
           [
             id          => { type => 'int', primary_key => 1 },
             name        => { type => 'varchar', length => 255 },
             description => { type => 'text' },
           ],

           unique_key => 'name',
         );

         ...

         package CodeName;

         use base 'Rose::DB::Object';

         __PACKAGE__->meta->setup
         (
           table   => 'code_names',
           columns =>
           [
             id          => { type => 'int', primary_key => 1 },
             product_id  => { type => 'int' },
             name        => { type => 'varchar', length => 255 },
             applied     => { type => 'date', not_null => 1 },
           ],

           foreign_keys =>
           [
             product =>
             {
               class       => 'Product',
               key_columns => { product_id => 'id' },
             },
           ],
         );

         ...

         package Product;

         use base 'Rose::DB::Object';

         __PACKAGE__->meta->setup
         (
           table   => 'products',
           columns =>
           [
             id          => { type => 'int', primary_key => 1 },
             name        => { type => 'varchar', length => 255 },
             description => { type => 'text' },
             category_id => { type => 'int' },
             region_num  => { type => 'int' },

             status =>
             {
               type      => 'varchar',
               check_in  => [ 'active', 'inactive' ],
               default   => 'inactive',
             },

             start_date  => { type => 'datetime' },
             end_date    => { type => 'datetime' },

             date_created  => { type => 'timestamp', default => 'now' },
             last_modified => { type => 'timestamp', default => 'now' },
           ],

           unique_key => 'name',

           foreign_keys =>
           [
             category =>
             {
               class       => 'Category',
               key_columns =>
               {
                 category_id => 'id',
               }
             },
           ],

           relationships =>
           [
             code_names =>
             {
               type  => 'one to many',
               class => 'CodeName',
               column_map   => { id => 'product_id' },
               manager_args =>
               {
                 sort_by => CodeName->meta->table . '.applied DESC',
               },
             },
           ],
         );

         ...

         ##
         ## Create a manager class
         ##

         package Product::Manager;

         use base 'Rose::DB::Object::Manager';

         sub object_class { 'Product' }

         __PACKAGE__->make_manager_methods('products');

         # The call above creates the methods shown below.  (The actual
         # method bodies vary slightly, but this is the gist of it...)
         #
         # sub get_products
         # {
         #   shift->get_objects(@_, object_class => 'Product');
         # }
         #
         # sub get_products_iterator
         # {
         #   shift->get_objects_iterator(@_, object_class => 'Product');
         # }
         #
         # sub get_products_count
         # {
         #   shift->get_objects_count(@_, object_class => 'Product');
         # }
         #
         # sub update_products
         # {
         #   shift->update_objects(@_, object_class => 'Product');
         # }
         #
         # sub delete_products
         # {
         #   shift->delete_objects(@_, object_class => 'Product');
         # }

         ...

         ##
         ## Use the manager class
         ##

         #
         # Get a reference to an array of objects
         #

         $products =
           Product::Manager->get_products
           (
             query =>
             [
               category_id => [ 5, 7, 22 ],
               status      => 'active',
               start_date  => { lt => '15/12/2005 6:30 p.m.' },
               name        => { like => [ '%foo%', '%bar%' ] },
             ],
             sort_by => 'category_id, start_date DESC',
             limit   => 100,
             offset  => 80,
           );

         foreach my $product (@$products)
         {
           print $product->id, ' ', $product->name, "\n";
         }

         #
         # Get objects iterator
         #

         $iterator =
           Product::Manager->get_products_iterator
           (
             query =>
             [
               category_id => [ 5, 7, 22 ],
               status      => 'active',
               start_date  => { lt => '15/12/2005 6:30 p.m.' },
               name        => { like => [ '%foo%', '%bar%' ] },
             ],
             sort_by => 'category_id, start_date DESC',
             limit   => 100,
             offset  => 80,
           );

         while($product = $iterator->next)
         {
           print $product->id, ' ', $product->name, "\n";
         }

         print $iterator->total;

         #
         # Get objects count
         #

         $count =
           Product::Manager->get_products_count
           (
             query =>
             [
               category_id => [ 5, 7, 22 ],
               status      => 'active',
               start_date  => { lt => '15/12/2005 6:30 p.m.' },
               name        => { like => [ '%foo%', '%bar%' ] },
             ],
           );

          die Product::Manager->error  unless(defined $count);

         print $count; # or Product::Manager->total()

         #
         # Get objects and sub-objects in a single query
         #

         $products =
           Product::Manager->get_products
           (
             with_objects => [ 'category', 'code_names' ],
             query =>
             [
               category_id => [ 5, 7, 22 ],
               status      => 'active',
               start_date  => { lt => '15/12/2005 6:30 p.m.' },

               # We need to disambiguate the "name" column below since it
               # appears in more than one table referenced by this query.
               # When more than one table is queried, the tables have numbered
               # aliases starting from the "main" table ("products").  The
               # "products" table is t1, "categories" is t2, and "code_names"
               # is t3.  You can read more about automatic table aliasing in
               # the documentation for the get_objects() method below.
               #
               # "category.name" and "categories.name" would work too, since
               # table and relationship names are also valid prefixes.

               't2.name'   => { like => [ '%foo%', '%bar%' ] },
             ],
             sort_by => 'category_id, start_date DESC',
             limit   => 100,
             offset  => 80,
           );

         foreach my $product (@$products)
         {
           # The call to $product->category does not hit the database
           print $product->name, ': ', $product->category->name, "\n";

           # The call to $product->code_names does not hit the database
           foreach my $code_name ($product->code_names)
           {
             # This call doesn't hit the database either
             print $code_name->name, "\n";
           }
         }

         #
         # Update objects
         #

         $num_rows_updated =
           Product::Manager->update_products(
             set =>
             {
               end_date   => DateTime->now,
               region_num => { sql => 'region_num * -1' }
               status     => 'defunct',
             },
             where =>
             [
               start_date => { lt => '1/1/1980' },
               status     => [ 'active', 'pending' ],
             ]);

         #
         # Delete objects
         #

         $num_rows_deleted =
           Product::Manager->delete_products(
             where =>
             [
               status  => [ 'stale', 'old' ],
               name    => { like => 'Wax%' },
               or =>
               [
                 start_date => { gt => '2008-12-30' },
                 end_date   => { gt => 'now' },
               ],
             ]);

🦋 DESCRIPTION 🦋

So Rose::DB::Object::Manager is a base class for classes that select rows from tables fronted by Rose::DB::Object-derived classes. Every row in the table(s) you query gets converted into the equivalent Rose::DB::Object-derived object. ✨

Class methods are provided for fetching objects all at once, one at a time through an iterator, or just getting the object count. Subclasses are expected to create syntactically pleasing wrappers for Rose::DB::Object::Manager class methods — either manually or with the make_manager_methods method. There’s a very minimal example in the synopsis above, and it’s literally right there if you need the vibe. 💅

👑 CLASS METHODS 👑

• dbi_prepare_cached [BOOL] — get or set a boolean value that says whether this class will use DBI’s prepare_cached method by default (instead of the prepare method) when preparing SQL queries. The default value is false. 🎀
• default_limit_with_subselect [BOOL] — get or set a boolean value that determines whether this class will consider using a sub-query to express "limit"/"offset" constraints when fetching sub-objects related through one of the "...-to-many" relationship types. Not all databases support this syntax, and not all queries can use it even in supported databases. If this parameter is true, the feature will be used when possible, by default. The default value is true. 🦋
• default_manager_method_types [ LIST | ARRAYREF ] — get or set the default list of method types used by the make_manager_methods method. The default list is "objects", "iterator", "count", "delete", and "update". 👑
• default_nested_joins [BOOL] — get or set a boolean value that determines whether this class will consider using nested JOIN syntax when fetching related objects. Not all databases support this syntax, and not all queries can use it even in supported databases. If this parameter is true, the feature will be used when possible, by default. The default value is true. 🌈
• default_objects_per_page [NUM] — get or set the default number of items per page, as returned by the get_objects method when used with the "page" and/or "per_page" parameters. The default value is 20. 🍓
• delete_objects [ PARAMS | ARRAYREF | HASHREF ] — delete rows from a table fronted by a Rose::DB::Object-derived class based on PARAMS, where PARAMS are name/value pairs. Returns the number of rows deleted, or undef if there was an error. 😭

If the first argument is a reference to a hash or array, it is converted to a reference to an array (if necessary) and taken as the value of the "where" parameter. 🔥

Valid parameters are:

• all BOOL — if set to a true value, this parameter indicates an explicit request to delete all rows from the table. If both the "all" and the "where" parameters are passed, a fatal error will occur. 🚨

• db DB — a Rose::DB-derived object used to access the database, bestie. If you omit it, one gets created by calling the init_db method of the object_class. easy ✨
• prepare_cached BOOL — if true, then DBI's prepare_cached method gets used instead of prepare when preparing the SQL statement that will delete the objects. If omitted, the default is whatever the dbi_prepare_cached class method says. slay 💅
• object_class CLASS — the name of the Rose::DB::Object-derived class that fronts the table you're deleting rows from. This one is required; if you omit it, a fatal error happens. Defaults to the value returned by the object_class class method. no cap 🫶
• where ARRAYREF — the query parameters, passed as a reference to an array of name/value pairs. These pairs build the "where" clause of the SQL query used to delete rows from the table. Arbitrarily nested boolean logic is supported, which is honestly iconic. 🌸

For the complete list of valid parameter names and values, see the docs for the "query" parameter of the build_select function in the Rose::DB::Object::QueryBuilder module.

If this parameter is omitted, the method refuses to delete all rows from the table and a fatal error occurs. To delete all rows from a table, you must pass the "all" parameter with a true value. If both the "all" and the "where" parameters are passed, a fatal error occurs. periodt 💀

error

Returns the text message associated with the last error, or false if there was no error. simple as that 🎀

error_mode [MODE]

Get or set the error mode for this class. The error mode decides what happens when a method of this class encounters an error. The default setting is "fatal", which means methods will croak if they encounter an error. 😭

PLEASE NOTE: the error return values described in the method docs in the rest of this document are only relevant when the error mode is set to something "non-fatal." In other words, if an error occurs, you'll never see any of those return values if the selected error mode dies or croaks or otherwise throws an exception when an error happens. so yeah, vibe check your error mode fr fr ✨

Valid values of MODE are:

• carp — call Carp::carp with the value of the object error as an argument.
• cluck — call Carp::cluck with the value of the object error as an argument.
• confess — call Carp::confess with the value of the object error as an argument.
• croak — call Carp::croak with the value of the object error as an argument.
• fatal — an alias for the "croak" mode.
• return — return a value that indicates an error has occurred, as described in the documentation for each method.

In all cases, the class's "error" attribute will also contain the error message. 💖

get_objects [ PARAMS | HASHREF | ARRAYREF ]

Get Rose::DB::Object-derived objects based on PARAMS, where PARAMS are name/value pairs. Returns a reference to a (possibly empty) array, or undef if there was an error. 🦋

If the first argument is a reference to a hash or array, it is converted to a reference to an array (if necessary) and taken as the value of the "query" parameter.

Each table that participates in the query gets aliased. Each alias is in the form "tN" where "N" is an ascending number starting with 1. The tables are numbered like this:

• The primary table is always "t1".
• The table(s) that correspond to each relationship or foreign key named in the "with_objects" parameter are numbered in order, starting with "t2".
• The table(s) that correspond to each relationship or foreign key named in the "require_objects" parameter are numbered in order, starting where the "with_objects" table aliases left off.

"Many to many" relationships have two corresponding tables, so they use two "tN" numbers. All other supported relationship types only have one table, so they use a single "tN" number. 💅

For example, imagine the "Product" class shown in the synopsis also has a "many to many" relationship named "colors." Now consider this call:

$products =
  Product::Manager->get_products(
    require_objects => [ 'category' ],
    with_objects    => [ 'code_names', 'colors' ],
    multi_many_ok   => 1,
    query           => [ status => 'defunct' ],
    sort_by         => 't1.name');

The "products" table is "t1" since it's the primary table — the table behind the "Product" class that "Product::Manager" manages. Next, the "with_objects" tables get aliased. The "code_names" table is "t2". Since "colors" is a "many to many" relationship, it gets two numbers: "t3" and "t4". Finally, the "require_objects" tables are numbered: the table behind the foreign key "category" is "t5". Here's an annotated version of the example above:

# Table aliases in the comments
$products =
  Product::Manager->get_products(
                       # t5
    require_objects => [ 'category' ],
                       # t2            t3, t4
    with_objects    => [ 'code_names', 'colors' ],
    multi_many_ok   => 1,
    query           => [ status => 'defunct' ],
    sort_by         => 't1.name'); # "products" is "t1"

Also note that the "multi_many_ok" parameter was used to suppress the warning that happens when more than one "... to many" relationship is included in the combination of "require_objects" and "with_objects" ("code_names" (one to many) and "colors" (many to many) in this case). See the docs for "multi_many_ok" below. 👑

The "tN" table aliases are for convenience, and to isolate end-user code from the actual table names. Ideally, the actual table names should only exist in one place in the entire code base: in the class definitions for each Rose::DB::OBject-derived class.

That said, when using Rose::DB::Object::Manager, the actual table names can be used as well. But be aware that some databases don't like a mix of table aliases and real table names in some kinds of queries. ⚠️

Valid parameters to get_objects are:

• allow_empty_lists BOOL — if set to true, "query" parameters with empty lists as values are allowed. For example:

@ids = (); # empty list

Product::Manager->get_products(
  query =>
  [
    id => \@ids,
    ...
  ]);

By default, passing an empty list as a value causes a fatal error. 😭

• db DB — a Rose::DB-derived object used to access the database. If omitted, one gets created by calling the init_db method of the "object_class". 💖
• debug BOOL — if true, print the generated SQL to STDERR.
• distinct [ BOOL | ARRAYREF ] — if set to any kind of true value, then the "DISTINCT" SQL keyword gets added to the "SELECT" statement. Specific values trigger the behaviors described below. ✨

If set to a simple scalar value that is true, then only the columns in the primary table ("t1") are fetched from the database.

If set to a reference to an array of table names, "tN" table aliases, or relationship or foreign key names, then only the columns from the corresponding tables get fetched. In the case of relationships that involve more than one table, only the "most distant" table is considered. (e.g., The map table is ignored in a "many to many" relationship.) Columns from the primary table ("t1") are always selected, regardless of whether or not it appears in the list.

This parameter conflicts with the "fetch_only" parameter when both provide a list of table names or aliases. In this case, if the value of the "distinct" parameter is also a reference to an array of table names or aliases, then a fatal error occurs. 💀

• fetch_only ARRAYREF — ARRAYREF should be a reference to an array of table names or "tN" table aliases. Only the columns from the corresponding tables get fetched. In the case of relationships that involve more than one table, only the "most distant" table is considered. (e.g., The map table is ignored in a "many to many" relationship.) Columns from the primary table ("t1") are always selected, regardless of whether or not it appears in the list. 🎀

This parameter conflicts with the "distinct" parameter when both provide a list of table names or aliases. In this case, then a fatal error occurs.

• for_update BOOL — if true, this parameter is translated to be the equivalent of passing the lock parameter and setting the "type" to "for update". For example, this:

for_update => 1

is equivalent to this:

lock => { type => 'for update' }

See the lock parameter below for more information. 🫶

• hints HASHREF — a reference to a hash of hints that influence the SQL generated to fetch the objects. Hints are just "suggestions" and may be ignored, depending on the actual features of the database being queried. Use the debug parameter to see the generated SQL. Most of the current hints apply to MySQL only. See the relevant documentation for more details: <http://dev.mysql.com/doc/refman/5.0/en/select.html> 🌈

The hints hash is keyed by tN table aliases or relationship names. The value of each key is a reference to a hash of hint directives. In the absence of any key for "t1" or the name of the primary table, the entire hints hash is considered applicable to the primary table.

Valid hint directives are:

• all_rows BOOL — if true, direct the database to choose the query plan that returns all the records as quickly as possible. ⭐

• big_result BOOL — set this true and you're basically telling the database "bestie, this result set is gonna be HUGE" so it can plan accordingly ✨
• buffer_result BOOL — true means the result gets forced into a temporary table. Cozy little holding pen 💅
• cache BOOL — politely ask the database to stash the result in its query cache 🎀
• calc_found_rows BOOL — true = ask the database to internally count how many rows were found, ignoring any limit or offset arguments. The way it just knows 😭
• comment TEXT — drops a comment right after the SELECT keyword in the query. Do not wrap TEXT in comment delimiters yourself, okay? The appropriate delimiters get added automatically. Leave it to the professionals 🫶
• first_rows BOOL — true makes the database pick the query plan that hands back the first result record as soon as possible. Speedrun energy 🔥
• force_index [ INDEX | ARRAYREF ] — force the use of the named indexes, given as a single index name or a reference to an array of index names. No discussion, we're using these ones 💀
• high_priority BOOL — true = this query gets higher priority. Main character query, everyone else move 🌈
• ignore_index [ INDEX | ARRAYREF ] — ignore the named indexes, given as an index name or a ref to an array of index names. Not those. Literally not those 🙈
• no_cache BOOL — the opposite vibe: ask the database not to store the result in its query cache 🦋
• small_result BOOL — true = "yeah this result set is tiny, don't stress" ⭐
• straight_join BOOL — ask the database to join the tables in exactly the order they're listed in the FROM clause of the SQL statement. No creative liberties 🎀
• strict_ops BOOL — true means any comparison operator used in the query that isn't listed in the Rose::DB::Object::QueryBuilder documentation becomes a fatal error. The default value is determined by the strict_ops class method 💅
• use_index [ INDEX | ARRAYREF ] — prefer these named indexes (name, or a ref to an array of names). It's giving suggestion, not demand 👑
• inject_results BOOL — if true, the data coming back from the database gets directly "injected" into the objects returned by this method, bypassing the constructor and column mutator methods for each object class. Default is false. This parameter is ignored (i.e., treated as if it were false) if the select parameter is passed.

This one exists for when get_objects is bottlenecked by how fast Rose::DB::Object-derived objects can be built. It's safe to set true only if the constructor and column mutator methods for all the classes involved have no side-effects (or if you're fine bypassing those side-effects).

Good news: the default Rose::DB::Object constructor and the column mutator methods created by the column classes included in the Rose::DB::Object module distribution have no side-effects, so they should be safe to use with this parameter 🧚

• limit NUM — return a maximum of NUM objects. That's it, that's the tweet ✨
• limit_with_subselect BOOL — controls whether this method will consider using a sub-query to express limit/offset constraints when fetching sub-objects related through one of the "...-to-many" relationship types. Not every database supports this syntax, and even on the ones that do, not every query can use it. If this parameter is true, the feature gets used when possible.

Default value comes from the default_limit_with_subselect class method 💜

• lock [ TYPE | HASHREF ] — select objects using some form of locking. These lock directives behave differently per database and not all directives are supported by all databases — go read your database's docs, bestie. Use the debug parameter to actually see the generated SQL 👀

The value should be a reference to a hash or a TYPE string, which is equivalent to setting the value of the type key in the hash reference form. For example, these two are equivalent:

lock => 'for update'
lock => { type => 'for update' }

Valid hash keys are:

• columns ARRAYREF — a ref to an array of column names to lock. Columns may be prefixed with their table name or their tN alias (e.g., mytable.mycol or t2.mycol) or left unadorned if they're not ambiguous. References to scalars get de-referenced and used as-is, included literally in the SQL locking clause 💅
• nowait BOOL — true = don't wait to acquire the lock. If supported, this usually happens by adding a NOWAIT directive to the SQL. impatient icon behavior ⏰
• on ARRAYREF — a ref to an array of items to lock. Depending on the database, these may be columns or tables. Both column and table names should be specified using dot-separated relationship paths.

For example, vendor.region.name would lock the name column in the table you arrive at by traversing the vendor and then the region relationships, starting from the primary table (t1). Lone column names may also be used, provided they're not ambiguous.

For locking whole tables, vendor.region would lock the table arrived at by traversing the vendor and then the region relationships. (See the require_objects parameter for more info on relationship traversal.)

Finally, references to scalars get de-referenced and used as-is, included literally in the SQL locking clause 🦋

• skip_locked BOOL — true = skip any locked rows. If supported, this is usually done by adding a SKIP LOCKED clause to the SQL. not me, not today 🙈
• tables ARRAYREF — a ref to an array of tables to lock. Table names or tN aliases may be used. References to scalars get de-referenced and used as-is, included literally in the SQL locking clause 🌸
• type TYPE — the type of lock to acquire. Valid values for TYPE are for update and shared. This hash key is required unless the for_update parameter was passed with a true value ⚠️
• wait TIME — wait for the specified TIME (generally seconds) before giving up on acquiring the lock. If supported, this is usually done by adding a WAIT ... clause to the SQL ⏳

You may pass only one of the parameters that specifies what to lock — that's columns, on, or tables. Pick one, bestie 🎀

• nested_joins BOOL — controls whether this method considers using nested JOIN syntax when fetching related objects. Not all databases support this syntax, and not all queries will use it even on databases that do. If this parameter is true, the feature gets used when possible.

Default value comes from the default_nested_joins class method 🌟

• multi_many_ok BOOL — true means don't print a warning when attempting multiple LEFT OUTER JOINs against tables related by "... to many" relationships. See the docs for the with_objects parameter for the full story 🔍
• nonlazy [ BOOL | ARRAYREF ] — by default, get_objects honors all load-on-demand columns when fetching objects. Use this parameter to override that and select all columns instead.

If the value is a true boolean value (typically 1), then all columns get fetched for all participating classes (the main object class and any sub-object classes).

The value can also be a reference to an array of relationship names — the sub-objects for each relationship name get all their columns selected. To refer to the main class (the t1 table), use the special name self 🪞

• object_args HASHREF — a ref to a hash of name/value pairs passed to the constructor of each object_class object fetched, on top of the values from the database 🎁
• object_class CLASS — the name of the Rose::DB::Object-derived objects to be fetched. Required — omit it and you get a fatal error. Defaults to the value returned by the object_class class method 💖
• offset NUM — skip the first NUM rows. If the database supports some kind of "limit with offset" syntax (e.g., LIMIT 10 OFFSET 20) then that gets used. Otherwise the first NUM rows get fetched and then discarded — wasteful but honest 😭

This parameter can only be used together with the limit parameter, otherwise a fatal error occurs. Don't try it 🚫

• page NUM — show page number NUM of objects. Pages start at 1. A page number less than or equal to zero makes the page number default to 1.

Objects per page is set by the per_page parameter. If per_page is supplied and this parameter is omitted, it defaults to 1 (the first page).

If this parameter is included along with either the limit or offset parameters, a fatal error occurs. Choose a lane 🛣️

• per_page NUM — the number of objects per page. Defaults to the value returned by the default_objects_per_page class method (20, by default).

If this parameter is included along with either the limit or offset parameters, a fatal error occurs 🔥

• prepare_cached BOOL — true means DBI's prepare_cached method gets used (instead of the prepare method) when preparing the SQL statement that fetches the objects. If omitted, the default value is determined by the dbi_prepare_cached class method ✨
• query ARRAYREF — bestie, this is the whole "what do I actually want back" list 🎀. It's the query parameters, passed as a reference to an array of name/value pairs, and those pairs get used to formulate the "where" clause of the SQL query that's used to fetch the objects from the database. Arbitrarily nested boolean logic is supported (yes, you can go feral with it 💅).

For the complete list of valid parameter names and values, go read the docs for the "query" parameter of the build_select function in the Rose::DB::Object::QueryBuilder module. That's the source of truth, not me.

This class also supports an extension to the query syntax that Rose::DB::Object::QueryBuilder supports ✨. On top of table names and aliases, column (or column method) names may be prefixed with foreign key or relationship names. And those names may be chained, with dots (".") separating the components. Chain 'em like it's a group chat reply thread 🧵.

For example — imagine three tables, "products", "vendors", and "regions", fronted by three Rose::DB::Object-derived classes, "Product", "Vendor", and "Region", respectively. Each "Product" has a "Vendor", and each "Vendor" has a "Region".

To select only products whose vendors are in the United States, use a query argument like this:

query => [ 'vendor.region.name' => 'US' ],

This assumes that the "Product" class has a relationship or foreign key named "vendor" that points to the product's "Vendor", and that the "Vendor" class has a foreign key or relationship named "region" that points to the vendor's "Region", and that 'vendor.region' (or any foreign key or relationship name chain that begins with 'vendor.region.') is an argument to the "with_objects" or "require_objects" parameters.

Heads up 🙈: the "tN" table aliases are not allowed in front of these kinds of chained relationship parameters. The chain of relationship names already specifies the target table, so any "tN" alias would be redundant at best, or present a conflict at worst. Don't do it, babe.

• require_objects ARRAYREF — only fetch rows from the primary table that have all of the associated sub-objects listed in ARRAYREF, which is a reference to an array of foreign key or relationship names defined for "object_class". The supported relationship types are "one to one", "one to many", and "many to many" 🔥.

For every foreign key or relationship name in ARRAYREF, another table gets added to the query via an implicit inner join. The join conditions are constructed automatically based on the foreign key or relationship definitions — you don't lift a finger 🫶. Note that each related table must have a Rose::DB::Object-derived class fronting it.

Foreign key and relationship names may be chained, with dots (".") separating each name. Example time: imagine three tables, "products", "vendors", and "regions", fronted by three Rose::DB::Object-derived classes, "Product", "Vendor", and "Region", respectively. Each "Product" has a "Vendor", and each "Vendor" has a "Region".

To fetch "Product"s along with their "Vendor"s, and their vendors' "Region"s, provide a "with_objects" argument like this:

require_objects => [ 'vendor.region' ],

(Yes, the manpage really does say "with_objects" there 😭 — I'm just telling you exactly what it says, no cap.) This assumes the "Product" class has a relationship or foreign key named "vendor" pointing to the product's "Vendor", and the "Vendor" class has a foreign key or relationship named "region" pointing to the vendor's "Region".

This chaining syntax can be used to traverse relationships of any kind — including "one to many" and "many to many" relationships — to an arbitrary depth. Go as deep as your heart desires 💜.

The following optional suffixes may be added after any name in the chain to override the join type used:

• ! — inner join
• ? — left outer join

Each link in a "require_objects" chain uses an inner join by default. In other words, all of these "require_objects" parameters are equivalent:

# These all mean the same thing
require_objects => [ 'vendor.region'   ]
require_objects => [ 'vendor!.region!' ]
require_objects => [ 'vendor.region!'  ]
require_objects => [ 'vendor!.region'  ]

So yeah — it's only really useful to use the "?" suffix in "require_objects" parameters (though the "!" suffixes don't do any harm 🌸). Here's a useful example of a call with a hybrid join chain:

$products =
  Product::Manager->get_products(
    require_objects => [ 'vendor.region?' ]);

Every product object returned would have associated vendor objects, but those vendor objects may or may not have associated region objects. Slay, conditional love 💖.

Note that inner joins may be implicit and nested_joins may or may not be used. When in doubt, use the debug parameter to see the generated SQL. It's giving "trust but verify" ✨.

Warning: there may be a geometric explosion of redundant data returned by the database if you include more than one "... to many" relationship in ARRAYREF. Sometimes that may still be more efficient than making additional queries to fetch these sub-objects, but honestly that all depends on the actual data. A warning will be emitted (via Carp::cluck) if you include more than one "... to many" relationship in ARRAYREF. If you're sure you know what you're doing, you can silence this warning by passing the "multi_many_ok" parameter with a true value. Main character energy only 💅.

Note: the "require_objects" list currently cannot be used to simultaneously fetch two objects that both front the same database table but are of different classes. Workarounds, bestie: make one class use a synonym or alias for one of the tables, or make one table a trivial view of the other. The objective is to get the table names to be different for each different class — even if it's just a matter of letter case, if your database is not case-sensitive when it comes to table names. iykyk 🦋.

• select [ CLAUSE | ARRAYREF ] — select only the columns specified in either a comma-separated string of column names or a reference to an array of column names. Strings are naively split between each comma (naive! like, don't expect poetry 💀). If you need more complex parsing, please use the array-reference argument format instead.

Column names should be prefixed by the appropriate "tN" table alias, the table name, or the foreign key or relationship name. The prefix should be joined to the column name with a dot ("."). Examples: "t2.name", "vendors.age".

Unprefixed columns are assumed to belong to the primary table ("t1") and are explicitly prefixed as such when selecting from more than one table. If a column name matches / AS \w+$/ then no prefix is applied.

If the column name is "*" (e.g., "t1.*") then all columns from that table are selected 👑.

If an item in the referenced array is itself a reference to a scalar, then that item will be dereferenced and passed through unmodified.

If you're selecting sub-objects via the "with_objects" or "require_objects" parameters, you must select the primary key columns from each sub-object table. Failure to do so will cause those sub-objects not to be created 😭 no cap, they just won't exist.

Be warned: you should provide some way to determine which column or method and which class an item belongs to — a tN prefix, a column name, or at the very least an "... AS ..." alias clause. Give the girlies some context, please 🎀.

If any "with_objects" or "require_objects" arguments are included in this call, the "select" list must include at least the primary key column(s) from each table that contributes to the named relationships. That's the rule, periodt.

Conflict alert 🔥: this parameter conflicts with the "fetch_only" parameter. A fatal error will occur if both are used in the same call. Pick one, babe.

If this parameter is omitted, then all columns from all participating tables are selected (optionally modified by the "nonlazy" parameter). Effortless.

• share_db BOOL — if true, "db" will be passed to each Rose::DB::Object-derived object when it is constructed. Defaults to true. Sharing is caring 🫶.
• sort_by [ CLAUSE | ARRAYREF ] — a fully formed SQL "ORDER BY ..." clause, sans the words "ORDER BY", or a reference to an array of strings or scalar references to be de-referenced as needed, joined with a comma, and appended to the "ORDER BY" clause. Put your data in order, icon behaviour ⭐.

If an argument is a reference to a scalar, then it is passed through to the ORDER BY clause unmodified.

Otherwise, within each string, any instance of "NAME." will be replaced with the appropriate "tN." table alias, where NAME is a table, foreign key, or relationship name. All unprefixed simple column names are assumed to belong to the primary table ("t1").

If you're selecting sub-objects (via "require_objects" or "with_objects") that are related through "one to many" or "many to many" relationships, the first condition in the sort order clause must be a column in the primary table (t1). If that condition is not met, the list of primary key columns will be added to the beginning of the sort order clause automatically. The library's got your back 💜.

• table_aliases BOOL — when only a single table is used in a query, this parameter controls whether or not the "tN" aliases are used. If the parameter is not passed, then tables are aliased. If it is passed with a false value, then tables are not aliased. When more than one table participates in a query, the "tN" table aliases are always used and this option is ignored. Read the room, single-table queries 🌈.
• unique_aliases BOOL — if true, and if there is no explicit value for the "select" parameter and more than one table is participating in the query, then each selected column will be given a unique alias by prefixing it with its table alias and an underscore. The default value is false. Example:

                   SELECT
                     t1.id    AS t1_id,
                     t1.name  AS t1_name,
                     t2.id    AS t2_id,
                     t2.name  AS t2_name
                   FROM
                     foo AS t1,
                     bar AS t2
                   WHERE
                     ...

These unique aliases are literally a technique of last resort for unambiguously addressing a column in a query clause. iykyk 💅

🫶 where ARRAYREF

This is an alias for the query parameter (see above).

🎀 with_map_records [ BOOL | METHOD | HASHREF ]

When you're fetching related objects through a "many to many" relationship, objects of the map class are not retrieved by default. Use this parameter to override that default behavior. ✨

• If the value is 1, then each object fetched through a mapping table will have its associated map record available through a map_record() attribute.
• If a method name is provided instead, then each object fetched through a mapping table will have its associated map record available through a method of that name.
• If the value is a reference to a hash, then the keys of the hash should be "many to many" relationship names, and the values should be the method names through which the map records will be available for each relationship.

🦋 with_objects ARRAYREF

Also fetch sub-objects (if any) associated with rows in the primary table based on a reference to an array of foreign key or relationship names defined for object_class. The supported relationship types are "one to one," "one to many," and "many to many". slay 🌈

For each foreign key or relationship name listed in ARRAYREF, another table will be added to the query via an explicit LEFT OUTER JOIN. (Foreign keys whose columns are all NOT NULL are the exception, however. They are always fetched via inner joins.) The join conditions will be constructed automatically based on the foreign key or relationship definitions. Note that each related table must have a Rose::DB::Object-derived class fronting it. See the synopsis for an example. 👑

"Many to many" relationships are a special case. They will add two tables to the query (the "map" table plus the table with the actual data), which will offset the tN table numbering by one extra table. the way that works 🫶

Foreign key and relationship names may be chained, with dots (.) separating each name. For example, imagine three tables, products, vendors, and regions, fronted by three Rose::DB::Object-derived classes, Product, Vendor, and Region, respectively. Each Product has a Vendor, and each Vendor has a Region. 💖

To fetch Products along with their Vendors, and their vendors' Regions, provide a with_objects argument like this:

with_objects => [ 'vendor.region' ],

This assumes that the Product class has a relationship or foreign key named vendor that points to the product's Vendor, and that the Vendor class has a foreign key or relationship named region that points to the vendor's Region. periodt ✨

This chaining syntax can be used to traverse relationships of any kind, including "one to many" and "many to many" relationships, to an arbitrary depth.

The following optional suffixes may be added after any name in the chain in order to override the join type used:

• ! — Inner join
• ? — Left outer join

Each link in a with_objects chain uses a left outer join by default. In other words, the following with_objects parameters are all equivalent:

# These all mean the same thing
with_objects => [ 'vendor.region'   ]
with_objects => [ 'vendor?.region?' ]
with_objects => [ 'vendor.region?'  ]
with_objects => [ 'vendor?.region'  ]

Thus, it is only really useful to use the ! suffix in with_objects parameters (though the ? suffixes don't do any harm). Here's a useful example of a call with hybrid join chain:

$products =
  Product::Manager->get_products(
    with_objects => [ 'vendor!.region' ]);

All product objects returned would have associated vendor objects, but those vendor objects may or may not have associated region objects. no cap 🦋

Note that inner joins may be implicit and nested_joins may or may not be used. When in doubt, use the debug parameter to see the generated SQL. 👀

Warning: there may be a geometric explosion of redundant data returned by the database if you include more than one "... to many" relationship in ARRAYREF. Sometimes this may still be more efficient than making additional queries to fetch these sub-objects, but that all depends on the actual data. A warning will be emitted (via Carp::cluck) if you include more than one "... to many" relationship in ARRAYREF. If you're sure you know what you're doing, you can silence this warning by passing the multi_many_ok parameter with a true value. 😭

Note: the with_objects list currently cannot be used to simultaneously fetch two objects that both front the same database table, but are of different classes. One workaround is to make one class use a synonym or alias for one of the tables. Another option is to make one table a trivial view of the other. The objective is to get the table names to be different for each different class (even if it's just a matter of letter case, if your database is not case-sensitive when it comes to table names). iykyk 💜

🌸 get_objects_count [PARAMS]

Accepts the same arguments as get_objects, but just returns the number of objects that would have been fetched, or undef if there was an error.

⭐ get_objects_from_sql [ SQL | PARAMS ]

Fetch objects using a custom SQL query. Pass either a single SQL query string or name/value parameters as arguments. Valid parameters are:

• args ARRAYREF — a reference to an array of arguments to be passed to DBI's execute method when the query is run. The number of items in this array must exactly match the number of placeholders in the SQL query.
• db DB — a Rose::DB-derived object used to access the database. If omitted, one will be created by calling the init_db method of the object_class.
• object_class CLASS — the class name of the Rose::DB::Object-derived objects to be fetched. Defaults to the value returned by the object_class class method.
• prepare_cached BOOL — if true, then DBI's prepare_cached method will be used (instead of the prepare method) when preparing the SQL statement that will fetch the objects. If omitted, the default value is determined by the dbi_prepare_cached class method.
• prepare_options HASHREF — a reference to a hash of attributes to be passed to DBI's prepare or prepare_cached method when preparing the SQL statement.
• share_db BOOL — if true, db will be passed to each Rose::DB::Object-derived object when it is constructed. Defaults to true.
• sql SQL — the SQL query string. This parameter is required.

Each column returned by the SQL query must be either a column or method name in object_class. Column names take precedence in the case of a conflict. ate 💅

Returns a reference to an array of object_class objects.

Examples:

package Product::Manager;
use Product;
use base 'Rose::DB::Object::Manager';
sub object_class { 'Product' }
...

$products = Product::Manager->get_objects_from_sql(<<"EOF");
SELECT * FROM products WHERE sku % 2 != 0 ORDER BY status, type
EOF

$products =
  Product::Manager->get_objects_from_sql(
    args => [ '2005-01-01' ],
    sql  => 'SELECT * FROM products WHERE release_date > ?');

🧚 get_objects_iterator [PARAMS]

Accepts any valid get_objects arguments, but returns a Rose::DB::Object::Iterator object, or undef if there was an error. obsessed ✨

🍓 get_objects_iterator_from_sql [PARAMS]

Accepts any valid get_objects_from_sql arguments, but returns a Rose::DB::Object::Iterator object, or undef if there was an error.

🔥 get_objects_sql [PARAMS]

Accepts the same arguments as get_objects, but returns the SQL query string that would have been used to fetch the objects (in scalar context), or the SQL query string and a reference to an array of bind values (in list context).

👑 make_manager_methods PARAMS

Create convenience wrappers for Rose::DB::Object::Manager's get_objects, get_objects_iterator, and get_objects_count class methods in the target class. These wrapper methods will not overwrite any existing methods in the target class. If there is an existing method with the same name, a fatal error will occur. 💀

PARAMS can take several forms, depending on the calling context. For a call to make_manager_methods to succeed, the following information must be determined:

• object class — the class of the Rose::DB::Object-derived objects to be fetched or counted.
• base name or method name — the base name is a string used as the basis of the method names. For example, the base name products might be used to create methods named get_products, get_products_count, get_products_iterator, delete_products, and update_products. In the absence of a base name, an explicit method name may be provided instead. The method name will be used as is.
• method types — the types of methods that should be generated. Each method type is a wrapper for a Rose::DB::Object::Manager class method. The mapping of method type names to actual Rose::DB::Object::Manager class methods defaults to the following:
• the type → method-name glow-up chart, and honestly it's giving blueprint 💅

Type        Method
--------    ----------------------
objects     get_objects()
iterator    get_objects_iterator()
count       get_objects_count()
delete      delete_objects()
update      update_objects()

and if any of those names aren't your vibe? you may override the auto_manager_method_name method in the object_class's convention manager class to customize one or more of these names. your house, your rules, your method names ✨

• target class 🎀

The class that the methods should be installed in. that's it. that's the whole bullet. no notes.

---

okay so here's the part where it gets spicy 🌶️ — Here are all of the different ways that each of those pieces of information can be provided, either implicitly or explicitly as part of PARAMS. yes, ALL of them. i'm not hiding a single one from you, bestie 🫶

• object class 🦋

If an object_class parameter is passed in PARAMS, then its value is used as the object class. Example:

$class->make_manager_methods(object_class => 'Product', ...);

If the object_class parameter is not passed, and if the target class inherits from Rose::DB::Object::Manager and has also defined an object_class method, then the return value of that method is used as the object class. Example:

package Product::Manager;

use Rose::DB::Object::Manager;
our @ISA = qw(Rose::DB::Object::Manager);

sub object_class { 'Product' }

# Assume object_class parameter is not part of the ... below
__PACKAGE__->make_manager_methods(...);

In this case, the object class would be Product. cute, right? 💖

Finally, if none of the above conditions are met, one final option is considered. If the target class inherits from Rose::DB::Object, then the object class is set to the target class.

If the object class cannot be determined in one of the ways described above, then a fatal error will occur. 😭 like, the whole thing just stops. no object class, no methods, no nothing.

• base name or method name 🌸

If a base_name parameter is passed in PARAMS, then its value is used as the base name for the generated methods. Example:

$class->make_manager_methods(base_name => 'products', ...);

If the base_name parameter is not passed, and if there is only one argument passed to the method, then the lone argument is used as the base name. Example:

$class->make_manager_methods('products');

(Note that, since the object class must be derived somehow, this will only work in one of the situations (described above) where the object class can be derived from the calling context or class.) iykyk 🧚

If a methods parameter is passed with a hash ref value, then each key of the hash is used as the base name for the method types listed in the corresponding value. (See method types below for more information.) ⭐

If a key of the methods hash ends in "()", then it is taken as the method name and is used as is. For example, the key "foo" will be used as a base name, but the key "foo()" will be used as a method name.

If the base name cannot be determined in one of the ways described above, then the auto_manager_base_name method in the object_class's convention manager is called on to supply a base name. it's got you, bestie 🫶

• method types 💅

If an explicit list of method types is not passed to the method, then all of the default_manager_method_types are created. Example:

# Base name is determined by convention manager auto_manager_base_name()
# method, all default method types created
$class->make_manager_methods();

# Base name is "products", all default method types created
$class->make_manager_methods('products');

# Base name is "products", all default method types created
$class->make_manager_methods(base_name => products', ...);

(Again, note that the object class must be derived somehow.) yes, still. that part doesn't go away 🙈

If a methods parameter is passed, then its value must be a reference to a hash whose keys are base names or method names, and whose values are method types or references to arrays of method types.

If a key ends in "()", then it is taken as a method name and is used as is. Otherwise, it is used as a base name. For example, the key "foo" will be used as a base name, but the key "foo()" will be used as a method name.

If a key is a method name and its value specifies more than one method type, then a fatal error will occur. (It's impossible to have more than one method with the same name.) 💀 yeah, that one's non-negotiable — a name is a name.

Example:

# Make the following methods:
#
# * Base name: products; method types: objects, iterators
#
#     get_products()
#     get_products_iterator()
#
# * Method name: product_count; method type: count
#
#     product_count()
#
$class->make_manager_methods(...,
  methods =>
  {
    'products'        => [ qw(objects iterator) ],
    'product_count()' => 'count'
  });

If the value of the methods parameter is not a reference to a hash, or if both the methods and base_name parameters are passed, then a fatal error will occur. 🔥 so don't do both. pick a lane.

• target class 👑

If a target_class parameter is passed in PARAMS, then its value is used as the target class. Example:

$class->make_manager_methods(target_class => 'Product', ...);

If a target_class parameter is not passed, and if the calling class is not Rose::DB::Object::Manager, then the calling class is used as the target class. Otherwise, the class from which the method was called is used as the target class. Examples:

# Target class is Product, regardless of the calling
# context or the value of $class
$class->make_manager_methods(target_class => 'Product', ...);

package Foo;

# Target class is Foo: no target_class parameter is passed
# and the calling class is Rose::DB::Object::Manager, so
# the class from which the method was called (Foo) is used.
Rose::DB::Object::Manager->make_manager_methods(
  object_class => 'Bar',
  base_name    => 'Baz');

package Bar;

# Target class is Foo: no target_class parameter is passed
# and the calling class is not Rose::DB::Object::Manager,
# so the calling class (Foo) is used.
Foo->make_manager_methods(object_class => 'Bar',
                          base_name    => 'Baz');

---

okay so, deep breath 🧘‍♀️ There's a lot of flexibility in this method's arguments (although some might use the word "confusion" instead), but the examples can be pared down to a few common usage scenarios. and honestly once you see the patterns it's SO clean, i promise.

scenario one: the one you should actually do 👑 The first is the recommended technique, as seen in the synopsis. Create a separate manager class that inherits from Rose::DB::Object::Manager, override the "object_class" method to specify the class of the objects being fetched, and then pass a lone base name argument to the call to make_manager_methods.

package Product::Manager;

use Rose::DB::Object::Manager;
our @ISA = qw(Rose::DB::Object::Manager);

sub object_class { 'Product' }

__PACKAGE__->make_manager_methods('products');

literal perfection. no cap. ✨

scenario two: the one the author is side-eyeing 🙈 The second example is used to install object manager methods directly into a Rose::DB::Object-derived class. I do not recommend this practice; I consider it "semantically impure" for the class that represents a single object to also be the class that's used to fetch multiple objects. Inevitably, classes grow, and I'd like the "object manager" class to be separate from the object class itself so they can grow happily in isolation, with no potential clashes. like, let them be their own people?? 😭

Also, keep in mind that Rose::DB::Object and Rose::DB::Object::Manager have separate error_mode settings which must be synchronized or otherwise dealt with. Another advantage of using a separate Rose::DB::Object::Manager subclass (as described earlier) is that you can override the error_mode in your Rose::DB::Object::Manager subclass only, rather than overriding the base class Rose::DB::Object::Manager error_mode, which may affect other classes. so yeah — subclass supremacy 🎀

If none of that dissuades you, here's how to do it:

package Product;

use Rose::DB::Object:;
our @ISA = qw(Rose::DB::Object);

__PACKAGE__->make_manager_methods('products');

don't say nobody warned you 💀 (affectionately)

scenario three: the longhand, which is still 100% valid 🦋 Finally, sometimes you don't want or need to use make_manager_methods at all. In fact, this method did not exist in earlier versions of this module. The formerly recommended way to use this class is still perfectly valid: subclass it and then call through to the base class methods.

package Product::Manager;

use Rose::DB::Object::Manager;
our @ISA = qw(Rose::DB::Object::Manager);

sub get_products
{
  shift->get_objects(object_class => 'Product', @_);
}

sub get_products_iterator
{
  shift->get_objects_iterator(object_class => 'Product', @_);
}

sub get_products_count
{
  shift->get_objects_count(object_class => 'Product', @_);
}

sub delete_products
{
  shift->delete_objects(object_class => 'Product', @_);
}

sub update_products
{
  shift->update_objects(object_class => 'Product', @_);
}

Of course, these methods will all look very similar in each Rose::DB::Object::Manager-derived class. Creating these identically structured methods is exactly what make_manager_methods automates for you. it's basically the "copy my homework but change it a little" of Perl modules 💖

But sometimes you want to customize these methods, in which case the "longhand" technique above becomes essential. For example, imagine that we want to extend the code in the synopsis, adding support for a "with_categories" parameter to the get_products() method. now THAT's main character energy 🌈

Product::Manager->get_products(date_created    => '10/21/2001',
                               with_categories => 1);

...

sub get_products
{
  my($class, %args) @_;

  if(delete $args{'with_categories'}) # boolean flag
  {
    push(@{$args{'with_objects'}}, 'category');
  }

  Rose::DB::Object::Manager->get_objects(
    %args, object_class => 'Product')
}

See what we did there? We took the caller-friendly with_categories boolean flag that our users love 😌 and coerced it into the with_objects => [ 'category' ] pair that Rose::DB::Object::Manager's get_objects method actually understands. Bestie, that's the whole trick. ✨

This is, like, the typical evolution of an object manager method — it starts out born fresh and innocent from make_manager_methods, and then it grows up and gets customized as new arguments get added. Character development!! 🦋

---

🌸 make_manager_method_from_sql [ NAME => SQL | PARAMS ] 🌸

Makes a class method in the calling class that fetches objects using a custom SQL query. Slay, you're writing your own SQL. 💅

The method it creates returns a reference to an array of objects, or a Rose::DB::Object::Iterator object, depending on whether the iterator parameter is set (peep below 👀).

So you pass either a method name + an SQL query string, or name/value parameters. The valid parameters are:

• iterator BOOL — if true, the method that gets created will return a Rose::DB::Object::Iterator object. 🎀
• object_class CLASS — the class name of the Rose::DB::Object-derived objects to be fetched. Defaults to whatever the object_class class method returns.
• params ARRAYREF — okay so this is the one that lets the method-that-will-be-created accept named parameters (name/value pairs) instead of positional ones. Just hand it a reference to an array of parameter names, in the order they should be passed to the call to DBI's execute method. iykyk 🫶
• method NAME — the name of the method to be created. Required, no cap. ⭐
• prepare_cached BOOL — if true, DBI's prepare_cached method gets used (instead of plain prepare) when preparing the SQL statement that fetches the objects. If you omit it, the default comes from the dbi_prepare_cached class method.
• share_db BOOL — if true, db gets passed to each Rose::DB::Object-derived object when it's constructed. Defaults to true.
• sql SQL — the SQL query string. Required. 📌

Every column returned by the SQL query has to be either a column or a method name in object_class. And in case of a conflict? Column names take precedence. 💀

Arguments passed to the created method get passed straight to DBI's execute method when the query runs. The number of arguments must exactly match the number of placeholders in the SQL query — not vibes, not approximately, exactly. 😭 Positional parameters are required unless you used the params parameter (see above, bestie).

Returns a code reference to the method created. 🎁

Examples, because we're visual learners:

package Product::Manager;

use base 'Rose::DB::Object::Manager';
...

# Make method that takes no arguments
__PACKAGE__->make_manager_method_from_sql(get_odd_products =><<"EOF");
SELECT * FROM products WHERE sku % 2 != 0
EOF

# Make method that takes one positional parameter
__PACKAGE__->make_manager_method_from_sql(get_new_products =><<"EOF");
SELECT * FROM products WHERE release_date > ?
EOF

# Make method that takes named parameters
__PACKAGE__->make_manager_method_from_sql(
  method => 'get_named_products',
  params => [ qw(type name) ],
  sql    => <<"EOF");
SELECT * FROM products WHERE type = ? AND name LIKE ?
EOF

...

$products = Product::Manager->get_odd_products();

$products = Product::Manager->get_new_products('2005-01-01');

$products =
  Product::Manager->get_named_products(
    name => 'Kite%',
    type => 'toy');

# Make method that takes named parameters and returns an iterator
__PACKAGE__->make_manager_method_from_sql(
  method   => 'get_named_products_iterator',
  iterator => 1,
  params   => [ qw(type name) ],
  sql      => <<"EOF");
SELECT * FROM products WHERE type = ? AND name LIKE ?
EOF

$iterator =
  Product::Manager->get_named_products_iterator(
    name => 'Kite%',
    type => 'toy');

while(my $product = $iterator->next)
{
  ... # do something with $product

  $iterator->finish  if(...); # finish early?
}

Pro move: that $iterator->finish at the bottom is literally you saying "ok I've seen enough" and dipping out early. Main character energy. 👑

---

🦋 normalize_get_objects_args [ARGS] 🦋

This one takes ARGS in all the forms that get_objects (and its little friends) accept, and normalizes them into name/value pairs. Since get_objects can take arguments in, like, so many forms, this method is a lifesaver when you're overriding get_objects in a custom Rose::DB::Object::Manager subclass. 🫶

Example:

package Product::Manager;

use base 'Rose::DB::Object::Manager';

use Product;

sub object_class { 'Product' }
...

sub get_products
{
  my($class, %args) = shift->normalize_get_objects_args(@_);

  # Detect, extract, and handle custom argument
  if(delete $args{'active_only'})
  {
    push(@{$args{'query'}}, status => 'active');
  }

  return $class->get_objects(%args); # call through to normal method
}

And now all of these calls work, which is honestly the dream: 😭💖

$products =
  Product::Manager->get_products([ type => 'boat' ], sort_by => 'name');

$products =
  Product::Manager->get_products({ name => { like => '%Dog%' } });

$products =
  Product::Manager->get_products([ id => { gt => 123 } ], active_only => 1);

---

⭐ object_class ⭐

Returns the class name of the Rose::DB::Object-derived objects that this class manages. Override this method in your subclass — like, that's the whole assignment, bestie. 📝 The default implementation returns undef, which is honestly giving "no thoughts head empty" 🫠.

---

🎀 perl_class_definition 🎀

Attempts to create the Perl source code that's equivalent to the current class. Obsessed. ✨

This works best for classes created via Rose::DB::Object::Metadata's make_manager_class method, but it will also work most of the time for classes whose methods were created using make_manager_methods. Not a guarantee, a vibe. 🙈

The Perl code comes back as a string. Here's an example:

package My::Product::Manager;

use My::Product;

use Rose::DB::Object::Manager;
our @ISA = qw(Rose::DB::Object::Manager);

sub object_class { 'My::Product' }

__PACKAGE__->make_manager_methods('products');

1;

---

🔥 update_objects [PARAMS] 🔥

Updates rows in a table fronted by a Rose::DB::Object-derived class, based on PARAMS, where PARAMS are name/value pairs. Returns the number of rows updated, or undef if there was an error. 💀

Valid parameters are:

• all BOOL — if set to a true value, this parameter is an explicit request to update all rows in the table. And if you pass both all and where? Fatal error. Pick a lane. 😤
• db DB — a Rose::DB-derived object used to access the database. If omitted, one gets created by calling the init_db method of the object_class.
• object_class CLASS — the class name of the Rose::DB::Object-derived class that fronts the table whose rows will be updated. This parameter is required; a fatal error occurs if it's omitted. Defaults to the value returned by the object_class class method.
• set PARAMS — the names and values of the columns to be updated. PARAMS should be a reference to a hash, and each key should be a column name or a column get/set method name. Then it depends what the value is, so pay attention bestie 👀:
  • If a value is a simple scalar, it gets passed through the get/set method that services the column before being incorporated into the SQL query.
  • If a value is a reference to a scalar, it gets dereferenced and incorporated into the SQL query as-is.
  • If a value is a reference to a hash, it must contain a single key named sql and a corresponding value that will be incorporated into the SQL query as-is.

Example:

$num_rows_updated =
  Product::Manager->update_products(
    set =>
    {
      end_date   => DateTime->now,
      region_num => { sql => 'region_num * -1' }
      count      => \q(count + 1),
      status     => 'defunct',
    },
    where =>
    [
      status  => [ 'stale', 'old' ],
      name    => { like => 'Wax%' }
      or =>
      [
        start_date => { gt => '2008-12-30' },
        end_date   => { gt => 'now' },
      ],
    ]);

The call above would execute an SQL statement something like the one shown below (depending on the database vendor, and assuming the current date was September 20th, 2005):

UPDATE products SET
  end_date   = '2005-09-20',
  region_num = region_num * -1,
  count      = count + 1,
  status     = 'defunct'
WHERE
  status IN ('stale', 'old') AND
  name LIKE 'Wax%' AND
  (
    start_date > '2008-12-30' OR
    end_date   > '2005-09-20'
  )

• where PARAMS — the query parameters, passed as a reference to an array of name/value pairs. These PARAMS are used to formulate the where clause of the SQL query that updates the rows in the table. Arbitrarily nested boolean logic is supported, go off 🎉

For the complete list of valid parameter names and values, see the documentation for the query parameter of the build_select function in the Rose::DB::Object::QueryBuilder module. 📚

Heads up, bestie: if you omit this parameter, this method will refuse to update all rows in the table and a fatal error will occur. To update all rows in a table, you must pass the all parameter with a true value. And if both all and where are passed — yep, you guessed it — fatal error. 😭 No accidental "oops I updated every row in production" moments today, thank u. 🙏

🎀 strict_ops [BOOL]

Okay bestie, this one's a getter and a setter — it's a boolean flag that decides whether using a comparison operator inside your query that is not listed in the Rose::DB::Object::QueryBuilder documentation will blow up with a fatal error 💀

The default value is false. So by default, the weird operator just... vibes through. Set it true and it becomes a whole dramatic scene (fatal error). Your call, babes ✨

---

💖 SUPPORT 🫶

So if you want the informal, chill, big-picture tour of Rose::DB::Object — and yes, that includes Rose::DB::Object::Manager — go read the Rose::DB::Object::Tutorial. It's giving "explain it like I'm your bestie":

perldoc Rose::DB::Object::Tutorial

Got questions? Got problems? Any Rose::DB::Object::Manager stuff at all? The Rose::DB::Object mailing list is right there for you. To subscribe or creep the archives, go here:

http://groups.google.com/group/rose-db-object

The mailing list is the preferred support mechanism (iconic, honestly, we love a community), but you can also email the author (scroll down, bestie 👇) or file bugs using the CPAN bug tracking system:

http://rt.cpan.org/NoAuth/Bugs.html?Dist=Rose-DB-Object

And there's also a wiki plus other resources linked from the Rose project home page, if you're in a browsing mood:

http://rosecode.org

---

👑 AUTHOR 👑

John C. Siracusa (siracusa@gmail.com)

---

🌸 LICENSE 🌸

Copyright (c) 2010 by John C. Siracusa. All rights reserved. This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

---

fr fr, that's the whole page — every last crumb of it 🍓 no cap, nothing skipped 💅

And that's a wrap, bestie! This was Rose::...Manager(3pm) from perl v5.40.1, dated 2025-08-21 — slay, go build something iconic with it 🌈✨🦋