class DataQuery (View source)

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

Acts as a wrapper over SQLQuery and performs all of the query generation. Used extensively by DataList.

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

Properties

protected string $dataClass
protected SQLQuery $query
protected array $collidingFields
protected $querySubclasses
protected $filterByClassName

Methods

public
__construct($dataClass)

Create a new DataQuery.

public
__clone()

Clone this object

public
string
dataClass()

Return the DataObject class that is being queried.

public
query()

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

public
$this
removeFilterOn(string|array $fieldExpression)

Remove a filter from the query

public
initialiseQuery()

Set up the simplest initial query

public
$this
setQueriedColumns(array $queriedColumns)

No description

public
getFinalisedQuery(array|null $queriedColumns = null)

Ensure that the query is ready to execute.

protected
null
ensureSelectContainsOrderbyColumns(SQLQuery $query, array $originalSelect = array())

Ensure that if a query has an order by clause, those columns are present in the select.

public
execute()

Execute the query and return the result as Query object.

public
string
sql(array $parameters = array())

Return this query's SQL

public
int
count()

Return the number of records in this query.

public
string
max(string $field)

Return the maximum value of the given field in this DataList

public
string
min(string $field)

Return the minimum value of the given field in this DataList

public
string
avg(string $field)

Return the average value of the given field in this DataList

public
string
sum(string $field)

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

public
string
aggregate(string $expression)

Runs a raw aggregate expression. Please handle escaping yourself

public
firstRow()

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

public
lastRow()

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

protected
selectColumnsFromTable(SQLQuery $query, string $tableClass, array $columns = null)

Update the SELECT clause of the query with the columns from the given table

public
$this
groupby(string $groupby)

Append a GROUP BY clause to this query.

public
$this
having(string $having)

Append a HAVING clause to this query.

public
disjunctiveGroup()

Create a disjunctive subgroup.

public
conjunctiveGroup()

Create a conjunctive subgroup

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

Adds a WHERE clause.

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

Append a WHERE with OR.

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

Set the ORDER BY clause of this query

public
$this
reverseSort()

Reverse order by clause

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

Set the limit of this query.

public
$this
distinct(bool $value)

Set whether this query should be distinct or not.

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

Add an INNER JOIN clause to this query.

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

Add a LEFT JOIN clause to this query.

public
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 SearchFilter->apply() methods manually.

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

Removes the result of query from this query.

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

Select only the given fields from the given table.

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

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

public
array
column(string $field = 'ID')

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

protected
string|null
expressionForField(string $field)

No description

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

Select the given field expressions.

public
$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.

public
mixed
getQueryParam(string $key)

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

public
array
getQueryParams()

Returns all query parameters

Details

__construct($dataClass)

Create a new DataQuery.

Parameters

$dataClass

__clone()

Clone this object

string dataClass()

Return the DataObject class that is being queried.

Return Value

string

SQLQuery query()

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

Return Value

SQLQuery

$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

initialiseQuery()

Set up the simplest initial query

$this setQueriedColumns(array $queriedColumns)

No description

Parameters

array $queriedColumns

Return Value

$this

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

protected null ensureSelectContainsOrderbyColumns(SQLQuery $query, array $originalSelect = array())

Ensure that if a query has an order by clause, those columns are present in the select.

Parameters

SQLQuery $query
array $originalSelect

Return Value

null

SS_Query execute()

Execute the query and return the result as Query object.

Return Value

SS_Query

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)

int count()

Return the number of records in this query.

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

Return Value

int

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

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

protected selectColumnsFromTable(SQLQuery $query, string $tableClass, array $columns = null)

Update the SELECT clause of the query with the columns from the given table

Parameters

SQLQuery $query
string $tableClass
array $columns

$this groupby(string $groupby)

Append a GROUP BY clause to this query.

Parameters

string $groupby

Escaped SQL statement

Return Value

$this

$this having(string $having)

Append a HAVING clause to this query.

Parameters

string $having

Escaped SQL statement

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

\SQLQuery::addWhere() for syntax examples, although DataQuery won't expand multiple arguments as SQLQuery 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

\SQLQuery::addWhere() for syntax examples, although DataQuery won't expand multiple method arguments as SQLQuery 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

\SQLQuery::orderby()

$this reverseSort()

Reverse order by clause

Return Value

$this

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

Set the limit of this query.

Parameters

int $limit
int $offset

$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 = 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

$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

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

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

$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.

Parameters

string $field

See expressionForField().

Return Value

array

List of column values for the specified column

protected string|null expressionForField(string $field)

No description

Parameters

string $field

Select statement identifier, either the unquoted column name, the full composite SQL statement, or the alias set through SQLQuery->selectField().

Return Value

string|null

The expression used to query this field via this DataQuery

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

Select the given field expressions.

Parameters

string $fieldExpression

The field to select (escaped SQL statement)

string $alias

The alias of that field (escaped SQL statement)

$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

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

array getQueryParams()

Returns all query parameters

Return Value

array

query parameters array