|Field||Field name. Can be first argument without 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.|
Return a refernece to an array containing all objects found by this search.
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 its 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:
TABLE Can be set to something different than this table if a join is wanted (that means we cant 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 be 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 allows you to 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 check. See CombineFunctionWithField for 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 STARTSWITH STARTSWITH is like LIKE, except it only appends a % at the end of the string ENDSWITH ENDSWITH is like LIKE, except it prepends a % to the beginning of the string MATCHES MATCHES is equivalent to the databases LIKE that is, its actually LIKE, but doesnt 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 its not then its 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.
ENTRYAGGREGATOR 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. CASESENSITIVE on some databases, such as postgres, setting CASESENSITIVE to 1 will make this search case sensitive SUBCLAUSE 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 statments 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 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.
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, its 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.
Use RowsPerPage to set size of pages. NextPage, PrevPage, FirstPage or GotoPage to change pages. FirstRow to do tricky stuff.
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.
Usually you dont 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.
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:
FIELD, ALIAS and FUNCTION are combined according to CombineFunctionWithField.
FIELD Column name to fetch or apply 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 <B>columnB> alias to use instead of the default. The default column alias is either the columns 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.
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 columns result:
my $time_alias = $articles->Column(FUNCTION => NOW()); my $article = $articles->First; my $now = $article->_Value( $time_alias );
To choose the columns alias yourself, pass a value for the AS parameter (see above). Be careful not to conflict with existing column aliases.
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.
$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
Specify that we want to load only the columns in LIST
Calls Column, but first ensures that this tables 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.
Return a list of fields in TABLE. These fields are in the case presented by the database, which may be case-sensitive.
Returns true if TABLE has field FIELD. Return false otherwise
Note: Both TABLE and FIELD are case-sensitive (See: Fields)
If called with an argument, sets this collections table.
Always returns this collections table.
DEPRECATED. Alias for the GroupByCols method.
DEPRECATED. Alias for the Table method.
DEPRECATED AND DOES NOTHING.
DEPRECATED AND DOES NOTHING.
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_MYSQL=test SB_TEST_MYSQL_USER=root SB_TEST_MYSQL_PASS=foo \ SB_TEST_PG=test SB_TEST_PG_USER=postgres make test
Best Practical Solutions, LLC <email@example.com>
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.
|perl v5.20.3||DBIX::SEARCHBUILDER (3)||2014-08-19|