DataQuery

class DataQuery

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

Acts as a wrapper over {@link SQLQuery} 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.

Methods

__construct(string $dataClass)

Create a new DataQuery.

__clone()

Clone this object

string
dataClass()

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

SQLQuery
query()

Return the {@link SQLQuery} 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

initialiseQuery()

Set up the simplest initial query

$this
setQueriedColumns(array $queriedColumns)

No description

SQLQuery
getFinalisedQuery(array|null $queriedColumns = null)

Ensure that the query is ready to execute.

SS_Query
execute()

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

string
sql(array $parameters = array())

Return this query's SQL

int
count()

Return the number of records in this query.

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(string $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, boolean $clear = true)

Set the ORDER BY clause of this query

$this
reverseSort()

Reverse order by clause

limit(int $limit, int $offset)

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 = array())

Add an INNER JOIN clause to this query.

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

Add a LEFT JOIN clause to this query.

string
applyRelation(string|array $relation)

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 only the 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.

$this
setQueryParam(string $key, mixed $value)

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

mixed
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

Details

at line 48
__construct(string $dataClass)

Create a new DataQuery.

Parameters

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

at line 56
__clone()

Clone this object

at line 65
string dataClass()

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

Return Value

string

at line 75
SQLQuery query()

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

Return Value

SQLQuery

at line 88
$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

at line 133
initialiseQuery()

Set up the simplest initial query

at line 170
$this setQueriedColumns(array $queriedColumns)

Parameters

array $queriedColumns

Return Value

$this

at line 181
SQLQuery 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

SQLQuery The finalised sql query

at line 377
SS_Query execute()

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

Return Value

SS_Query

at line 387
string sql(array $parameters = array())

Return this query's SQL

Parameters

array $parameters Out variable for parameters required for this query

Return Value

string The resulting SQL query (may be paramaterised)

at line 397
int count()

Return the number of records in this query.

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

Return Value

int

at line 409
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

at line 424
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

at line 439
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

at line 454
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

at line 468
string aggregate(string $expression)

Runs a raw aggregate expression. Please handle escaping yourself

Parameters

string $expression

Return Value

string

at line 478
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

at line 488
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

at line 530
$this groupby(string $groupby)

Append a GROUP BY clause to this query.

Parameters

string $groupby Escaped SQL statement

Return Value

$this

at line 541
$this having(string $having)

Append a HAVING clause to this query.

Parameters

string $having Escaped SQL statement

Return Value

$this

at line 553
DataQuery_SubGroup disjunctiveGroup()

Create a disjunctive subgroup.

That is a subgroup joined by OR

Return Value

DataQuery_SubGroup

at line 564
DataQuery_SubGroup conjunctiveGroup()

Create a conjunctive subgroup

That is a subgroup joined by AND

Return Value

DataQuery_SubGroup

at line 578
$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

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

at line 595
$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

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

at line 612
$this sort(string $sort = null, string $direction = null, boolean $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)
boolean $clear Clear existing values

Return Value

$this

See also

SQLQuery::orderby()

at line 627
$this reverseSort()

Reverse order by clause

Return Value

$this

at line 638
limit(int $limit, int $offset)

Set the limit of this query.

Parameters

int $limit
int $offset

at line 649
$this distinct(bool $value)

Set whether this query should be distinct or not.

Parameters

bool $value

Return Value

$this

at line 666
$this innerJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = array())

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

at line 685
$this leftJoin(string $table, string $onClause, string $alias = null, int $order = 20, array $parameters = array())

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

at line 700
string applyRelation(string|array $relation)

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.

Parameters

string|array $relation The array/dot-syntax relation to follow

Return Value

string The model class of the related item

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

Removes the result of query from this query.

Parameters

DataQuery $subtractQuery
string $field

Return Value

$this

at line 803
$this selectFromTable(string $table, array $fields)

Select only the 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

at line 820
$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

at line 836
array column(string $field = 'ID')

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

Parameters

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

Return Value

array List of column values for the specified column

at line 895
$this setQueryParam(string $key, mixed $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
mixed $value

Return Value

$this

at line 906
mixed 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

mixed

at line 915
array getQueryParams()

Returns all query parameters

Return Value

array query parameters array