Jifty:...ection(3pm)

🌸 NAME 🌸

Jifty::DBI::Collection — encapsulate SQL queries and rows in simple perl objects 💖

okay so real talk, this is the page for Jifty::DBI::Collection(3pm) from the User Contributed Perl Documentation, and bestie it is long, so buckle up. 💅

---

✨ SYNOPSIS ✨

use Jifty::DBI::Collection;

package My::ThingCollection;
use base qw/Jifty::DBI::Collection/;

package My::Thing;
use Jifty::DBI::Schema;
use Jifty::DBI::Record schema {
  column column_1 => type is 'text';
};

package main;

use Jifty::DBI::Handle;
my $handle = Jifty::DBI::Handle->new();
$handle->connect( driver => 'SQLite', database => "my_test_db" );

my $collection = My::ThingCollection->new( handle => $handle );

$collection->limit( column => "column_1", value => "matchstring" );

while ( my $record = $collection->next ) {
    print $record->id;
}

like… look at it. it's giving object-oriented database access. 🔥

---

🦋 DESCRIPTION 🦋

So this module hands you an object-oriented mechanism for grabbing and updating data in a DBI-accessible database. no cap, that's the whole vibe.

To actually use it, you gotta make a subclass of Jifty::DBI::Collection and a subclass of Jifty::DBI::Record for every single table you wanna touch. (go read the docs for Jifty::DBI::Record if you need the tea on how to subclass it, k? 🫶)

Your Jifty::DBI::Collection subclass must override new_item, and honestly it probably should override at least _init too. at the very least, _init should probably call _handle and _table to set up the database handle (a Jifty::DBI::Handle object) and the table name for the class — the SYNOPSIS above has an example, go peek. 👑

---

💜 METHODS 💜

🌟 new

Makes a brand new Jifty::DBI::Collection object and immediately calls _init with the exact same parameters you passed to new. so if you haven't overridden _init in your subclass, that means you need to pass in a Jifty::DBI::Handle (or one of its subclasses) like this:

my $collection = My::Jifty::DBI::Subclass->new( handle => $handle );

BUT — if your subclass does override _init, you don't need to take a handle argument at all, as long as your subclass handles calling the _handle method somehow. super useful if you want all your Jifty::DBI objects to share one global handle and you're not tryna pass it in every single time, bestie. 💫

🌟 _init

This one gets called by new with whatever arguments were passed to new. By default it takes a Jifty::DBI::Handle object as a handle argument and calls _handle with it. simple. clean. iconic. ✨

🌟 clean_slate

This completely erases all the data in the object. 😭 it's useful if a subclass is doing funky stuff to keep track of a search and wants to reset the object's data without losing its own data — but ngl it's probably cleaner to accomplish that in a different way, though. 🙈

🌟 implicit_clauses

Called by clean_slate to set up any implicit clauses that the collection always has. Defaults to doing absolutely nothing. It's passed the paramhash that got passed into new.

🌟 _handle [DBH]

Get or set this object's Jifty::DBI::Handle object. that's it, that's the method. 💅

🌟 _do_search

This internal private method actually executes the search on the database. It gets called automatically the first time you actually need results (like, say, a call to next). no manual labour required, she works on her own schedule. 🔥

🌟 add_record RECORD

Adds a record object to this collection.

This method automatically sets our "must redo search" flag to 0 and our "we have limits" flag to 1.

Without those two flags, counting the number of items wouldn't work. ⚠️

🌟 _record_count

This private internal method returns the number of Jifty::DBI::Record objects saved as a result of the last query.

🌟 _do_count

This internal private method actually executes a counting operation on the database; it's used by count and count_all.

🌟 _apply_limits STATEMENTREF

This routine takes a reference to a scalar containing an SQL statement. It massages that statement to limit the returned rows to only $self->rows_per_page rows, skipping $self->first_row rows. (so like, if rows are numbered starting from 0, row number $self->first_row will be the first row returned.)

Note that it probably makes no sense to set these variables unless you're also enforcing an ordering on the rows (with order_by, say). 🫠

🌟 _distinct_query STATEMENTREF

Takes a reference to a scalar containing an SQL statement, and massages it to ensure a distinct result set is returned.

🌟 _build_joins

Builds up all of the joins we need to perform this query. 🔗

🌟 _is_joined

Returns true if this collection will be joining multiple tables together.

🌟 _is_distinctly_joined

Returns true if this collection is joining multiple tables, but is joining other tables' distinct fields, hence resulting in distinct resultsets. 🧚

The behaviour is undefined if called on a non-joining collection. 💀

🌟 _is_limited

If we've limited down this search, return true. Otherwise, return false.

• 1 means "we have limits"
• -1 means "we should return all rows. We want no where clause"
• 0 means "no limits have been applied yet."

🌟 build_select_query

Builds a query string for a SELECT rows from Tables statement for this collection. 🛠️

🌟 query_columns

The columns that the query would load for result items. By default it's everything. yes, all of it, she's greedy like that. 💖

🌟 class_and_column_for_alias

Takes the alias you've assigned to a prefetched related object. Returns the class of the column we've declared that alias prefetches.

🌟 prefetch PARAMHASH

Prefetches properties of a related table, in the same query. 🎀 the possible keys in the paramhash are:

• name — This argument is required; it specifies the name of the collection or record that is to be prefetched. If the name matches a column with a refers_to relationship, the other arguments can be inferred, and this is the only parameter which needs to be passed.

It is possible to pass values for name which are not real columns in the model; these, while they won't be accessible by calling $record-> columnname on records in this collection, will still be accessible by calling $record->prefetched( columnname ).

• reference — Specifies the series of column names to traverse to extract the information. For instance, if groups referred to multiple users, and users referred to multiple phone numbers, then providing users.phones would do the two necessary joins to produce a phone collection for all users in each group.

This option defaults to the name, and is irrelevant if an alias is provided.

• alias — Specifies an alias which has already been joined to this collection as the source of the prefetched data. class will also need to be specified.
• class — Specifies the class of the data to preload. This is only necessary if alias is provided, and name is not the name of a column which provides refers_to information.

For backwards compatibility, prefetch can instead be called with alias and name as its two arguments, instead of a paramhash. 🕰️

🌟 find_column NAMES

Takes a chained list of column names, where all but the last element is the name of a column on the previous class which refers to the next collection or record. Returns a list of Jifty::DBI::Column objects for the list.

🌟 find_class NAMES

Takes a chained list of column names, where each element is the name of a column on the previous class which refers to the next collection or record. Returns an instance of the ending class, followed by the list of Jifty::DBI::Column objects traversed to get there. 🗺️

🌟 resolve_join COLUMNS

Takes a chained list of Jifty::DBI::Column objects, and performs the requisite joins to join all of them. Returns the alias of the last join. ✨

🌟 distinct_required

Returns true if Jifty::DBI expects that this result set will end up with repeated rows and should be "condensed" down to a single row for each unique primary key.

Out of the box, this method returns true if you've joined to another table. To add additional logic, feel free to override this method in your subclass.

XXX TODO: it should be possible to create a better heuristic than the simple "is it joined?" question we're asking now. Something along the lines of "are we joining this table to something that is not the other table's primary key" 🧠

🌟 build_select_count_query

Builds a SELECT statement to find the number of rows this collection would find. 🔢

🌟 do_search

Jifty::DBI::Collection usually does searches lazily. That is, it does a SELECT COUNT or a SELECT on the fly the first time you ask for results that would need one or the other. Sometimes, you need to display a count of results found before you iterate over a collection, but you know you're about to do that too. To save a bit of wear and tear on your database, call do_search before that count. 💅

🌟 next

Returns the next row from the set as an object of the type defined by sub new_item. When the complete set has been iterated through, it returns undef and resets the search such that the following call to next will start over with the first item retrieved from the database. 🔁

You may also call this method via the built-in iterator syntax. The two lines below are equivalent:

while ($_ = $collection->next) { ... }

while (<$collection>) { ... }

🌟 peek

Exactly the same as next, only it doesn't move the iterator. 👀

🌟 goto_first_item

Starts the recordset counter over from the first item. The next time you call next, you'll get the first item returned by the database, as if you'd just started iterating through the result set. 🌀

🌟 goto_item

Takes an integer, n. Sets the record counter to n. The next time you call next, you'll get the nth item.

🌟 first

Returns the first item. 🥇

🌟 last

Returns the last item. 🏁

🌟 distinct_column_values

Takes a column name and returns distinct values of the column. Only values in the current collection are returned.

Optional arguments are max and sort to limit number of values returned and it makes sense to sort results.

$col->distinct_column_values('column');

$col->distinct_column_values(column => 'column');

$col->distinct_column_values('column', max => 10, sort => 'asc');

🌟 items_array_ref

Return a reference to an array containing all objects found by this search. 📚

You may also call this method via the built-in array dereference syntax. The two lines below are equivalent:

for (@{$collection->items_array_ref}) { ... }

for (@$collection) { ... }

🌟 new_item

Should return a new object of the correct type for the current collection. The record_class method is used to determine the class of the object.

Each record class at least once is loaded using require. This method is called each time a record is fetched, so load attempts are cached to avoid penalties. If you're sure that all record classes are loaded before first use then you can override this method. ⚡

record_class 🎀

Returns the record class that this thing is a collection of. Override it if you wanna subclass. OR — plot twist — you can pass it the name of a class as an argument after you've already made a Jifty::DBI::Collection object, and boom, you get yourself an 'anonymous' collection class. ✨

If you haven't specified a record class at all, it hits you with a best guess at what the record class name should be.

The guess comes from a lil heuristic — it just chops Collection or s off its own name. Wanna name your records and collections totally differently? Go off, bestie, nobody's stopping you. But don't say we didn't warn you. 💀

redo_search 🔁

Takes zero arguments, no notes. It just tells Jifty::DBI::Collection that the next time somebody asks it for a record, it should re-execute the query. That's it, that's the whole vibe.

unlimit 🧼

unlimit clears all restrictions on this collection and resets it back to that "default" pristine state. And listen — this means unlimit will also erase your ordering and grouping metadata. Gone. Poof. 🫧

If your goal is "find all the rows but keep my metadata, please," use the find_all_rows method instead. iykyk.

find_all_rows 🌊

find_all_rows tells this collection class to return every single row in the table. (Translation: it removes the WHERE clause from your query. Just, yeets it.)

limit 🎯

Takes a hash of parameters, and the keys are:

• table — can be set to something different from this table if you want a join. (Which means, for now, no recursive joins. Sorry babes.)
• alias — unless alias is set, the join criteria gets taken from EXT_LINKcolumn and INT_LINKcolumn and added to the criteria. If alias is set, then new criteria about the foreign table get added.
• column — the column to be checked against.
• value — should always be set, and it will always be quoted. If the value is a subclass of Jifty::DBI::Object, the value gets interpreted as that object's id.
• operator — the SQL operator to use for this phrase. Possible choices include:
  • "="
  • "!=" — and honestly, any other standard SQL comparison operator your underlying database supports is also valid. Go wild.
  • "LIKE"
  • "NOT LIKE"
  • "MATCHES" — MATCHES is like LIKE, except it surrounds the value with % signs.
  • "starts_with" — like LIKE, except it only appends a % at the end of the string.
  • "ends_with" — like LIKE, except it prepends a % to the beginning of the string.
  • "IN" — IN matches a column within a set of values. The value you specify in the limit should be an array reference of values.
  • "IS"
  • "IS NOT" — this one is useful when you wish to match columns that contain NULL (or ones that don't). Use this operator with a value of "NULL".
• escape — if you need to escape wildcard characters (usually _ or %) in the value explicitly with "ESCAPE", set the escape character here. Heads up: backslashes may require special treatment (e.g. Postgres dislikes \ or \\ in queries unless we use the E'' syntax).
• entry_aggregator — can be AND or OR (or literally anything else valid for aggregating two clauses in SQL).
• case_sensitive — on some databases, like postgres, setting case_sensitive to 1 makes this search case sensitive. Note that this flag is ignored if the column is numeric.

open_paren CLAUSE 🌸

Places an open parenthesis at the current location in the given CLAUSE.

⚠️ Okay so, this can be used for Deep Magic, and it has a high likelihood of letting you construct malformed SQL queries. Its interface will probably change in the near future. But its presence is what allows for arbitrarily complex queries, so. Trade-offs, bestie. 💅

Here's an example, to construct a SQL WHERE clause roughly equivalent to this (depending on your SQL dialect):

parent = 12 AND task_type = 'action'
    AND (status = 'open'
        OR (status = 'done'
            AND completed_on >= '2008-06-26 11:39:22'))

You can use sub-clauses and open_paren and close_paren like this:

$col->limit( column => 'parent', value => 12 );
$col->limit( column => 'task_type', value => 'action' );

$col->open_paren("my_clause");

$col->limit( subclause => "my_clause", column => 'status', value => 'open' );

$col->open_paren("my_clause");

$col->limit( subclause => "my_clause", column => 'status',
    value => 'done', entry_aggregator => 'OR' );
$col->limit( subclause => "my_clause", column => 'completed_on',
    operator => '>=', value => '2008-06-26 11:39:22' );

$col->close_paren("my_clause");

$col->close_paren("my_clause");

Where "my_clause" can be literally any name you choose. Your clause, your rules. ✨

close_paren CLAUSE 🌷

Places a close parenthesis at the current location in the given CLAUSE.

⚠️ Same Deep Magic warning as its sibling: this has a high likelihood of letting you construct malformed SQL queries. Its interface will probably change in the near future, but its presence is what allows for arbitrarily complex queries. You've been warned, babe. 💀

order_by_cols DEPRECATED 💀

DEPRECATED. Use the order_by method instead. That's the whole message. Move on, queen.

order_by EMPTY|HASH|ARRAY_OF_HASHES 📊

Orders the returned results by column(s) and/or function(s) on column(s). Iconic behaviour, honestly.

Takes a paramhash of alias, column and order — or function and order. alias defaults to main. order defaults to ASC(ending), and DES(cending) is also a valid value. column and function have no default values.

Use function instead of alias and column when you wanna order by the function value. And heads up: if you want to use a column as an argument of the function, you have to build the correct reference with the alias in the alias.column format. No skipping steps. ✋

If you specify both function and column, then the column (and alias) get wrapped in the function. This is lowkey perfect for simple functions like min or lower.

Use an array of hashes to order by many columns/functions. The more the merrier. 🫶

Calling this sets the ordering — it doesn't refine it. If you wanna keep your previous ordering, use add_order_by.

And if you call this method with no arguments? The results would be unordered. Fr fr.

Returns the current list of columns.

add_order_by EMPTY|HASH|ARRAY_OF_HASHES ➕

Same as order_by, except it will not reset the ordering you've already set. It's the "yes, and…" of sorting. 💖

clear_order_by 🧹

Clears whatever would normally get set in the ORDER BY clause. Just wipes it. Fresh start energy.

_order_clause 📝

Returns the ORDER BY clause for the search. That's it — little helper, doing its job. ✨

group_by_cols DEPRECATED 💀

DEPRECATED. Use the group_by method. Bye girl.

group_by EMPTY|HASH|ARRAY_OF_HASHES 🗂️

Groups the search results by column(s) and/or function(s) on column(s).

Takes a paramhash of alias and column, or function. alias defaults to main. column and function have no default values.

Use function instead of alias and column to group by the function value. Note that if you want to use a column as an argument of the function, you have to build the correct reference with alias in the alias.column format.

Use an array of hashes to group by many columns/functions.

🧪 Heads up bestie: this method is EXPERIMENTAL and subject to change. Hold it loosely. 🦋

_group_clause 🔒

Private function to return the "GROUP BY" clause for this query. Nothing to see here, move along. 💅

new_alias table_OR_CLASS 🏷️

Takes the name of a table or a Jifty::DBI::Record subclass. Returns the string of a new Alias for that table, which you can use to join tables or to limit what gets found by a search. So handy. 💖

join 🔗

Join instructs Jifty::DBI::Collection to join two tables. Main character move.

The standard form takes a paramhash with the keys alias1, column1, alias2 and column2. alias1 and alias2 are column aliases you got from $self->new_alias or a $self->limit. column1 and column2 are the columns in alias1 and alias2 that should be linked, respectively. For this type of join, this method has no return value.

Supplying the parameter "type" => 'left' makes Join perform a left join. In this case, it takes alias1, column1, table2 and column2. Because of the way left joins work, this method needs a table for the second column rather than merely an alias. For this type of join, it will return the alias generated by the join. 🫶

The parameter operator defaults to "=", but you can specify other operators to join with. You do you.

Passing a true value for the is_distinct parameter lets you specify that, despite the join, the original table's rows will all still be distinct.

And instead of alias1/column1, it's possible to specify expression, to join alias2/table2 on an arbitrary expression. Get creative. ✨

set_page_info [per_page => NUMBER,] [current_page => NUMBER] 📖

Sets the current page (one-based, bestie, don't forget) and the number of items per page on the pager object, and pulls the number of elements from the collection. This both sets up the collection's Data::Page object so you can use its calculations, and sets the Jifty::DBI::Collection first_row and rows_per_page so that queries return values from the selected page. Two birds, one slay. 💅

If a current_page of all is passed, then paging is basically disabled (by setting per_page to the number of entries, and current_page to 1). Paging? Never heard of her.

rows_per_page 📏

Limits the number of rows returned by the database. Optionally takes an integer which restricts the # of rows returned in a result. Returns the number of rows the database should display.

first_row 🥇

Get or set the first row of the result set the database should return. Takes an optional single integer argument. Returns the currently set integer first row that the database should return. Very demure, very mindful.

_items_counter 🔢

Returns the current position in the record set.

count 🧮

Returns the number of records in the set.

count_all 🌈

Returns the total number of potential records in the set, ignoring any limit_clause. So it's giving "how many could there be" energy, not "how many am I seeing."

is_last 🏁

Returns true if the current row is the last record in the set. The final boss.

DEBUG 🐛

Gets/sets the DEBUG flag. Flipping it on is between you and the logs, bestie.

column 🎨

Normally a collection object contains record objects populated with all the columns in the database. But you can restrict the records to only contain some particular columns by calling the column method once for each column you're interested in. Selective. Love that for you. ✨

Takes a hash of parameters; the column, table and alias keys mean the same thing as in the limit method. A special function key may contain one of several possible kinds of expressions:

• "DISTINCT COUNT" — same as COUNT(DISTINCT ?).
• Expression with "?" in it — the "?" is substituted with the column name, then passed verbatim to the underlying SELECT statement.
• Expression with "(" in it — the expression is passed verbatim to the underlying SELECT.
• Any other expression — the expression is taken to be a function name. For example, "SUM" means the same thing as SUM(?).

columns LIST 📋

Specify that we want to load only the columns in LIST, which should be a list of column names. Curated. Minimal. Iconic. 🎀

columns_in_db table 🗃️

Return a list of columns in table, in lowercase. That's the whole thing. 💜

✨ TODO: Why are they in lowercase? ✨

okay bestie this is a real heading in the actual docs, the authors were lowkey shading their own naming conventions in public and honestly? iconic. 💅

---

🎀 has_column { table => undef, column => undef }

Returns true if table has column. Return false otherwise. That's it, that's the whole method, she's simple and she's honest. ✨

---

🌸 table [table]

If you call her with an argument, she sets this collection's table. Like, "bestie, we're working with this table now."

Always returns this collection's table. Always. Every time. Even when you passed her something. 💖

---

🦋 clone

Returns a copy of the current object with all search restrictions — so your limits, your order-by, your whole vibe, duplicated. 👑

---

🧚 _cloned_attributes

Returns the list of the object's fields that should be copied.

If your subclass stores references in the object that should be copied while cloning, then you probably want to override this method and add your own values to the list. Note the underscore, she's private, mind your business. 🙈

---

⭐ each CALLBACK

Executes the callback for each item in the collection. The callback receives as arguments each record, its zero-based index, and the collection. (zero-based, so the first one is 0, we're being very precise here bestie)

The return value of each is the original collection. Not a copy, the actual original one.

⚠️ If the callback returns zero, the iteration ends. Like, stops. Done. So if your callback accidentally returns 0 on item one, you're getting item one and nothing else. no cap, be careful. 💀

---

🌈 TESTING 🌈

In order to test most of the features of Jifty::DBI::Collection, you need to provide make test with a test database. Yes, an actual database. She's not playing around. 🍓

For each DBI driver that you would like to test, set the environment variables JDBI_TEST_FOO, JDBI_TEST_FOO_USER, and JDBI_TEST_FOO_PASS to a database name, database username, and database password, where FOO is the driver name in all uppercase. You can test as many drivers as you like. 🫶

The appropriate DBD:: module needs to be installed in order for the test to work. No module, no test, that's the rule. 💅

Note that the SQLite driver will automatically be tested if DBD::Sqlite is installed, using a temporary file as the database. Free test, no config, we love a low-maintenance icon. ✨

For example:

JDBI_TEST_MYSQL=test JDBI_TEST_MYSQL_USER=root JDBI_TEST_MYSQL_PASS=foo \
  JDBI_TEST_PG=test JDBI_TEST_PG_USER=postgres  make test

(yep, two spaces before make test in the original, we're keeping it, character for character, that's the whole point of this exercise bestie 🎀)

---

👑 AUTHOR 👑

Jesse Vincent <jesse@bestpractical.com>, Alex Vandiver <alexmv@bestpractical.com>, Ruslan Zakirov <ruslan.zakirov@gmail.com>

Based on DBIx::SearchBuilder::Collection, whose credits read:

Jesse Vincent, <jesse@fsck.com>

All rights reserved.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. Free software, slay, share the love. 💖

---

🌟 SEE ALSO 🌟

• Jifty::DBI
• Jifty::DBI::Handle
• Jifty::DBI::Record

---

and that's the whole page, bestie!! 🎉 this part shipped as perl v5.34.0, dated 2022-06-15, from the Jifty:...ection(3pm) manual page. go forth and query responsibly. mwah 💜🦋