DBIx::...Builder(3pm)

📦
version 1.85 · 10 versions
, in ubuntu stonking

💖 DBIx::SearchBuilder 💖

okay bestie, welcome to the DBIx::...Builder(3pm) page from the User Contributed Perl Documentation ✨ no cap, this one's a big one, so this is part 1 of 3 — grab a snack 🍓

🌸 NAME 🌸

DBIx::SearchBuilder — literally encapsulates SQL queries and rows in cute lil simple perl objects 💅

✨ SYNOPSIS ✨

use DBIx::SearchBuilder;

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

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

sub NewItem {
    my $self = shift;
    # MyThing is a subclass of DBIx::SearchBuilder::Record
    return(MyThing->new);
}

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

    # SearchBuilder returns the vanilla value fetched from database drivers. Note
    # that different drivers handle the encoding differently. Check your
    # driver's documentation to get more details.

    print $record->my_column_name();
}

# Show the generated SELECT query, possibly for debugging
my $QueryString = $sb->BuildSelectQuery();
print "Select query is: $QueryString";

🦋 DESCRIPTION 🦋

So this module hands you a whole object-oriented situation for pulling data out of a DBI-accessible database and updating it too. Very demure, very mindful. 😌

To actually use it, you're gonna make a subclass of DBIx::SearchBuilder and a subclass of DBIx::SearchBuilder::Record — one of those for every table you want to touch. (go read the DBIx::SearchBuilder::Record docs if you wanna know how subclassing that one works, bestie)

Your DBIx::SearchBuilder subclass must override NewItem, and it probably should override at least _Init too. At the bare minimum, _Init should probably be calling _Handle and _Table to set up the database handle (a DBIx::SearchBuilder::Handle object, obviously) and the table name for the class. And like, you can try overriding basically every other method in here — as long as you genuinely know what you're doing 👑.

🎀 METHOD NAMING 🎀

every single method has a lowercase alias, and _ is what splits the words apart. so for example, the method RedoSearch has the alias redo_search. iykyk 💅

🔥 METHODS 🔥

new

Makes a brand new SearchBuilder object and immediately calls _Init with the exact same parameters you passed to new. If you didn't override _Init in your subclass, that means you gotta pass in a DBIx::SearchBuilder::Handle (or one of its subclasses) like this:

my $sb = My::DBIx::SearchBuilder::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 returns a proper handle object from the _Handle method. This is lowkey the move if you want all your SearchBuilder objects sharing one global handle and you don't wanna be passing it in every single time ✨

_Init

new calls this method with whatever arguments were passed to new. By default it takes a DBIx::SearchBuilder::Handle object as a Handle argument — though that's not necessary if your subclass overrides _Handle. 💖

CleanSlate

This one completely erases all the data in the SearchBuilder object. It's handy 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 — though ngl, it's probably cleaner to just accomplish that some other way.

Clone

Returns a copy of the current object with all the search restrictions. Obsessed with the copy-paste energy fr fr 🫶

_ClonedAttributes

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 wanna override this method and add your own values to the list. 🎀

_Handle [DBH]

Get or set this object's DBIx::SearchBuilder::Handle object.

_DoSearch

This internal private method is the one that actually runs the search on the database. It gets called automatically the very first time you actually need results (like, say, a call to Next). 🙈

AddRecord RECORD

Adds a record object to this collection.

_RecordCount

This private internal method returns the number of Record objects that got saved as a result of the last query.

_DoCount

This internal private method actually executes a counting operation on the database; it's what Count and CountAll use.

_DoSearchAndCount

This internal private method actually executes the search and the counting on the database too;

_ApplyLimits STATEMENTREF

This routine takes a reference to a scalar containing an SQL statement. It massages that statement so the returned rows get limited to only $self->RowsPerPage rows, skipping $self->FirstRow rows. (so like, if rows are numbered starting from 0, row number $self->FirstRow will be the first row returned.) Heads up though: it probably makes zero sense to set these variables unless you're 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 so a distinct result set gets returned. ✨

_DistinctQueryAndCount STATEMENTREF

This routine takes a reference to a scalar containing an SQL statement. It massages the statement so a distinct result set and the total number of potential records both get returned.

_BuildJoins

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

_isJoined

Returns true if this SearchBuilder is gonna be joining multiple tables together.

_isLimited

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

_ColumnsForQuery

Returns the column selection string for use in SELECT statements.

If explicit columns have been added via Column, those get returned as-is. If _select_all_columns is set on the object, or if the package variable $DBIx::SearchBuilder::PREFER_LAZY_LOAD (controlled by the SB_PREFER_LAZY_LOAD environment variable) is false, it returns main.*. Otherwise it introspects the record class's _ClassAccessible metadata and excludes columns marked as lazy_load, foreign-collection, or record-read. Falls back to main.* if no metadata is available. 🧚

SelectAllColumns 1|0

When set to true, forces selection of all columns (main.*), bypassing any lazy_load filtering that would otherwise apply when $DBIx::SearchBuilder::PREFER_LAZY_LOAD is enabled. When set to false, it restores normal behaviour (main.* by default, or lazy_load filtering if PREFER_LAZY_LOAD is enabled).

BuildSelectQuery PreferBind => 1|0

Builds a query string for a SELECT rows from Tables statement for this SearchBuilder object. Used internally, and also super handy for debugging. Returns the query as a string.

my $QueryString = $self->BuildSelectQuery();

If PreferBind is true, the generated query will use bind variables wherever it can. If PreferBind isn't passed, it defaults to the package variable $DBIx::SearchBuilder::PREFER_BIND, which defaults to $ENV{SB_PREFER_BIND}.

To override the global $DBIx::SearchBuilder::PREFER_BIND for the current object only, you can also set _prefer_bind accordingly, like:

$sb->{_prefer_bind} = 1;

BuildSelectCountQuery PreferBind => 1|0

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

BuildSelectAndCountQuery PreferBind => 1|0

Builds a query string that's a combination of BuildSelectQuery and BuildSelectCountQuery. it's giving best of both worlds 💖

Next

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, it returns undef and resets the search so that the following call to Next will start over with the first item retrieved from the database. 🔁

GotoFirstItem

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

GotoItem

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

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. ⚠️

First

Returns the first item 🥇

Last

Returns the last item 🏁

DistinctFieldValues

Returns a list with the distinct values of a field. Limits on the collection are accounted for, so the collection should be UnLimited if you want values from the whole table.

Takes a paramhash with these keys:

Field

Field name. Can be the first argument without a key. ✨

Order

'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. 💅

ItemsArrayRef

Returns a reference to an array containing all the objects found by this search.

NewItem

NewItem must be subclassed. DBIx::SearchBuilder uses it to create record objects for each row returned from the database. 🎀

RedoSearch

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

CombineSearchAndCount 1|0

Tells DBIx::SearchBuilder whether it shall search both the records and the total count in a single query. 🫶

UnLimit

UnLimit clears all restrictions and causes this object to return all rows in the primary table. main character energy honestly 👑

Limit

Limit takes a hash of parameters with these keys:

TABLE

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

ALIAS

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 get added.

LEFTJOIN

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 lets you refer to the join alias in the expression. 💫)

FIELD

Column to be checked against.

FUNCTION

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

VALUE

Should always be set and will always be quoted.

OPERATOR

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

  • "="
  • "!="
  • "LIKE" — in the case of LIKE, the string is surrounded in % signs. Yes. this is a bug. 💀
  • "NOT LIKE" — literally the opposite vibe of LIKE, iconic 💅
  • "STARTSWITH" — it's LIKE but with main character energy at the start only: it just tacks a % onto the end of the string.
  • "ENDSWITH" — LIKE, but the % goes at the beginning instead. Prepend era 🎀
  • "MATCHES" — bestie this is just the database's LIKE. Fr fr. It is LIKE, except it does not wrap your string in % signs the way LIKE does.
  • "IN" and "NOT IN" — so VALUE here can be an array reference, or an object that inherited from this class. If it's neither of those, it gets treated like any other operator and in most cases the SQL would just be wrong 💀. Values inside the array are treated as constants and quoted according to QUOTEVALUE.

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

ENTRYAGGREGATOR — can be "AND" or "OR" (or honestly anything else that's valid for aggregating two clauses in SQL). The special value is "none", which means no entry aggregator should be used at all. Default value is "OR".

CASESENSITIVE — on some databases, like postgres, setting CASESENSITIVE to 1 makes this search case sensitive. Periodt.

SUBCLAUSE — Subclause lets you assign tags to Limit statements. Statements with matching SUBCLAUSE tags get grouped together in the final SQL statement. Group chat for queries, basically 🫶

Example:

Say you want Limit statements that produce results identical to this SQL:

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

You'd write these 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');

OrderBy PARAMHASH

Orders the returned results by ALIAS.FIELD ORDER. Simple as that ✨

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 totally valid value for OrderBy.

FIELD also accepts the FUNCTION(FIELD) format.

OrderByCols ARRAY

OrderByCols takes an array of paramhashes of the exact same form you'd pass to OrderBy. The result set gets ordered by the items in the array. Slay.

_OrderClause

returns the ORDER BY clause for the search.

GroupByCols ARRAY_OF_HASHES

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

_GroupClause

Private function that returns the "GROUP BY" clause for this query.

NewAlias

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

Use TYPE equal to "LEFT" to say it's a LEFT JOIN. The old style way to call it (see below) is also supported, but bestie, please avoid it:

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

A true DISTINCT value means this join keeps the result set distinct, so DB-side distinct is not required. See also "Join".

Returns the string of a new Alias for that table, which you can then use to Join tables or to Limit what a search finds. 👑

Join

Join tells DBIx::SearchBuilder to join two tables together. Matchmaker behavior, honestly 💖

The standard form takes a param hash with the keys ALIAS1, FIELD1, ALIAS2 and FIELD2. ALIAS1 and ALIAS2 are column aliases you got from $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in ALIAS1 and ALIAS2 that should be linked, respectively. For this kind of join, this method has no return value.

Supplying the parameter TYPE => 'left' makes Join preform a left join. In that case it takes ALIAS1, FIELD1, TABLE2 and FIELD2. Because of the way left joins work, this method needs a TABLE for the second field instead of just an alias. For this kind of join, it returns the alias generated by the join.

Instead of ALIAS1/FIELD1, you can specify EXPRESSION, so you can join ALIAS2/TABLE2 on an arbitrary expression. Flexible fr fr.

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

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

Pages: size and changing

Use "RowsPerPage" to set the size of pages. "NextPage", "PrevPage", "FirstPage" or "GotoPage" to change pages. "FirstRow" to do tricky stuff. Page-flipping era 🦋

RowsPerPage

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

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

Returns the current page size.

NextPage

Turns one page forward. 👉

PrevPage

Turns one page backwards. 👈

FirstPage

Jumps to the first page.

GotoPage

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

FirstRow

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 a historical issue, don't look at me 😭).

Usually you don't need this method. Use "RowsPerPage", "NextPage" and the other page-walking methods. It may only be helpful when you want to get 10 records starting from the 5th.

_ItemsCounter

Returns the current position in the record set.

Count

Returns the number of records in the set. When "RowsPerPage" is set, it returns the number of records in the page only — otherwise it's the same as "CountAll".

CountAll

Returns the total number of potential records in the set, ignoring any "RowsPerPage" settings entirely.

IsLast

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

Column

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

FIELD : Column name to fetch, or to apply a function to.

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

FUNCTION : 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's in this table (ALIAS is 'main'), or an autogenerated alias. Pass "undef" to skip column aliasing entirely.

"FIELD", "ALIAS" and "FUNCTION" get combined according to "CombineFunctionWithField".

If a FIELD is provided and it's in this table (ALIAS is 'main'), then the column named FIELD 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 aliases ⚠️

CombineFunctionWithField

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

Returns SQL with all three arguments combined according to these rules:

  • FUNCTION or undef is returned when FIELD is not provided
  • 'main' ALIAS is used if not provided
  • ALIAS.FIELD is returned when FUNCTION is not provided
  • NULL is returned if FUNCTION is 'NULL'
  • If FUNCTION contains '?' (question marks) then they are replaced with ALIAS.FIELD and the result is returned.
  • If FUNCTION has no '(' (opening parenthesis) then ALIAS.FIELD is appended in parentheses and returned.

Examples:

$obj->CombineFunctionWithField()
 => undef

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

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

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

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

$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. Nothing extra, we're being picky 💅

AdditionalColumn

Calls "Column", but first makes sure that this table's standard columns are selected as well. So every call to this method gives you an additional column selected instead of replacing the default columns. Additive queen 👑

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 the field FIELD. Returns 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. Call this method before performing additional operations on a collection, such as Count(), Next(), etc.

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:

$sb->QueryHint("+CURSOR_SHARING_EXACT");

QueryHintFormatted

Returns the query hint formatted appropriately for inclusion in SQL queries. ✨

💀 DEPRECATED METHODS 💀

GroupBy

DEPRECATED. Alias for the "GroupByCols" method.

SetTable

DEPRECATED. Alias for the "Table" method.

ShowRestrictions

DEPRECATED AND DOES NOTHING. Literally nothing. It's giving ghost town 👻

ImportRestrictions

DEPRECATED AND DOES NOTHING. Same energy, bestie. 😭

🧪 TESTING 🧪

okay so if you actually wanna test most of the features of DBIx::SearchBuilder, bestie, you gotta hand make test a real live test database. no database, no vibes check. 💅

For every DBI driver you wanna test, you set these three environment variables:

  • SB_TEST_FOO — the database name
  • SB_TEST_FOO_USER — the database username
  • SB_TEST_FOO_PASS — the database password

…where FOO is the driver name written in ALL UPPERCASE. 🫶 And you can test as many drivers as your little heart desires, go off honestly. (tho lowkey the appropriate DBD:: module needs to be installed first or the whole thing just won't work, no cap.)

🌸 special lil note: the SQLite driver gets tested automatically if DBD::Sqlite is installed, and it just uses a temporary file as the database. icon behavior, truly.

For example:

SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
  SB_TEST_PG=test SB_TEST_PG_USER=postgres  make test

👑 AUTHOR 👑

Best Practical Solutions, LLC <modules@bestpractical.com>

✨ CONTRIBUTORS ✨

the absolute legends, the real ones, the main characters:

  • Ansgar Burchardt <ANSGAR@cpan.org>
  • Audrey Tang <audreyt@audreyt.org>
  • Ivan Kohler <ivan-rt@420.am>
  • Martin King <Martin.King@arm.com>
  • Mathieu Arnold <mat@mat.cc>
  • Matt Knopp <mhat@netlag.com>
  • Matthew Simon Cavalletto <simonm@cavalletto.org>
  • Nick Morrott <knowledgejunkie@gmail.com>
  • Oliver Tappe <oliver@akso.de>
  • Simon Cozens <simon@cpan.org>

🐛 BUGS 🐛

found a bug? okay so don't just sit there and suffer in silence, report it pls. 💖 All bugs should be reported via email to

L<bug-DBIx-SearchBuilder@rt.cpan.org|mailto:bug-DBIx-SearchBuilder@rt.cpan.org>

or via the web at

L<rt.cpan.org|http://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-SearchBuilder>.

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

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. ✨ (yes bestie, same terms as Perl, go forth and remix, that's the whole point of free software 💜)

🔍 SEE ALSO 🔍

  • DBIx::SearchBuilder::Handle
  • DBIx::SearchBuilder::Record

and that's a wrap on the whole page, bestie!! 🎀 this lil masterpiece came out of the perl toolchain, version v5.40.1, dated 2026-05-07, filed under DBIx::...Builder(3pm). read it, love it, go build something iconic with it. periodt. 💅🌈⭐

📦 where this page comes from