1DBIx::SearchBuilder(3)User Contributed Perl DocumentationDBIx::SearchBuilder(3)
2
6 DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl
7 objects
8
10 use DBIx::SearchBuilder;
11 package My::Things;
13 use base qw/DBIx::SearchBuilder/;
14 sub _Init {
16 my $self = shift;
17 $self->Table('Things');
18 return $self->SUPER::_Init(@_);
19 }
20 sub NewItem {
22 my $self = shift;
23 # MyThing is a subclass of DBIx::SearchBuilder::Record
24 return(MyThing->new);
25 }
26 package main;
28 use DBIx::SearchBuilder::Handle;
30 my $handle = DBIx::SearchBuilder::Handle->new();
31 $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );
32 my $sb = My::Things->new( Handle => $handle );
34 $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );
36 while ( my $record = $sb->Next ) {
38 print $record->my_column_name();
39 }
40
42 This module provides an object-oriented mechanism for retrieving and
43 updating data in a DBI-accesible database.
44 In order to use this module, you should create a subclass of
46 "DBIx::SearchBuilder" and a subclass of "DBIx::SearchBuilder::Record"
47 for each table that you wish to access. (See the documentation of
48 "DBIx::SearchBuilder::Record" for more information on subclassing it.)
49 Your "DBIx::SearchBuilder" subclass must override "NewItem", and
51 probably should override at least "_Init" also; at the very least,
52 "_Init" should probably call "_Handle" and "_Table" to set the database
53 handle (a "DBIx::SearchBuilder::Handle" object) and table name for the
54 class. You can try to override just about every other method here, as
55 long as you think you know what you are doing.
56
58 Each method has a lower case alias; '_' is used to separate words. For
59 example, the method "RedoSearch" has the alias "redo_search".
60
62 new
63 Creates a new SearchBuilder object and immediately calls "_Init" with
64 the same parameters that were passed to "new". If you haven't
65 overridden "_Init" in your subclass, this means that you should pass in
66 a "DBIx::SearchBuilder::Handle" (or one of its subclasses) like this:
67 my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
69 However, if your subclass overrides _Init you do not need to take a
71 Handle argument, as long as your subclass returns an appropriate handle
72 object from the "_Handle" method. This is useful if you want all of
73 your SearchBuilder objects to use a shared global handle and don't want
74 to have to explicitly pass it in each time, for example.
75 _Init
77 This method is called by "new" with whatever arguments were passed to
78 "new". By default, it takes a "DBIx::SearchBuilder::Handle" object as
79 a "Handle" argument, although this is not necessary if your subclass
80 overrides "_Handle".
81 CleanSlate
83 This completely erases all the data in the SearchBuilder object. It's
84 useful if a subclass is doing funky stuff to keep track of a search and
85 wants to reset the SearchBuilder data without losing its own data; it's
86 probably cleaner to accomplish that in a different way, though.
87 Clone
89 Returns copy of the current object with all search restrictions.
90 _ClonedAttributes
92 Returns list of the object's fields that should be copied.
93 If your subclass store references in the object that should be copied
95 while clonning then you probably want override this method and add own
96 values to the list.
97 _Handle [DBH]
99 Get or set this object's DBIx::SearchBuilder::Handle object.
100 _DoSearch
102 This internal private method actually executes the search on the
103 database; it is called automatically the first time that you actually
104 need results (such as a call to "Next").
105 AddRecord RECORD
107 Adds a record object to this collection.
108 _RecordCount
110 This private internal method returns the number of Record objects saved
111 as a result of the last query.
112 _DoCount
114 This internal private method actually executes a counting operation on
115 the database; it is used by "Count" and "CountAll".
116 _DoSearchAndCount
118 This internal private method actually executes the search and also
119 counting on the database;
120 _ApplyLimits STATEMENTREF
122 This routine takes a reference to a scalar containing an SQL statement.
123 It massages the statement to limit the returned rows to only
124 "$self->RowsPerPage" rows, skipping "$self->FirstRow" rows. (That is,
125 if rows are numbered starting from 0, row number "$self->FirstRow" will
126 be the first row returned.) Note that it probably makes no sense to
127 set these variables unless you are also enforcing an ordering on the
128 rows (with "OrderByCols", say).
129 _DistinctQuery STATEMENTREF
131 This routine takes a reference to a scalar containing an SQL statement.
132 It massages the statement to ensure a distinct result set is returned.
133 _DistinctQueryAndCount STATEMENTREF
135 This routine takes a reference to a scalar containing an SQL statement.
136 It massages the statement to ensure a distinct result set and total
137 number of potential records are returned.
138 _BuildJoins
140 Build up all of the joins we need to perform this query.
141 _isJoined
143 Returns true if this SearchBuilder will be joining multiple tables
144 together.
145 _isLimited
147 If we've limited down this search, return true. Otherwise, return
148 false.
149 BuildSelectQuery PreferBind => 1|0
151 Builds a query string for a "SELECT rows from Tables" statement for
152 this SearchBuilder object
153 If "PreferBind" is true, the generated query will use bind variables
155 where possible. If "PreferBind" is not passed, it defaults to package
156 variable $DBIx::SearchBuilder::PREFER_BIND, which defaults to
157 $ENV{SB_PREFER_BIND}.
158 To override global $DBIx::SearchBuilder::PREFER_BIND for current object
160 only, you can also set "_prefer_bind" accordingly, e.g.
161 $sb->{_prefer_bind} = 1;
163 BuildSelectCountQuery PreferBind => 1|0
165 Builds a SELECT statement to find the number of rows this SearchBuilder
166 object would find.
167 BuildSelectAndCountQuery PreferBind => 1|0
169 Builds a query string that is a combination of BuildSelectQuery and
170 BuildSelectCountQuery.
171 Next
173 Returns the next row from the set as an object of the type defined by
174 sub NewItem. When the complete set has been iterated through, returns
175 undef and resets the search such that the following call to Next will
176 start over with the first item retrieved from the database.
177 GotoFirstItem
179 Starts the recordset counter over from the first item. The next time
180 you call Next, you'll get the first item returned by the database, as
181 if you'd just started iterating through the result set.
182 GotoItem
184 Takes an integer N and sets the record iterator to N. The first time
185 "Next" is called afterwards, it will return the Nth item found by the
186 search.
187 You should only call GotoItem after you've already fetched at least one
189 result or otherwise forced the search query to run (such as via
190 "ItemsArrayRef"). If GotoItem is called before the search query is
191 ever run, it will reset the item iterator and "Next" will return the
192 "First" item.
193 First
195 Returns the first item
196 Last
198 Returns the last item
199 DistinctFieldValues
201 Returns list with distinct values of field. Limits on collection are
202 accounted, so collection should be "UnLimit"ed to get values from the
203 whole table.
204 Takes paramhash with the following keys:
206 Field
208 Field name. Can be first argument without key.
209 Order
211 'ASC', 'DESC' or undef. Defines whether results should be sorted or
212 not. By default results are not sorted.
213 Max Maximum number of elements to fetch.
215 ItemsArrayRef
217 Return a reference to an array containing all objects found by this
218 search.
219 NewItem
221 NewItem must be subclassed. It is used by DBIx::SearchBuilder to create
222 record objects for each row returned from the database.
223 RedoSearch
225 Takes no arguments. Tells DBIx::SearchBuilder that the next time it's
226 asked for a record, it should requery the database
227 CombineSearchAndCount 1|0
229 Tells DBIx::SearchBuilder if it shall search both records and the total
230 count in a single query.
231 UnLimit
233 UnLimit clears all restrictions and causes this object to return all
234 rows in the primary table.
235 Limit
237 Limit takes a hash of parameters with the following keys:
238 TABLE
240 Can be set to something different than this table if a join is
241 wanted (that means we can't do recursive joins as for now).
242 ALIAS
244 Unless ALIAS is set, the join criterias will be taken from
245 EXT_LINKFIELD and INT_LINKFIELD and added to the criterias. If
246 ALIAS is set, new criterias about the foreign table will be added.
247 LEFTJOIN
249 To apply the Limit inside the ON clause of a previously created
250 left join, pass this option along with the alias returned from
251 creating the left join. ( This is similar to using the EXPRESSION
252 option when creating a left join but this allows you to refer to
253 the join alias in the expression. )
254 FIELD
256 Column to be checked against.
257 FUNCTION
259 Function that should be checked against or applied to the FIELD
260 before check. See "CombineFunctionWithField" for rules.
261 VALUE
263 Should always be set and will always be quoted.
264 OPERATOR
266 OPERATOR is the SQL operator to use for this phrase. Possible
267 choices include:
268 "="
270 "!="
271 "LIKE"
272 In the case of LIKE, the string is surrounded in % signs. Yes.
273 this is a bug.
274 "NOT LIKE"
276 "STARTSWITH"
277 STARTSWITH is like LIKE, except it only appends a % at the end
278 of the string
279 "ENDSWITH"
281 ENDSWITH is like LIKE, except it prepends a % to the beginning
282 of the string
283 "MATCHES"
285 MATCHES is equivalent to the database's LIKE -- that is, it's
286 actually LIKE, but doesn't surround the string in % signs as
287 LIKE does.
288 "IN" and "NOT IN"
290 VALUE can be an array reference or an object inherited from
291 this class. If it's not then it's treated as any other operator
292 and in most cases SQL would be wrong. Values in array are
293 considered as constants and quoted according to QUOTEVALUE.
294 If object is passed as VALUE then its select statement is used.
296 If no "Column" is selected then "id" is used, if more than one
297 selected then warning is issued and first column is used.
298 ENTRYAGGREGATOR
300 Can be "AND" or "OR" (or anything else valid to aggregate two
301 clauses in SQL). Special value is "none" which means that no entry
302 aggregator should be used. The default value is "OR".
303 CASESENSITIVE
305 on some databases, such as postgres, setting CASESENSITIVE to 1
306 will make this search case sensitive
307 SUBCLAUSE
309 Subclause allows you to assign tags to Limit statements.
310 Statements with matching SUBCLAUSE tags will be grouped together in
311 the final SQL statement.
312 Example:
314 Suppose you want to create Limit statements which would produce
316 results the same as the following SQL:
317 SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
319 You would use the following Limit statements:
321 $folks->Limit( FIELD => 'EmailAddress', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
323 $folks->Limit( FIELD => 'Name', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
324 $folks->Limit( FIELD => 'RealName', OPERATOR => 'LIKE', VALUE => "$query", SUBCLAUSE => 'groupsearch');
325 OrderBy PARAMHASH
327 Orders the returned results by ALIAS.FIELD ORDER.
328 Takes a paramhash of ALIAS, FIELD and ORDER. ALIAS defaults to "main".
330 FIELD has no default value. ORDER defaults to ASC(ending).
331 DESC(ending) is also a valid value for OrderBy.
332 FIELD also accepts FUNCTION(FIELD) format.
334 OrderByCols ARRAY
336 OrderByCols takes an array of paramhashes of the form passed to
337 OrderBy. The result set is ordered by the items in the array.
338 _OrderClause
340 returns the ORDER BY clause for the search.
341 GroupByCols ARRAY_OF_HASHES
343 Each hash contains the keys FIELD, FUNCTION and ALIAS. Hash combined
344 into SQL with "CombineFunctionWithField".
345 _GroupClause
347 Private function to return the "GROUP BY" clause for this query.
348 NewAlias
350 Takes the name of a table and paramhash with TYPE and DISTINCT.
351 Use TYPE equal to "LEFT" to indicate that it's LEFT JOIN. Old style way
353 to call (see below) is also supported, but should be avoided:
354 $records->NewAlias('aTable', 'left');
356 True DISTINCT value indicates that this join keeps result set distinct
358 and DB side distinct is not required. See also "Join".
359 Returns the string of a new Alias for that table, which can be used to
361 Join tables or to Limit what gets found by a search.
362 Join
364 Join instructs DBIx::SearchBuilder to join two tables.
365 The standard form takes a param hash with keys ALIAS1, FIELD1, ALIAS2
367 and FIELD2. ALIAS1 and ALIAS2 are column aliases obtained from
368 $self->NewAlias or a $self->Limit. FIELD1 and FIELD2 are the fields in
369 ALIAS1 and ALIAS2 that should be linked, respectively. For this type
370 of join, this method has no return value.
371 Supplying the parameter TYPE => 'left' causes Join to preform a left
373 join. in this case, it takes ALIAS1, FIELD1, TABLE2 and FIELD2.
374 Because of the way that left joins work, this method needs a TABLE for
375 the second field rather than merely an alias. For this type of join,
376 it will return the alias generated by the join.
377 Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
379 ALIAS2/TABLE2 on an arbitrary expression.
380 It is also possible to join to a pre-existing, already-limited
382 DBIx::SearchBuilder object, by passing it as COLLECTION2, instead of
383 providing an ALIAS2 or TABLE2.
384 By passing true value as DISTINCT argument join can be marked distinct.
386 If all joins are distinct then whole query is distinct and
387 SearchBuilder can avoid "_DistinctQuery" call that can hurt performance
388 of the query. See also "NewAlias".
389 Pages: size and changing
391 Use "RowsPerPage" to set size of pages. "NextPage", "PrevPage",
392 "FirstPage" or "GotoPage" to change pages. "FirstRow" to do tricky
393 stuff.
394 RowsPerPage
396 Get or set the number of rows returned by the database.
398 Takes an optional integer which restricts the # of rows returned in a
400 result. Zero or undef argument flush back to "return all records
401 matching current conditions".
402 Returns the current page size.
404 NextPage
406 Turns one page forward.
408 PrevPage
410 Turns one page backwards.
412 FirstPage
414 Jumps to the first page.
416 GotoPage
418 Takes an integer number and jumps to that page or first page if number
420 omitted. Numbering starts from zero.
421 FirstRow
423 Get or set the first row of the result set the database should return.
425 Takes an optional single integer argrument. Returns the currently set
426 integer minus one (this is historical issue).
427 Usually you don't need this method. Use "RowsPerPage", "NextPage" and
429 other methods to walk pages. It only may be helpful to get 10 records
430 starting from 5th.
431 _ItemsCounter
433 Returns the current position in the record set.
434 Count
436 Returns the number of records in the set. When "RowsPerPage" is set,
437 returns number of records in the page only, otherwise the same as
438 "CountAll".
439 CountAll
441 Returns the total number of potential records in the set, ignoring any
442 "RowsPerPage" settings.
443 IsLast
445 Returns true if the current row is the last record in the set.
446 Column
448 Call to specify which columns should be loaded from the table. Each
449 calls adds one column to the set. Takes a hash with the following
450 named arguments:
451 FIELD
453 Column name to fetch or apply function to.
454 ALIAS
456 Alias of a table the field is in; defaults to "main"
457 FUNCTION
459 A SQL function that should be selected instead of FIELD or applied
460 to it.
461 AS The column alias to use instead of the default. The default column
463 alias is either the column's name (i.e. what is passed to FIELD) if
464 it is in this table (ALIAS is 'main') or an autogenerated alias.
465 Pass "undef" to skip column aliasing entirely.
466 "FIELD", "ALIAS" and "FUNCTION" are combined according to
468 "CombineFunctionWithField".
469 If a FIELD is provided and it is in this table (ALIAS is 'main'), then
471 the column named FIELD and can be accessed as usual by accessors:
472 $articles->Column(FIELD => 'id');
474 $articles->Column(FIELD => 'Subject', FUNCTION => 'SUBSTR(?, 1, 20)');
475 my $article = $articles->First;
476 my $aid = $article->id;
477 my $subject_prefix = $article->Subject;
478 Returns the alias used for the column. If FIELD was not provided, or
480 was from another table, then the returned column alias should be passed
481 to the "_Value" in DBIx::SearchBuilder::Record method to retrieve the
482 column's result:
483 my $time_alias = $articles->Column(FUNCTION => 'NOW()');
485 my $article = $articles->First;
486 my $now = $article->_Value( $time_alias );
487 To choose the column's alias yourself, pass a value for the AS
489 parameter (see above). Be careful not to conflict with existing column
490 aliases.
491 CombineFunctionWithField
493 Takes a hash with three optional arguments: FUNCTION, FIELD and ALIAS.
494 Returns SQL with all three arguments combined according to the
496 following rules.
497 • FUNCTION or undef returned when FIELD is not provided
499 • 'main' ALIAS is used if not provided
501 • ALIAS.FIELD returned when FUNCTION is not provided
503 • NULL returned if FUNCTION is 'NULL'
505 • If FUNCTION contains '?' (question marks) then they are replaced
507 with ALIAS.FIELD and result returned.
508 • If FUNCTION has no '(' (opening parenthesis) then ALIAS.FIELD is
510 appended in parentheses and returned.
511 Examples:
513 $obj->CombineFunctionWithField()
515 => undef
516 $obj->CombineFunctionWithField(FUNCTION => 'FOO')
518 => 'FOO'
519 $obj->CombineFunctionWithField(FIELD => 'foo')
521 => 'main.foo'
522 $obj->CombineFunctionWithField(ALIAS => 'bar', FIELD => 'foo')
524 => 'bar.foo'
525 $obj->CombineFunctionWithField(FUNCTION => 'FOO(?, ?)', FIELD => 'bar')
527 => 'FOO(main.bar, main.bar)'
528 $obj->CombineFunctionWithField(FUNCTION => 'FOO', ALIAS => 'bar', FIELD => 'baz')
530 => 'FOO(bar.baz)'
531 $obj->CombineFunctionWithField(FUNCTION => 'NULL', FIELD => 'bar')
533 => 'NULL'
534 Columns LIST
536 Specify that we want to load only the columns in LIST
537 AdditionalColumn
539 Calls "Column", but first ensures that this table's standard columns
540 are selected as well. Thus, each call to this method results in an
541 additional column selected instead of replacing the default columns.
542 Takes a hash of parameters which is the same as "Column". Returns the
544 result of calling "Column".
545 Fields TABLE
547 Return a list of fields in TABLE. These fields are in the case
548 presented by the database, which may be case-sensitive.
549 HasField { TABLE => undef, FIELD => undef }
551 Returns true if TABLE has field FIELD. Return false otherwise
552 Note: Both TABLE and FIELD are case-sensitive (See: "Fields")
554 Table [TABLE]
556 If called with an argument, sets this collection's table.
557 Always returns this collection's table.
559 QueryHint [Hint]
561 If called with an argument, sets a query hint for this collection. Call
562 this method before performing additional operations on a collection,
563 such as Count(), Next(), etc.
564 Always returns the query hint.
566 When the query hint is included in the SQL query, the "/* ... */" will
568 be included for you. Here's an example query hint for Oracle:
569 $sb->QueryHint("+CURSOR_SHARING_EXACT");
571 QueryHintFormatted
573 Returns the query hint formatted appropriately for inclusion in SQL
574 queries.
575
577 GroupBy
578 DEPRECATED. Alias for the "GroupByCols" method.
579 SetTable
581 DEPRECATED. Alias for the "Table" method.
582 ShowRestrictions
584 DEPRECATED AND DOES NOTHING.
585 ImportRestrictions
587 DEPRECATED AND DOES NOTHING.
588
590 In order to test most of the features of "DBIx::SearchBuilder", you
591 need to provide "make test" with a test database. For each DBI driver
592 that you would like to test, set the environment variables
593 "SB_TEST_FOO", "SB_TEST_FOO_USER", and "SB_TEST_FOO_PASS" to a database
594 name, database username, and database password, where "FOO" is the
595 driver name in all uppercase. You can test as many drivers as you
596 like. (The appropriate "DBD::" module needs to be installed in order
597 for the test to work.) Note that the "SQLite" driver will
598 automatically be tested if "DBD::Sqlite" is installed, using a
599 temporary file as the database. For example:
600 SB_TEST_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \
602 SB_TEST_PG=test SB_TEST_PG_USER=postgres make test
603
605 Best Practical Solutions, LLC <modules@bestpractical.com>
606
608 Ansgar Burchardt <ANSGAR@cpan.org>
609 Audrey Tang <audreyt@audreyt.org>
610 Ivan Kohler <ivan-rt@420.am>
611 Martin King <Martin.King@arm.com>
612 Mathieu Arnold <mat@mat.cc>
613 Matt Knopp <mhat@netlag.com>
614 Matthew Simon Cavalletto <simonm@cavalletto.org>
615 Nick Morrott <knowledgejunkie@gmail.com>
616 Oliver Tappe <oliver@akso.de>
617 Simon Cozens <simon@cpan.org>
618
620 All bugs should be reported via email to
621 L<bug-DBIx-SearchBuilder@rt.cpan.org|mailto:bug-DBIx-SearchBuilder@rt.cpan.org>
623 or via the web at
625 L<rt.cpan.org|http://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-SearchBuilder>.
627
629 Copyright (C) 2001-2023, Best Practical Solutions LLC.
630 This library is free software; you can redistribute it and/or modify it
632 under the same terms as Perl itself.
633
635 DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.
636perl v5.36.3 2023-12-04 DBIx::SearchBuilder(3)