1DBIx::SearchBuilder(3)User Contributed Perl DocumentationDBIx::SearchBuilder(3)
2
3
4

NAME

6       DBIx::SearchBuilder - Encapsulate SQL queries and rows in simple perl
7       objects
8

SYNOPSIS

10         use DBIx::SearchBuilder;
11
12         package My::Things;
13         use base qw/DBIx::SearchBuilder/;
14
15         sub _Init {
16             my $self = shift;
17             $self->Table('Things');
18             return $self->SUPER::_Init(@_);
19         }
20
21         sub NewItem {
22             my $self = shift;
23             # MyThing is a subclass of DBIx::SearchBuilder::Record
24             return(MyThing->new);
25         }
26
27         package main;
28
29         use DBIx::SearchBuilder::Handle;
30         my $handle = DBIx::SearchBuilder::Handle->new();
31         $handle->Connect( Driver => 'SQLite', Database => "my_test_db" );
32
33         my $sb = My::Things->new( Handle => $handle );
34
35         $sb->Limit( FIELD => "column_1", VALUE => "matchstring" );
36
37         while ( my $record = $sb->Next ) {
38             print $record->my_column_name();
39         }
40

DESCRIPTION

42       This module provides an object-oriented mechanism for retrieving and
43       updating data in a DBI-accesible database.
44
45       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
50       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

METHOD NAMING

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

METHODS

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
68          my $sb = My::DBIx::SearchBuilder::Subclass->new( Handle => $handle );
69
70       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
76   _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
82   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
88   Clone
89       Returns copy of the current object with all search restrictions.
90
91   _ClonedAttributes
92       Returns list of the object's fields that should be copied.
93
94       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
98   _Handle  [DBH]
99       Get or set this object's DBIx::SearchBuilder::Handle object.
100
101   _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
106   AddRecord RECORD
107       Adds a record object to this collection.
108
109   _RecordCount
110       This private internal method returns the number of Record objects saved
111       as a result of the last query.
112
113   _DoCount
114       This internal private method actually executes a counting operation on
115       the database; it is used by "Count" and "CountAll".
116
117   _DoSearchAndCount
118       This internal private method actually executes the search and also
119       counting on the database;
120
121   _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
130   _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
134   _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
139   _BuildJoins
140       Build up all of the joins we need to perform this query.
141
142   _isJoined
143       Returns true if this SearchBuilder will be joining multiple tables
144       together.
145
146   _isLimited
147       If we've limited down this search, return true. Otherwise, return
148       false.
149
150   BuildSelectQuery PreferBind => 1|0
151       Builds a query string for a "SELECT rows from Tables" statement for
152       this SearchBuilder object
153
154       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
159       To override global $DBIx::SearchBuilder::PREFER_BIND for current object
160       only, you can also set "_prefer_bind" accordingly, e.g.
161
162           $sb->{_prefer_bind} = 1;
163
164   BuildSelectCountQuery PreferBind => 1|0
165       Builds a SELECT statement to find the number of rows this SearchBuilder
166       object would find.
167
168   BuildSelectAndCountQuery PreferBind => 1|0
169       Builds a query string that is a combination of BuildSelectQuery and
170       BuildSelectCountQuery.
171
172   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
178   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
183   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
188       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
194   First
195       Returns the first item
196
197   Last
198       Returns the last item
199
200   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
205       Takes paramhash with the following keys:
206
207       Field
208           Field name. Can be first argument without key.
209
210       Order
211           'ASC', 'DESC' or undef. Defines whether results should be sorted or
212           not. By default results are not sorted.
213
214       Max Maximum number of elements to fetch.
215
216   ItemsArrayRef
217       Return a reference to an array containing all objects found by this
218       search.
219
220   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
224   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
228   CombineSearchAndCount 1|0
229       Tells DBIx::SearchBuilder if it shall search both records and the total
230       count in a single query.
231
232   UnLimit
233       UnLimit clears all restrictions and causes this object to return all
234       rows in the primary table.
235
236   Limit
237       Limit takes a hash of parameters with the following keys:
238
239       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
243       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
248       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
255       FIELD
256           Column to be checked against.
257
258       FUNCTION
259           Function that should be checked against or applied to the FIELD
260           before check. See "CombineFunctionWithField" for rules.
261
262       VALUE
263           Should always be set and will always be quoted.
264
265       OPERATOR
266           OPERATOR is the SQL operator to use for this phrase.  Possible
267           choices include:
268
269           "="
270           "!="
271           "LIKE"
272               In the case of LIKE, the string is surrounded in % signs.  Yes.
273               this is a bug.
274
275           "NOT LIKE"
276           "STARTSWITH"
277               STARTSWITH is like LIKE, except it only appends a % at the end
278               of the string
279
280           "ENDSWITH"
281               ENDSWITH is like LIKE, except it prepends a % to the beginning
282               of the string
283
284           "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
289           "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
295               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
299       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
304       CASESENSITIVE
305           on some databases, such as postgres, setting CASESENSITIVE to 1
306           will make this search case sensitive
307
308       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
313           Example:
314
315           Suppose you want to create Limit statements which would produce
316           results the same as the following SQL:
317
318              SELECT * FROM Users WHERE EmailAddress OR Name OR RealName OR Email LIKE $query;
319
320           You would use the following Limit statements:
321
322               $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
326   OrderBy PARAMHASH
327       Orders the returned results by ALIAS.FIELD ORDER.
328
329       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
333       FIELD also accepts FUNCTION(FIELD) format.
334
335   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
339   _OrderClause
340       returns the ORDER BY clause for the search.
341
342   GroupByCols ARRAY_OF_HASHES
343       Each hash contains the keys FIELD, FUNCTION and ALIAS. Hash combined
344       into SQL with "CombineFunctionWithField".
345
346   _GroupClause
347       Private function to return the "GROUP BY" clause for this query.
348
349   NewAlias
350       Takes the name of a table and paramhash with TYPE and DISTINCT.
351
352       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
355           $records->NewAlias('aTable', 'left');
356
357       True DISTINCT value indicates that this join keeps result set distinct
358       and DB side distinct is not required. See also "Join".
359
360       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
363   Join
364       Join instructs DBIx::SearchBuilder to join two tables.
365
366       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
372       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
378       Instead of ALIAS1/FIELD1, it's possible to specify EXPRESSION, to join
379       ALIAS2/TABLE2 on an arbitrary expression.
380
381       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
385       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
390   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
395       RowsPerPage
396
397       Get or set the number of rows returned by the database.
398
399       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
403       Returns the current page size.
404
405       NextPage
406
407       Turns one page forward.
408
409       PrevPage
410
411       Turns one page backwards.
412
413       FirstPage
414
415       Jumps to the first page.
416
417       GotoPage
418
419       Takes an integer number and jumps to that page or first page if number
420       omitted. Numbering starts from zero.
421
422       FirstRow
423
424       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
428       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
432   _ItemsCounter
433       Returns the current position in the record set.
434
435   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
440   CountAll
441       Returns the total number of potential records in the set, ignoring any
442       "RowsPerPage" settings.
443
444   IsLast
445       Returns true if the current row is the last record in the set.
446
447   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
452       FIELD
453           Column name to fetch or apply function to.
454
455       ALIAS
456           Alias of a table the field is in; defaults to "main"
457
458       FUNCTION
459           A SQL function that should be selected instead of FIELD or applied
460           to it.
461
462       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
467       "FIELD", "ALIAS" and "FUNCTION" are combined according to
468       "CombineFunctionWithField".
469
470       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
473           $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
479       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
484           my $time_alias = $articles->Column(FUNCTION => 'NOW()');
485           my $article = $articles->First;
486           my $now = $article->_Value( $time_alias );
487
488       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
492   CombineFunctionWithField
493       Takes a hash with three optional arguments: FUNCTION, FIELD and ALIAS.
494
495       Returns SQL with all three arguments combined according to the
496       following rules.
497
498       •   FUNCTION or undef returned when FIELD is not provided
499
500       •   'main' ALIAS is used if not provided
501
502       •   ALIAS.FIELD returned when FUNCTION is not provided
503
504       •   NULL returned if FUNCTION is 'NULL'
505
506       •   If FUNCTION contains '?' (question marks) then they are replaced
507           with ALIAS.FIELD and result returned.
508
509       •   If FUNCTION has no '(' (opening parenthesis) then ALIAS.FIELD is
510           appended in parentheses and returned.
511
512       Examples:
513
514           $obj->CombineFunctionWithField()
515            => undef
516
517           $obj->CombineFunctionWithField(FUNCTION => 'FOO')
518            => 'FOO'
519
520           $obj->CombineFunctionWithField(FIELD => 'foo')
521            => 'main.foo'
522
523           $obj->CombineFunctionWithField(ALIAS => 'bar', FIELD => 'foo')
524            => 'bar.foo'
525
526           $obj->CombineFunctionWithField(FUNCTION => 'FOO(?, ?)', FIELD => 'bar')
527            => 'FOO(main.bar, main.bar)'
528
529           $obj->CombineFunctionWithField(FUNCTION => 'FOO', ALIAS => 'bar', FIELD => 'baz')
530            => 'FOO(bar.baz)'
531
532           $obj->CombineFunctionWithField(FUNCTION => 'NULL', FIELD => 'bar')
533            => 'NULL'
534
535   Columns LIST
536       Specify that we want to load only the columns in LIST
537
538   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
543       Takes a hash of parameters which is the same as "Column".  Returns the
544       result of calling "Column".
545
546   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
550   HasField  { TABLE => undef, FIELD => undef }
551       Returns true if TABLE has field FIELD.  Return false otherwise
552
553       Note: Both TABLE and FIELD are case-sensitive (See: "Fields")
554
555   Table [TABLE]
556       If called with an argument, sets this collection's table.
557
558       Always returns this collection's table.
559
560   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
565       Always returns the query hint.
566
567       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
570           $sb->QueryHint("+CURSOR_SHARING_EXACT");
571
572   QueryHintFormatted
573       Returns the query hint formatted appropriately for inclusion in SQL
574       queries.
575

DEPRECATED METHODS

577   GroupBy
578       DEPRECATED. Alias for the "GroupByCols" method.
579
580   SetTable
581       DEPRECATED. Alias for the "Table" method.
582
583   ShowRestrictions
584       DEPRECATED AND DOES NOTHING.
585
586   ImportRestrictions
587       DEPRECATED AND DOES NOTHING.
588

TESTING

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

AUTHOR

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

CONTRIBUTORS

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

BUGS

620       All bugs should be reported via email to
621
622           L<bug-DBIx-SearchBuilder@rt.cpan.org|mailto:bug-DBIx-SearchBuilder@rt.cpan.org>
623
624       or via the web at
625
626           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
631       This library is free software; you can redistribute it and/or modify it
632       under the same terms as Perl itself.
633

SEE ALSO

635       DBIx::SearchBuilder::Handle, DBIx::SearchBuilder::Record.
636
637
638
639perl v5.36.3                      2023-12-04            DBIx::SearchBuilder(3)
Impressum