class DataQuery (View source)

An object representing a query of data from the DataObject's supporting database.

Acts as a wrapper over {@link SQLSelect} and performs all of the query generation. Used extensively by {@link DataList}.

Unlike DataList, modifiers on DataQuery modify the object rather than returning a clone. DataList is immutable, DataQuery is mutable.

Traits

Allows an object to have extensions applied to it.
Allows an object to declare a set of custom methods

Properties

static private array $extensions An array of extension names and parameters to be applied to this object upon construction. from  Extensible

Methods

mixed
__call(string $method, array $arguments)

Attempts to locate and call a method dynamically added to a class at runtime if a default cannot be located

bool
hasMethod(string $method)

Return TRUE if a method exists on this object

array
allMethodNames(bool $custom = false)

Return the names of all the methods available on this object

static bool
add_extension(string $classOrExtension, string $extension = null)

Add an extension to a specific class.

static 
remove_extension(string $extension)

Remove an extension from a class.

static array
get_extensions(string $class = null, bool $includeArgumentString = false)

No description

static array|null
get_extra_config_sources(string $class = null)

Get extra config sources for this class

static bool
has_extension(string $classOrExtension, string $requiredExtension = null, bool $strict = false)

Return TRUE if a class has a specified extension.

array
invokeWithExtensions(string $method, mixed $a1 = null, mixed $a2 = null, mixed $a3 = null, mixed $a4 = null, mixed $a5 = null, mixed $a6 = null, mixed $a7 = null)

Calls a method if available on both this object and all applied {@link Extensions}, and then attempts to merge all results into an array

array
extend(string $method, mixed $a1 = null, mixed $a2 = null, mixed $a3 = null, mixed $a4 = null, mixed $a5 = null, mixed $a6 = null, mixed $a7 = null)

Run the given function on all of this object's extensions. Note that this method originally returned void, so if you wanted to return results, you're hosed

Extension|null
getExtensionInstance(string $extension)

Get an extension instance attached to this object by name.

bool
hasExtension(string $extension)

Returns TRUE if this object instance has a specific extension applied in {@link $extension_instances}. Extension instances are initialized at constructor time, meaning if you use {@link add_extension()} afterwards, the added extension will just be added to new instances of the extended class. Use the static method {@link has_extension()} to check if a class (not an instance) has a specific extension.

Extension[]
getExtensionInstances()

Get all extension instances for this specific object instance.

__construct(string $dataClass)

Create a new DataQuery.

__clone()

Clone this object

string
dataClass()

Return the {@link DataObject} class that is being queried.

SQLSelect
query()

Return the {@link SQLSelect} object that represents the current query; note that it will be a clone of the object.

$this
removeFilterOn(string|array $fieldExpression)

Remove a filter from the query

$this
setQueriedColumns(array $queriedColumns)

No description

SQLSelect
getFinalisedQuery(array|null $queriedColumns = null)

Ensure that the query is ready to execute.

Query
execute()

Execute the query and return the result as {@link SS_Query} object.

string
sql(array $parameters = [])

Return this query's SQL

int
count()

Return the number of records in this query.

bool
exists()

Return whether this dataquery will have records. This will use EXISTS statements in SQL which are more performant - especially when used in combination with indexed columns (that you're filtering on)

string
max(string $field)

Return the maximum value of the given field in this DataList

string
min(string $field)

Return the minimum value of the given field in this DataList

string
avg(string $field)

Return the average value of the given field in this DataList

string
sum(string $field)

Return the sum of the values of the given field in this DataList

string
aggregate(string $expression)

Runs a raw aggregate expression. Please handle escaping yourself

SQLSelect
firstRow()

Return the first row that would be returned by this full DataQuery Note that this will issue a separate SELECT .

SQLSelect
lastRow()

Return the last row that would be returned by this full DataQuery Note that this will issue a separate SELECT .

$this
groupby(string $groupby)

Append a GROUP BY clause to this query.

$this
having(mixed $having)

Append a HAVING clause to this query.

DataQuery_SubGroup
disjunctiveGroup()

Create a disjunctive subgroup.

DataQuery_SubGroup
conjunctiveGroup()

Create a conjunctive subgroup

$this
where(string|array|SQLConditionGroup $filter)

Adds a WHERE clause.

$this
whereAny(string|array|SQLConditionGroup $filter)

Append a WHERE with OR.

$this
sort(string $sort = null, string $direction = null, bool $clear = true)

Set the ORDER BY clause of this query

$this
reverseSort()

Reverse order by clause

$this
limit(int $limit, int $offset = 0)

Set the limit of this query.

$this
distinct(bool $value)

Set whether this query should be distinct or not.

$this
innerJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = [])

Add an INNER JOIN clause to this query.

$this
leftJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = [])

Add a LEFT JOIN clause to this query.

static string
applyRelationPrefix(string|array $relation)

Prefix of all joined table aliases. E.g. ->filter('Banner.Image.Title)' Will join the Banner, and then Image relations $relationPrefx will be banner_image_ Each table in the Image chain will be suffixed to this prefix. E.g.

string
applyRelation(string|array $relation, bool $linearOnly = false)

Traverse the relationship fields, and add the table mappings to the query object state. This has to be called in any overloaded {@link SearchFilter->apply()} methods manually.

$this
subtract(DataQuery $subtractQuery, string $field = 'ID')

Removes the result of query from this query.

$this
selectFromTable(string $table, array $fields)

Select the only given fields from the given table.

$this
addSelectFromTable(string $table, array $fields)

Add the given fields from the given table to the select statement.

array
column(string $field = 'ID')

Query the given field column from the database and return as an array.

selectField(string $fieldExpression, string $alias = null)

Select the given field expressions.

$this
setQueryParam(string $key, string|array $value)

Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query.

string
getQueryParam(string $key)

Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query.

array
getQueryParams()

Returns all query parameters

DataQueryManipulator[]
getDataQueryManipulators()

Get query manipulators

$this
pushQueryManipulator(DataQueryManipulator $manipulator)

Assign callback to be invoked in getFinalisedQuery()

Details

mixed __call(string $method, array $arguments)

Attempts to locate and call a method dynamically added to a class at runtime if a default cannot be located

You can add extra methods to a class using {@link Extensions}, {@link Object::createMethod()} or {@link Object::addWrapperMethod()}

Parameters

string $method
array $arguments

Return Value

mixed

Exceptions

BadMethodCallException

bool hasMethod(string $method)

Return TRUE if a method exists on this object

This should be used rather than PHP's inbuild method_exists() as it takes into account methods added via extensions

Parameters

string $method

Return Value

bool

array allMethodNames(bool $custom = false)

Return the names of all the methods available on this object

Parameters

bool $custom include methods added dynamically at runtime

Return Value

array

static bool add_extension(string $classOrExtension, string $extension = null)

Add an extension to a specific class.

The preferred method for adding extensions is through YAML config, since it avoids autoloading the class, and is easier to override in more specific configurations.

As an alternative, extensions can be added to a specific class directly in the {@link Object::$extensions} array. See {@link SiteTree::$extensions} for examples. Keep in mind that the extension will only be applied to new instances, not existing ones (including all instances created through {@link singleton()}).

Parameters

string $classOrExtension Class that should be extended - has to be a subclass of {@link Object}
string $extension Subclass of {@link Extension} with optional parameters as a string, e.g. "Versioned" or "Translatable('Param')"

Return Value

bool

See also

http://doc.silverstripe.org/framework/en/trunk/reference/dataextension

static remove_extension(string $extension)

Remove an extension from a class.

Note: This will not remove extensions from parent classes, and must be called directly on the class assigned the extension.

Keep in mind that this won't revert any datamodel additions of the extension at runtime, unless its used before the schema building kicks in (in your _config.php). Doesn't remove the extension from any {@link Object} instances which are already created, but will have an effect on new extensions. Clears any previously created singletons through {@link singleton()} to avoid side-effects from stale extension information.

Parameters

string $extension class name of an {@link Extension} subclass, without parameters

static array get_extensions(string $class = null, bool $includeArgumentString = false)

Parameters

string $class If omitted, will get extensions for the current class
bool $includeArgumentString Include the argument string in the return array, FALSE would return array("Versioned"), TRUE returns array("Versioned('Stage','Live')").

Return Value

array

static array|null get_extra_config_sources(string $class = null)

Get extra config sources for this class

Parameters

string $class Name of class. If left null will return for the current class

Return Value

array|null

static bool has_extension(string $classOrExtension, string $requiredExtension = null, bool $strict = false)

Return TRUE if a class has a specified extension.

This supports backwards-compatible format (static Object::has_extension($requiredExtension)) and new format ($object->has_extension($class, $requiredExtension))

Parameters

string $classOrExtension Class to check extension for, or the extension name to check if the second argument is null.
string $requiredExtension If the first argument is the parent class, this is the extension to check. If left null, the first parameter will be treated as the extension.
bool $strict if the extension has to match the required extension and not be a subclass

Return Value

bool

array invokeWithExtensions(string $method, mixed $a1 = null, mixed $a2 = null, mixed $a3 = null, mixed $a4 = null, mixed $a5 = null, mixed $a6 = null, mixed $a7 = null)

Calls a method if available on both this object and all applied {@link Extensions}, and then attempts to merge all results into an array

Parameters

string $method the method name to call
mixed $a1
mixed $a2
mixed $a3
mixed $a4
mixed $a5
mixed $a6
mixed $a7

Return Value

array

array extend(string $method, mixed $a1 = null, mixed $a2 = null, mixed $a3 = null, mixed $a4 = null, mixed $a5 = null, mixed $a6 = null, mixed $a7 = null)

Run the given function on all of this object's extensions. Note that this method originally returned void, so if you wanted to return results, you're hosed

Currently returns an array, with an index resulting every time the function is called. Only adds returns if they're not NULL, to avoid bogus results from methods just defined on the parent extension. This is important for permission-checks through extend, as they use min() to determine if any of the returns is FALSE. As min() doesn't do type checking, an included NULL return would fail the permission checks.

The extension methods are defined during {@link __construct()} in {@link defineMethods()}.

Parameters

string $method the name of the method to call on each extension
mixed $a1
mixed $a2
mixed $a3
mixed $a4
mixed $a5
mixed $a6
mixed $a7

Return Value

array

Extension|null getExtensionInstance(string $extension)

Get an extension instance attached to this object by name.

Parameters

string $extension

Return Value

Extension|null

bool hasExtension(string $extension)

Returns TRUE if this object instance has a specific extension applied in {@link $extension_instances}. Extension instances are initialized at constructor time, meaning if you use {@link add_extension()} afterwards, the added extension will just be added to new instances of the extended class. Use the static method {@link has_extension()} to check if a class (not an instance) has a specific extension.

Caution: Don't use singleton()->hasExtension() as it will give you inconsistent results based on when the singleton was first accessed.

Parameters

string $extension Classname of an {@link Extension} subclass without parameters

Return Value

bool

Extension[] getExtensionInstances()

Get all extension instances for this specific object instance.

See {@link get_extensions()} to get all applied extension classes for this class (not the instance).

This method also provides lazy-population of the extension_instances property.

Return Value

Extension[]

__construct(string $dataClass)

Create a new DataQuery.

Parameters

string $dataClass The name of the DataObject class that you wish to query

__clone()

Clone this object

string dataClass()

Return the {@link DataObject} class that is being queried.

Return Value

string

SQLSelect query()

Return the {@link SQLSelect} object that represents the current query; note that it will be a clone of the object.

Return Value

SQLSelect

$this removeFilterOn(string|array $fieldExpression)

Remove a filter from the query

Parameters

string|array $fieldExpression The predicate of the condition to remove (ignoring parameters). The expression will be considered a match if it's contained within any other predicate.

Return Value

$this

$this setQueriedColumns(array $queriedColumns)

Parameters

array $queriedColumns

Return Value

$this

SQLSelect getFinalisedQuery(array|null $queriedColumns = null)

Ensure that the query is ready to execute.

Parameters

array|null $queriedColumns Any columns to filter the query by

Return Value

SQLSelect

Query execute()

Execute the query and return the result as {@link SS_Query} object.

Return Value

Query

string sql(array $parameters = [])

Return this query's SQL

Parameters

array $parameters Out variable for parameters required for this query

Return Value

string

int count()

Return the number of records in this query.

Note that this will issue a separate SELECT COUNT() query.

Return Value

int

bool exists()

Return whether this dataquery will have records. This will use EXISTS statements in SQL which are more performant - especially when used in combination with indexed columns (that you're filtering on)

Return Value

bool

string max(string $field)

Return the maximum value of the given field in this DataList

Parameters

string $field Unquoted database column name. Will be ANSI quoted automatically so must not contain double quotes.

Return Value

string

string min(string $field)

Return the minimum value of the given field in this DataList

Parameters

string $field Unquoted database column name. Will be ANSI quoted automatically so must not contain double quotes.

Return Value

string

string avg(string $field)

Return the average value of the given field in this DataList

Parameters

string $field Unquoted database column name. Will be ANSI quoted automatically so must not contain double quotes.

Return Value

string

string sum(string $field)

Return the sum of the values of the given field in this DataList

Parameters

string $field Unquoted database column name. Will be ANSI quoted automatically so must not contain double quotes.

Return Value

string

string aggregate(string $expression)

Runs a raw aggregate expression. Please handle escaping yourself

Parameters

string $expression An aggregate expression, such as 'MAX("Balance")', or a set of them (as an escaped SQL statement)

Return Value

string

SQLSelect firstRow()

Return the first row that would be returned by this full DataQuery Note that this will issue a separate SELECT .

.. LIMIT 1 query.

Return Value

SQLSelect

SQLSelect lastRow()

Return the last row that would be returned by this full DataQuery Note that this will issue a separate SELECT .

.. LIMIT query.

Return Value

SQLSelect

$this groupby(string $groupby)

Append a GROUP BY clause to this query.

Parameters

string $groupby Escaped SQL statement

Return Value

$this

$this having(mixed $having)

Append a HAVING clause to this query.

Parameters

mixed $having Predicate(s) to set, as escaped SQL statements or parameterised queries

Return Value

$this

DataQuery_SubGroup disjunctiveGroup()

Create a disjunctive subgroup.

That is a subgroup joined by OR

Return Value

DataQuery_SubGroup

DataQuery_SubGroup conjunctiveGroup()

Create a conjunctive subgroup

That is a subgroup joined by AND

Return Value

DataQuery_SubGroup

$this where(string|array|SQLConditionGroup $filter)

Adds a WHERE clause.

Parameters

string|array|SQLConditionGroup $filter Predicate(s) to set, as escaped SQL statements or paramaterised queries

Return Value

$this

See also

\SQLSelect::addWhere() for syntax examples, although DataQuery won't expand multiple arguments as SQLSelect does.

$this whereAny(string|array|SQLConditionGroup $filter)

Append a WHERE with OR.

Parameters

string|array|SQLConditionGroup $filter Predicate(s) to set, as escaped SQL statements or paramaterised queries

Return Value

$this

See also

\SQLSelect::addWhere() for syntax examples, although DataQuery won't expand multiple method arguments as SQLSelect does.

$this sort(string $sort = null, string $direction = null, bool $clear = true)

Set the ORDER BY clause of this query

Parameters

string $sort Column to sort on (escaped SQL statement)
string $direction Direction ("ASC" or "DESC", escaped SQL statement)
bool $clear Clear existing values

Return Value

$this

See also

\SQLSelect::orderby()

$this reverseSort()

Reverse order by clause

Return Value

$this

$this limit(int $limit, int $offset = 0)

Set the limit of this query.

Parameters

int $limit
int $offset

Return Value

$this

$this distinct(bool $value)

Set whether this query should be distinct or not.

Parameters

bool $value

Return Value

$this

$this innerJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = [])

Add an INNER JOIN clause to this query.

Parameters

string $table The unquoted table name to join to.
string $onClause The filter for the join (escaped SQL statement)
string $alias An optional alias name (unquoted)
int $order A numerical index to control the order that joins are added to the query; lower order values will cause the query to appear first. The default is 20, and joins created automatically by the ORM have a value of 10.
array $parameters Any additional parameters if the join is a parameterised subquery

Return Value

$this

$this leftJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = [])

Add a LEFT JOIN clause to this query.

Parameters

string $table The unquoted table to join to.
string $onClause The filter for the join (escaped SQL statement).
string $alias An optional alias name (unquoted)
int $order A numerical index to control the order that joins are added to the query; lower order values will cause the query to appear first. The default is 20, and joins created automatically by the ORM have a value of 10.
array $parameters Any additional parameters if the join is a parameterised subquery

Return Value

$this

static string applyRelationPrefix(string|array $relation)

Prefix of all joined table aliases. E.g. ->filter('Banner.Image.Title)' Will join the Banner, and then Image relations $relationPrefx will be banner_image_ Each table in the Image chain will be suffixed to this prefix. E.g.

banner_image_File and banner_image_Image

This will be null if no relation is joined. E.g. ->filter('Title')

Parameters

string|array $relation Relation in '.' delimited string, or array of parts

Return Value

string

string applyRelation(string|array $relation, bool $linearOnly = false)

Traverse the relationship fields, and add the table mappings to the query object state. This has to be called in any overloaded {@link SearchFilter->apply()} methods manually.

Note, that in order to filter against the joined relation user code must use {see \tablePrefix()} to get the table alias used for this relation.

Parameters

string|array $relation The array/dot-syntax relation to follow
bool $linearOnly Set to true to restrict to linear relations only. Set this if this relation will be used for sorting, and should not include duplicate rows.

Return Value

string

$this subtract(DataQuery $subtractQuery, string $field = 'ID')

Removes the result of query from this query.

Parameters

DataQuery $subtractQuery
string $field

Return Value

$this

$this selectFromTable(string $table, array $fields)

Select the only given fields from the given table.

Parameters

string $table Unquoted table name (will be escaped automatically)
array $fields Database column names (will be escaped automatically)

Return Value

$this

$this addSelectFromTable(string $table, array $fields)

Add the given fields from the given table to the select statement.

Parameters

string $table Unquoted table name (will be escaped automatically)
array $fields Database column names (will be escaped automatically)

Return Value

$this

array column(string $field = 'ID')

Query the given field column from the database and return as an array.

querying DB columns of related tables is supported but you need to make sure that the related table is already available in join

Parameters

string $field See {@link expressionForField()}.

Return Value

array

Exceptions

InvalidArgumentException

See also

\DataList::applyRelation() example use: column("MyTable"."Title") or $columnName = null; Category::get() ->applyRelation('Products.Title', $columnName) ->column($columnName);

selectField(string $fieldExpression, string $alias = null)

Select the given field expressions.

Parameters

string $fieldExpression String The field to select (escaped SQL statement)
string $alias String The alias of that field (escaped SQL statement)

$this setQueryParam(string $key, string|array $value)

Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query.

It's expected that the $key will be namespaced, e.g, 'Versioned.stage' instead of just 'stage'.

Parameters

string $key
string|array $value

Return Value

$this

string getQueryParam(string $key)

Set an arbitrary query parameter, that can be used by decorators to add additional meta-data to the query.

Parameters

string $key

Return Value

string

array getQueryParams()

Returns all query parameters

Return Value

array

DataQueryManipulator[] getDataQueryManipulators()

Get query manipulators

Return Value

DataQueryManipulator[]

$this pushQueryManipulator(DataQueryManipulator $manipulator)

Assign callback to be invoked in getFinalisedQuery()

Parameters

DataQueryManipulator $manipulator

Return Value

$this