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

FreeBSD Manual Pages


home | help
DBIx::SearchBuilder(3)User Contributed Perl DocumentatioDBIx::SearchBuilder(3)

       DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple	perl

	 use DBIx::SearchBuilder;

	 package My::Things;
	 use base qw/DBIx::SearchBuilder/;

	 sub _Init {
	     my	$self =	shift;
	     return $self->SUPER::_Init(@_);

	 sub NewItem {
	     my	$self =	shift;
	     # MyThing is a subclass of	DBIx::SearchBuilder::Record

	 package main;

	 use DBIx::SearchBuilder::Handle;
	 my $handle = DBIx::SearchBuilder::Handle->new();
	 $handle->Connect( Driver => 'SQLite', Database	=> "my_test_db"	);

	 my $sb	= My::Things->new( Handle => $handle );

	 $sb->Limit( FIELD => "column_1", VALUE	=> "matchstring" );

	 while ( my $record = $sb->Next	) {
	     print $record->my_column_name();

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

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

       Your "DBIx::SearchBuilder" subclass must	override "NewItem", 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 "DBIx::SearchBuilder::Handle" object) and table name for the
       class.  You can try to override just about every	other method here, as
       long as you think you know what you are doing.

       Each method has a lower case alias; '_' is used to separate words.  For
       example,	the method "RedoSearch"	has the	alias "redo_search".

       Creates a new SearchBuilder 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 "DBIx::SearchBuilder::Handle" (or one of its subclasses) like this:

	  my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );

       However,	if your	subclass overrides _Init you do	not need to take a
       Handle argument,	as long	as your	subclass returns an appropriate	handle
       object from the "_Handle" method.  This is useful if you	want all of
       your SearchBuilder 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 "DBIx::SearchBuilder::Handle" object as
       a "Handle" argument, although this is not necessary if your subclass
       overrides "_Handle".

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

       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 clonning then you probably	want override this method and add own
       values to the list.

   _Handle  [DBH]
       Get or set this object's	DBIx::SearchBuilder::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").

   AddRecord RECORD
       Adds a record object to this collection.

       This private internal method returns the	number of 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 "CountAll".

   _ApplyLimits	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->RowsPerPage" rows, skipping "$self->FirstRow" rows.  (That is,
       if rows are numbered starting from 0, row number	"$self->FirstRow" 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 "OrderByCols", say).

   _DistinctQuery 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 SearchBuilder will be joining multiple tables

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

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

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

       Returns the next	row from the set as an object of the type defined by
       sub NewItem.  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.

       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 and sets the record iterator to N.  The first	time
       "Next" is called	afterwards, it will return the Nth item	found by the

       You should only call GotoItem after you've already fetched at least one
       result or otherwise forced the search query to run (such	as via
       "ItemsArrayRef").  If GotoItem is called	before the search query	is
       ever run, it will reset the item	iterator and "Next" will return	the
       "First" item.

       Returns the first item

       Returns the last	item

       Returns list with distinct values of field. Limits on collection	are
       accounted, so collection	should be "UnLimit"ed to get values from the
       whole table.

       Takes paramhash with the	following keys:

	   Field name. Can be first argument without key.

	   'ASC', 'DESC' or undef. Defines whether results should be sorted or
	   not.	By default results are not sorted.

       Max Maximum number of elements to fetch.

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

       NewItem must be subclassed. It is used by DBIx::SearchBuilder to	create
       record objects for each row returned from the database.

       Takes no	arguments.  Tells DBIx::SearchBuilder that the next time it's
       asked for a record, it should requery the database

       UnLimit clears all restrictions and causes this object to return	all
       rows in the primary table.

       Limit 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 criterias will	be taken from
	   EXT_LINKFIELD and INT_LINKFIELD and added to	the criterias.	If
	   ALIAS is set, new criterias about the foreign table will be added.

	   To apply the	Limit inside the ON clause of a	previously created
	   left	join, pass this	option along with the alias returned from
	   creating the	left join. ( This is similar to	using the EXPRESSION
	   option when creating	a left join but	this allows you	to refer to
	   the join alias in the expression. )

	   Column to be	checked	against.

	   Function that should	be checked against or applied to the FIELD
	   before check. See "CombineFunctionWithField"	for rules.

	   Should always be set	and will always	be quoted.

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

	       In the case of LIKE, the	string is surrounded in	% signs.  Yes.
	       this is a bug.

	   "NOT	LIKE"
	       STARTSWITH is like LIKE,	except it only appends a % at the end
	       of the string

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

	       MATCHES is equivalent to	the database's LIKE -- that is,	it's
	       actually	LIKE, but doesn't surround the string in % signs as
	       LIKE does.

	   "IN"	and "NOT IN"
	       VALUE can be an array reference or an object inherited from
	       this class. If it's not then it's treated as any	other operator
	       and in most cases SQL would be wrong. Values in array are
	       considered as constants and quoted according to QUOTEVALUE.

	       If object is passed as VALUE then its select statement is used.
	       If no "Column" is selected then "id" is used, if	more than one
	       selected	then warning is	issued and first column	is used.

	   Can be "AND"	or "OR"	(or anything else valid	to aggregate two
	   clauses in SQL).  Special value is "none" which means that no entry
	   aggregator should be	used.  The default value is "OR".

	   on some databases, such as postgres,	setting	CASESENSITIVE to 1
	   will	make this search case sensitive

	   Subclause allows you	to assign tags to Limit	statements.
	   Statements with matching SUBCLAUSE tags will	be grouped together in
	   the final SQL statement.


	   Suppose you want to create Limit statements which would produce
	   results the same as the following SQL:

	      SELECT * FROM Users WHERE	EmailAddress OR	Name OR	RealName OR Email LIKE $query;

	   You would use the following Limit statements:

	       $folks->Limit( FIELD => 'EmailAddress', OPERATOR	=> 'LIKE', VALUE => "$query", SUBCLAUSE	=> 'groupsearch');
	       $folks->Limit( FIELD => 'Name', OPERATOR	=> 'LIKE', VALUE => "$query", SUBCLAUSE	=> 'groupsearch');
	       $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE =>	"$query", SUBCLAUSE => 'groupsearch');

       Orders the returned results by ALIAS.FIELD ORDER.

       Takes a paramhash of ALIAS, FIELD and ORDER.  ALIAS defaults to "main".
       FIELD has no default value.  ORDER defaults to ASC(ending).
       DESC(ending) is also a valid value for OrderBy.

       FIELD also accepts "FUNCTION(FIELD)" format.

   OrderByCols ARRAY
       OrderByCols takes an array of paramhashes of the	form passed to
       OrderBy.	 The result set	is ordered by the items	in the array.

       returns the ORDER BY clause for the search.

       Each hash contains the keys FIELD, FUNCTION and ALIAS. Hash combined
       into SQL	with "CombineFunctionWithField".

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

       Takes the name of a table and paramhash with TYPE and DISTINCT.

       Use TYPE	equal to "LEFT"	to indicate that it's LEFT JOIN. Old style way
       to call (see below) is also supported, but should be avoided:

	   $records->NewAlias('aTable',	'left');

       True DISTINCT value indicates that this join keeps result set distinct
       and DB side distinct is not required. See also "Join".

       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 DBIx::SearchBuilder to join two tables.

       The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
       and FIELD2. ALIAS1 and ALIAS2 are column	aliases	obtained from
       $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are	the fields 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' causes Join to preform a left
       join.  in this case, it takes ALIAS1, FIELD1, TABLE2 and	FIELD2.
       Because of the way that left joins work,	this method needs a TABLE for
       the second field	rather than merely an alias.  For this type of join,
       it will return the alias	generated by the join.

       Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
       ALIAS2/TABLE2 on	an arbitrary expression.

       It is also possible to join to a	pre-existing, already-limited
       DBIx::SearchBuilder object, by passing it as COLLECTION2, instead of
       providing an ALIAS2 or TABLE2.

       By passing true value as	DISTINCT argument join can be marked distinct.
       If all joins are	distinct then whole query is distinct and
       SearchBuilder can avoid "_DistinctQuery"	call that can hurt performance
       of the query. See also "NewAlias".

   Pages: size and changing
       Use "RowsPerPage" to set	size of	pages. "NextPage", "PrevPage",
       "FirstPage" or "GotoPage" to change pages. "FirstRow" to	do tricky


       Get or set the number of	rows returned by the database.

       Takes an	optional integer which restricts the # of rows returned	in a
       result. Zero or undef argument flush back to "return all	records
       matching	current	conditions".

       Returns the current page	size.


       Turns one page forward.


       Turns one page backwards.


       Jumps to	the first page.


       Takes an	integer	number and jumps to that page or first page if number
       omitted.	Numbering starts from zero.


       Get or set the first row	of the result set the database should return.
       Takes an	optional single	integer	argrument. Returns the currently set
       integer minus one (this is historical issue).

       Usually you don't need this method. Use "RowsPerPage", "NextPage" and
       other methods to	walk pages. It only may	be helpful to get 10 records
       starting	from 5th.

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

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

       Call to specify which columns should be loaded from the table. Each
       calls adds one column to	the set.  Takes	a hash with the	following
       named arguments:

	   Column name to fetch	or apply function to.

	   Alias of a table the	field is in; defaults to "main"

	   A SQL function that should be selected instead of FIELD or applied
	   to it.

       AS  The column alias to use instead of the default.  The	default	column
	   alias is either the column's	name (i.e. what	is passed to FIELD) if
	   it is in this table (ALIAS is 'main') or an autogenerated alias.
	   Pass	"undef"	to skip	column aliasing	entirely.

       "FIELD",	"ALIAS"	and "FUNCTION" are combined according to

       If a FIELD is provided and it is	in this	table (ALIAS is	'main'), then
       the column named	FIELD and can be accessed as usual by accessors:

	   $articles->Column(FIELD => 'id');
	   $articles->Column(FIELD => 'Subject', FUNCTION => 'SUBSTR(?,	1, 20)');
	   my $article = $articles->First;
	   my $aid = $article->id;
	   my $subject_prefix =	$article->Subject;

       Returns the alias used for the column. If FIELD was not provided, or
       was from	another	table, then the	returned column	alias should be	passed
       to the "_Value" in DBIx::SearchBuilder::Record method to	retrieve the
       column's	result:

	   my $time_alias = $articles->Column(FUNCTION => 'NOW()');
	   my $article = $articles->First;
	   my $now = $article->_Value( $time_alias );

       To choose the column's alias yourself, pass a value for the AS
       parameter (see above).  Be careful not to conflict with existing	column

       Takes a hash with three optional	arguments: FUNCTION, FIELD and ALIAS.

       Returns SQL with	all three arguments combined according to the
       following rules.

       o   FUNCTION or undef returned when FIELD is not	provided

       o   'main' ALIAS	is used	if not provided

       o   ALIAS.FIELD returned	when FUNCTION is not provided

       o   NULL	returned if FUNCTION is	'NULL'

       o   If FUNCTION contains	'?' (question marks) then they are replaced
	   with	ALIAS.FIELD and	result returned.

       o   If FUNCTION has no '(' (opening parenthesis)	then ALIAS.FIELD is
	   appended in parentheses and returned.


	    => undef

	   $obj->CombineFunctionWithField(FUNCTION => 'FOO')
	    => 'FOO'

	   $obj->CombineFunctionWithField(FIELD	=> 'foo')
	    => ''

	   $obj->CombineFunctionWithField(ALIAS	=> 'bar', FIELD	=> 'foo')
	    => ''

	   $obj->CombineFunctionWithField(FUNCTION => 'FOO(?, ?)', FIELD => 'bar')
	    => 'FOO(,'

	   $obj->CombineFunctionWithField(FUNCTION => 'FOO', ALIAS => 'bar', FIELD => 'baz')
	    => 'FOO(bar.baz)'

	   $obj->CombineFunctionWithField(FUNCTION => 'NULL', FIELD => 'bar')
	    => 'NULL'

   Columns LIST
       Specify that we want to load only the columns in	LIST

       Calls "Column", but first ensures that this table's standard columns
       are selected as well.  Thus, each call to this method results in	an
       additional column selected instead of replacing the default columns.

       Takes a hash of parameters which	is the same as "Column".  Returns the
       result of calling "Column".

   Fields TABLE
       Return a	list of	fields in TABLE.  These	fields are in the case
       presented by the	database, which	may be case-sensitive.

   HasField  { TABLE =>	undef, FIELD =>	undef }
       Returns true if TABLE has field FIELD.  Return false otherwise

       Note: Both TABLE	and FIELD are case-sensitive (See: "Fields")

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

       Always returns this collection's	table.

   QueryHint [Hint]
       If called with an argument, sets	a query	hint for this collection.

       Always returns the query	hint.

       When the	query hint is included in the SQL query, the "/* ... */" will
       be included for you. Here's an example query hint for Oracle:


       Returns the query hint formatted	appropriately for inclusion in SQL

       DEPRECATED. Alias for the "GroupByCols" method.

       DEPRECATED. Alias for the "Table" method.



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

	   SB_TEST_PG=test SB_TEST_PG_USER=postgres  make test

       Best Practical Solutions, LLC <>

       All bugs	should be reported via email to


       or via the web at


       Copyright (C) 2001-2014,	Best Practical Solutions LLC.

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

       DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.

perl v5.32.1			  2020-07-06		DBIx::SearchBuilder(3)


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

home | help