Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Jifty::DBI::CollectionUser Contributed Perl DocumentaJifty::DBI::Collection(3)

       Jifty::DBI::Collection -	Encapsulate SQL	queries	and rows in simple
       perl objects

	 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;

       This module provides an object-oriented mechanism for retrieving	and
       updating	data in	a DBI-accessible database.

       In order	to use this module, you	should create a	subclass of
       Jifty::DBI::Collection and a subclass of	Jifty::DBI::Record for each
       table that you wish to access.  (See the	documentation of
       Jifty::DBI::Record for more information on subclassing it.)

       Your Jifty::DBI::Collection subclass must override "new_item", and
       probably	should override	at least "_init" also; at the very least,
       "_init" should probably call "_handle" and "_table" to set the database
       handle (a Jifty::DBI::Handle object) and	table name for the class --
       see the "SYNOPSIS" for an example.

       Creates a new Jifty::DBI::Collection object and immediately calls
       "_init" with the	same parameters	that were passed to "new".  If you
       haven't overridden _init	in your	subclass, this means that you should
       pass in a Jifty::DBI::Handle (or	one of its subclasses) like this:

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

       However,	if your	subclass overrides "_init" you do not need to take a
       handle argument,	as long	as your	subclass takes care of calling the
       "_handle" method	somehow.  This is useful if you	want all of your
       Jifty::DBI objects to use a shared global handle	and don't want to have
       to explicitly pass it in	each time, for example.

       This method is 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 that.

       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; it's probably
       cleaner to accomplish that in a different way, though.

       Called by "clean_slate" to set up any implicit clauses that the
       collection always has.  Defaults	to doing nothing. Is passed the
       paramhash passed	into "new".

   _handle [DBH]
       Get or set this object's	Jifty::DBI::Handle object.

       This internal private method actually executes the search on the
       database; it is called automatically the	first time that	you actually
       need results (such as a call to "next").

   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.

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

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

   _apply_limits STATEMENTREF
       This routine takes a reference to a scalar containing an	SQL statement.
       It massages the statement to limit the returned rows to only
       "$self->rows_per_page" rows, skipping "$self->first_row"	rows.  (That
       is, 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 are also enforcing an ordering	on the
       rows (with "order_by", say).

   _distinct_query STATEMENTREF
       This routine takes a reference to a scalar containing an	SQL statement.
       It massages the statement to ensure a distinct result set is returned.

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

       Returns true if this collection will be joining multiple	tables

       Returns true if this collection is joining multiple table, but is
       joining other table's distinct fields, hence resulting in distinct
       resultsets.  The	behaviour is undefined if called on a non-joining

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

       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.

       Builds a	query string for a "SELECT rows	from Tables" statement for
       this collection

       The columns that	the query would	load for result	items.	By default
       it's everything.

       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.  Possible
       keys in the paramhash are:

	   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 )".

	   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.

	   Specifies an	alias which has	already	been joined to this collection
	   as the source of the	prefetched data.  "class" will also need to be

	   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
       Tales 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
       Tales 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

       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"

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

       "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".

       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, 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>) { ... }

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

       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.

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

       Returns the first item

       Returns the last	item

       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 => 'column');

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

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

       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) {	... }

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

       Each record class at least once is loaded using require.	This method is
       called each time	a record 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.

       Returns the record class	which this is a	collection of; override	this
       to subclass.  Or, pass it the name of a class as	an argument after
       creating	a "Jifty::DBI::Collection" object to create an 'anonymous'
       collection class.

       If you haven't specified	a record class,	this returns a best guess at
       the name	of the record class for	this collection.

       It uses a simple	heuristic to determine the record class	name --	It
       chops "Collection" or "s" off its own name. If you want to name your
       records and collections differently, go right ahead, but	don't say we
       didn't warn you.

       Takes no	arguments.  Tells Jifty::DBI::Collection that the next time it
       is asked	for a record, it should	re-execute the query.

       Unlimit clears all restrictions on this collection and resets it	to a
       "default" pristine state. Note, in particular, that this	means
       "unlimit" will erase ordering and grouping metadata.  To	find all rows
       without resetting this metadata,	use the	"find_all_rows"	method.

       "find_all_rows" instructs this collection class to return all rows in
       the table. (It removes the WHERE	clause from your query).

       Takes a hash of parameters with the following keys:

	   Can be set to something different than this table if	a join is
	   wanted (that	means we can't do recursive joins as for now).

	   Unless alias	is set,	the join criteria will be taken	from
	   EXT_LINKcolumn and INT_LINKcolumn and added to the criteria.	 If
	   alias is set, new criteria about the	foreign	table will be added.

	   Column to be	checked	against.

	   Should always be set	and will always	be quoted.  If the value is a
	   subclass of Jifty::DBI::Object, the value will be interpreted to be
	   the object's	id.

	   operator is the SQL operator	to use for this	phrase.	 Possible
	   choices include:

	       Any other standard SQL comparison operators that	your
	       underlying database supports are	also valid.

	   "NOT	LIKE"
	       MATCHES is like LIKE, except it surrounds the value with	%

	       starts_with is like LIKE, except	it only	appends	a % at the end
	       of the string

	       ends_with is like LIKE, except it prepends a % to the beginning
	       of the string

	       IN matches a column within a set	of values.  The	value
	       specified in the	limit should be	an array reference of values.

	   "IS NOT"
	       This is useful for when you wish	to match columns that contain
	       NULL (or	ones that don't). Use this operator and	a value	of

	   If you need to escape wildcard characters (usually _	or %) in the
	   value *explicitly* with "ESCAPE", set the  escape character here.
	   Note	that backslashes may require special treatment (e.g. Postgres
	   dislikes \ or \\ in queries unless we use the E'' syntax).

	   Can be AND or OR (or	anything else valid to aggregate two clauses
	   in SQL)

	   on some databases, such as postgres,	setting	case_sensitive to 1
	   will	make 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".  Note that this can be	used for Deep Magic, and has a high
       likelihood of allowing you to construct malformed SQL queries.  Its
       interface will probably change in the near future, but its presence
       allows for arbitrarily complex queries.

       Here's an example, to construct a SQL WHERE clause roughly equivalent
       to (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" as follows:

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


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


	 $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' );



       Where the "my_clause" can be any	name you choose.

   close_paren CLAUSE
       Places a	close parenthesis at the current location in the given
       "CLAUSE".  Note that this can be	used for Deep Magic, and has a high
       likelihood of allowing you to construct malformed SQL queries.  Its
       interface will probably change in the near future, but its presence
       allows for arbitrarily complex queries.

   order_by_cols DEPRECATED
       *DEPRECATED*. Use "order_by" method.

       Orders the returned results by column(s)	and/or function(s) on

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

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

       If you specify "function" and "column", the column (and "alias")	will
       be wrapped in the function.  This is useful for simple functions	like
       "min" or	"lower".

       Use array of hashes to order by many columns/functions.

       Calling this sets the ordering, it doesn't refine it. If	you want to
       keep previous ordering, use "add_order_by".

       The results would be unordered if method	called without arguments.

       Returns the current list	of columns.

       Same as order_by, except	it will	not reset the ordering you have
       already set.

       Clears whatever would normally get set in the ORDER BY clause.

       returns the ORDER BY clause for the search.

   group_by_cols DEPRECATED
       *DEPRECATED*. Use group_by method.

       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 use	a column as argument of	the function
       then you	have to	build correct reference	with alias in the
       "alias.column" format.

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

       The method is EXPERIMENTAL and subject to change.

       Private function	to return the "GROUP BY" clause	for this query.

   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 can be used to Join
       tables or to limit what gets found by a search.

       Join instructs Jifty::DBI::Collection to	join two tables.

       The standard form takes a paramhash with	keys "alias1", "column1",
       "alias2"	and "column2". "alias1"	and "alias2" are column	aliases
       obtained	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

       Supplying the parameter "type" => 'left'	causes Join to perform a left
       join.  in this case, it takes "alias1", "column1", "table2" and
       "column2". Because of the way that 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 "=", but you can specify other
       operators to join with.

       Passing a true value for	the "is_distinct" parameter allows one to
       specify that, despite the join, the original table's rows are will all
       still be	distinct.

       Instead of "alias1"/"column1", it's possible to specify expression, to
       join "alias2"/"table2" on an arbitrary expression.

   set_page_info [per_page => NUMBER,] [current_page =>	NUMBER]
       Sets the	current	page (one-based) and 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 that 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

       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)

       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.

       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.

       Returns the current position in the record set.

       Returns the number of records in	the set.

       Returns the total number	of potential records in	the set, ignoring any

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

       Gets/sets the DEBUG flag.

       Normally	a collection object contains record objects populated with all
       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 are interested in.

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

	   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.

   columns_in_db table
       Return a	list of	columns	in table, in lowercase.

       TODO: Why are they in lowercase?

   has_column  { table => undef, column	=> undef }
       Returns true if table has column	column.	 Return	false otherwise

   table [table]
       If called with an argument, sets	this collection's table.

       Always returns this collection's	table.

       Returns copy of the current object with all search restrictions.

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

       If your subclass	store references in the	object that should be copied
       while cloning then you probably want override this method and add own
       values to the list.

   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. The return value of "each" is the original collection.

       If the callback returns zero, the iteration ends.

       In order	to test	most of	the features of	"Jifty::DBI::Collection", you
       need to provide "make test" with	a test database.  For each DBI driver
       that you	would like to test, set	the environment	variables
       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.)  Note that the "SQLite" driver will
       automatically be	tested if "DBD::Sqlite"	is installed, using a
       temporary file as the database.	For example:

	   JDBI_TEST_PG=test JDBI_TEST_PG_USER=postgres	 make test

       Jesse Vincent <>,	Alex Vandiver
       <>, Ruslan Zakirov <>

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

	Jesse Vincent, <>

       All rights reserved.

       This library is free software; you can redistribute it and/or modify it
       under the same terms as Perl itself.

       Jifty::DBI, Jifty::DBI::Handle, Jifty::DBI::Record.

perl v5.32.0			  2014-05-29	     Jifty::DBI::Collection(3)


Want to link to this manual page? Use this URL:

home | help