SQLQuery deprecated

class SQLQuery extends SQLSelect

deprecated since version 4.0

Object representing a SQL SELECT query.

The various parts of the SQL query can be manipulated individually.

Methods

__get($field) deprecated

No description

from SQLExpression
__set($field, $value) deprecated

No description

from SQLExpression
replaceText(string $old, string $new)

Swap some text in the SQL query with another.

from SQLExpression
string
__toString()

Return the generated SQL string for this query

from SQLExpression
renameTable(string $old, string $new)

Swap the use of one table with another.

from SQLExpression
bool
isEmpty()

Determine if this query is empty, and thus cannot be executed

from SQLConditionalExpression
string
sql(array $parameters = array())

Generate the SQL statement for this query.

SS_Query
execute()

Execute this query.

from SQLExpression
__construct(array $select = "*", array|string $from = array(), array $where = array(), array $orderby = array(), array $groupby = array(), array $having = array(), array|string $limit = array()) deprecated

No description

SQLConditionalExpression
setFrom(string|array $from)

Sets the list of tables to query from or update

from SQLConditionalExpression
SQLConditionalExpression
addFrom(string|array $from)

Add a table to include in the query or update

from SQLConditionalExpression
setConnective(string $value)

Set the connective property.

from SQLConditionalExpression
string
getConnective()

Get the connective property.

from SQLConditionalExpression
useDisjunction()

Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.

from SQLConditionalExpression
useConjunction()

Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.

from SQLConditionalExpression
SQLConditionalExpression
addLeftJoin(string $table, string $onPredicate, string $tableAlias = '', int $order = 20, array $parameters = array())

Add a LEFT JOIN criteria to the tables list.

from SQLConditionalExpression
SQLConditionalExpression
addInnerJoin(string $table, string $onPredicate, string $tableAlias = null, int $order = 20, array $parameters = array())

Add an INNER JOIN criteria

from SQLConditionalExpression
SQLConditionalExpression
addFilterToJoin(string $table, string $filter)

Add an additional filter (part of the ON clause) on a join.

from SQLConditionalExpression
SQLConditionalExpression
setJoinFilter(string $table, string $filter)

Set the filter (part of the ON clause) on a join.

from SQLConditionalExpression
boolean
isJoinedTo(string $tableAlias)

Returns true if we are already joining to the given table alias

from SQLConditionalExpression
array
queriedTables()

Return a list of tables that this query is selecting from.

from SQLConditionalExpression
array
getFrom()

Return a list of tables queried

from SQLConditionalExpression
array
getJoins(array $parameters = array())

Retrieves the finalised list of joins

from SQLConditionalExpression
SQLConditionalExpression
setWhere($where)

Set a WHERE clause.

from SQLConditionalExpression
SQLConditionalExpression
addWhere($where)

Adds a WHERE clause.

from SQLConditionalExpression
SQLConditionalExpression
setWhereAny($filters)

No description

from SQLConditionalExpression
SQLConditionalExpression
addWhereAny($filters)

No description

from SQLConditionalExpression
array
getWhere()

Return a list of SQL where conditions (flattened as a list of strings)

array
getWhereParameterised(array $parameters)

Return a list of WHERE clauses used internally.

from SQLConditionalExpression
splitQueryParameters(array $conditions, array $predicates, array $parameters)

Given a list of conditions as per the format of $this->where, split this into an array of predicates, and a separate array of ordered parameters

from SQLConditionalExpression
boolean
filtersOnID()

Checks whether this query is for a specific ID in a table

from SQLConditionalExpression
boolean
filtersOnFK()

Checks whether this query is filtering on a foreign key, ie finding a has_many relationship

from SQLConditionalExpression
SQLDelete
toDelete()

Generates an SQLDelete object using the currently specified parameters

SQLSelect
toSelect()

Generates an SQLSelect object using the currently specified parameters.

SQLUpdate
toUpdate()

Generates an SQLUpdate object using the currently specified parameters.

from SQLConditionalExpression
static SQLSelect
create(array $select = "*", array|string $from = array(), array $where = array(), array $orderby = array(), array $groupby = array(), array $having = array(), array|string $limit = array())

Construct a new SQLSelect.

from SQLSelect
$this
setSelect($fields)

Set the list of columns to be selected by the query.

from SQLSelect
$this
addSelect(string|array $fields)

Add to the list of columns to be selected by the query.

from SQLSelect
$this
selectField(string $field, string|null $alias = null)

Select an additional field.

from SQLSelect
string
expressionForField(string $field)

Return the SQL expression for the given field alias.

from SQLSelect
SQLSelect
setDistinct(bool $value)

Set distinct property.

from SQLSelect
bool
getDistinct()

Get the distinct property.

from SQLSelect
array
getLimit()

Get the limit property.

from SQLSelect
SQLSelect
setLimit(int|string|array $limit, int $offset)

Pass LIMIT clause either as SQL snippet or in array format.

from SQLSelect
$this
setOrderBy(string|array $clauses = null, string $direction = null)

Set ORDER BY clause either as SQL snippet or in array format.

from SQLSelect
$this
addOrderBy(string|array $clauses = null, string $direction = null)

Add ORDER BY clause either as SQL snippet or in array format.

from SQLSelect
array
getOrderBy()

Returns the current order by as array if not already. To handle legacy statements which are stored as strings. Without clauses and directions, convert the orderby clause to something readable.

from SQLSelect
SQLSelect
reverseOrderBy()

Reverses the order by clause by replacing ASC or DESC references in the current order by with it's corollary.

from SQLSelect
SQLSelect
setGroupBy(string|array $groupby)

Set a GROUP BY clause.

from SQLSelect
SQLSelect
addGroupBy(string|array $groupby)

Add a GROUP BY clause.

from SQLSelect
SQLSelect
setHaving($having)

Set a HAVING clause.

from SQLSelect
SQLSelect
addHaving($having)

Add a HAVING clause

from SQLSelect
array
getHaving()

Return a list of HAVING clauses used internally.

from SQLSelect
array
getHavingParameterised(array $parameters)

Return a list of HAVING clauses used internally.

from SQLSelect
array
getGroupBy()

Return a list of GROUP BY clauses used internally.

from SQLSelect
array
getSelect()

Return an itemised select list as a map, where keys are the aliases, and values are the column sources.

from SQLSelect
int
unlimitedRowCount(string $column = null)

Return the number of rows in this query if the limit were removed. Useful in paged data sets.

from SQLSelect
bool
canSortBy(string $fieldName)

Returns true if this query can be sorted by the given field.

from SQLSelect
int
count(string $column = null)

Return the number of rows in this query, respecting limit and offset.

from SQLSelect
SQLSelect
aggregate(string $column, string $alias = null)

Return a new SQLSelect that calls the given aggregate functions on this data.

from SQLSelect
SQLSelect
firstRow()

Returns a query that returns only the first row of this query

from SQLSelect
SQLSelect
lastRow()

Returns a query that returns only the last row of this query

from SQLSelect
setDelete($value) deprecated

No description

getDelete() deprecated

No description

SQLExpression
toAppropriateExpression()

Convert this SQLQuery to a SQLExpression based on its internal $delete state (Normally SQLSelect or SQLDelete)

Details

in SQLExpression at line 29
__get($field) deprecated

deprecated since version 4.0

Parameters

$field

in SQLExpression at line 37
__set($field, $value) deprecated

deprecated since version 4.0

Parameters

$field
$value

in SQLExpression at line 52
replaceText(string $old, string $new)

Swap some text in the SQL query with another.

Note that values in parameters will not be replaced

Parameters

string $old The old text (escaped)
string $new The new text (escaped)

in SQLExpression at line 64
string __toString()

Return the generated SQL string for this query

Return Value

string

in SQLExpression at line 82
renameTable(string $old, string $new)

Swap the use of one table with another.

Parameters

string $old Name of the old table (unquoted, escaped)
string $new Name of the new table (unquoted, escaped)

in SQLConditionalExpression at line 680
bool isEmpty()

Determine if this query is empty, and thus cannot be executed

Return Value

bool Flag indicating that this query is empty

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

Generate the SQL statement for this query.

Parameters

array $parameters Out variable for parameters required for this query

Return Value

string The completed SQL query

in SQLExpression at line 119
SS_Query execute()

Execute this query.

Return Value

SS_Query

at line 27
__construct(array $select = "*", array|string $from = array(), array $where = array(), array $orderby = array(), array $groupby = array(), array $having = array(), array|string $limit = array()) deprecated

deprecated since version 4.0

Parameters

array $select An array of SELECT fields.
array|string $from An array of Tables (FROM clauses). The first one should be just the table name.
array $where An array of WHERE clauses.
array $orderby An array ORDER BY clause.
array $groupby An array of GROUP BY clauses.
array $having An array of HAVING clauses.
array|string $limit A LIMIT clause or array with limit and offset keys

in SQLConditionalExpression at line 62
SQLConditionalExpression setFrom(string|array $from)

Sets the list of tables to query from or update

Parameters

string|array $from Single, or list of, ANSI quoted table names

Return Value

SQLConditionalExpression

in SQLConditionalExpression at line 75
SQLConditionalExpression addFrom(string|array $from)

Add a table to include in the query or update

Parameters

string|array $from Single, or list of, ANSI quoted table names

Return Value

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 90
setConnective(string $value)

Set the connective property.

Parameters

string $value either 'AND' or 'OR'

in SQLConditionalExpression at line 99
string getConnective()

Get the connective property.

Return Value

string 'AND' or 'OR'

in SQLConditionalExpression at line 106
useDisjunction()

Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.

in SQLConditionalExpression at line 113
useConjunction()

Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.

in SQLConditionalExpression at line 130
SQLConditionalExpression addLeftJoin(string $table, string $onPredicate, string $tableAlias = '', int $order = 20, array $parameters = array())

Add a LEFT JOIN criteria to the tables list.

Parameters

string $table Unquoted table name
string $onPredicate The "ON" SQL fragment in a "LEFT JOIN ... AS ... ON ..." statement, Needs to be valid (quoted) SQL.
string $tableAlias Optional alias which makes it easier to identify and replace joins later on
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

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 157
SQLConditionalExpression addInnerJoin(string $table, string $onPredicate, string $tableAlias = null, int $order = 20, array $parameters = array())

Add an INNER JOIN criteria

Parameters

string $table Unquoted table name
string $onPredicate The "ON" SQL fragment in an "INNER JOIN ... AS ... ON ..." statement. Needs to be valid (quoted) SQL.
string $tableAlias Optional alias which makes it easier to identify and replace joins later on
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

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 176
SQLConditionalExpression addFilterToJoin(string $table, string $filter)

Add an additional filter (part of the ON clause) on a join.

Parameters

string $table Table to join on from the original join (unquoted)
string $filter The "ON" SQL fragment (escaped)

Return Value

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 188
SQLConditionalExpression setJoinFilter(string $table, string $filter)

Set the filter (part of the ON clause) on a join.

Parameters

string $table Table to join on from the original join (unquoted)
string $filter The "ON" SQL fragment (escaped)

Return Value

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 199
boolean isJoinedTo(string $tableAlias)

Returns true if we are already joining to the given table alias

Parameters

string $tableAlias Table name

Return Value

boolean

in SQLConditionalExpression at line 208
array queriedTables()

Return a list of tables that this query is selecting from.

Return Value

array Unquoted table names

in SQLConditionalExpression at line 234
array getFrom()

Return a list of tables queried

Return Value

array

in SQLConditionalExpression at line 246
array getJoins(array $parameters = array())

Retrieves the finalised list of joins

Parameters

array $parameters Out variable for parameters required for this query

Return Value

array List of joins as a mapping from array('Alias' => 'Join Expression')

in SQLConditionalExpression at line 380
SQLConditionalExpression setWhere($where)

Set a WHERE clause.

Parameters

$where

Return Value

SQLConditionalExpression Self reference

See also

SQLConditionalExpression::addWhere() for syntax examples

in SQLConditionalExpression at line 466
SQLConditionalExpression addWhere($where)

Adds a WHERE clause.

Note that the database will execute any parameterised queries using prepared statements whenever available.

There are several different ways of doing this.

// the entire predicate as a single string $query->addWhere("\"Column\" = 'Value'");

// multiple predicates as an array $query->addWhere(array("\"Column\" = 'Value'", "\"Column\" != 'Value'"));

// Shorthand for the above using argument expansion $query->addWhere("\"Column\" = 'Value'", "\"Column\" != 'Value'");

// multiple predicates with parameters $query->addWhere(array('"Column" = ?' => $column, '"Name" = ?' => $value)));

// Shorthand for simple column comparison (as above), omitting the '?' $query->addWhere(array('"Column"' => $column, '"Name"' => $value));

// Multiple predicates, each with multiple parameters. $query->addWhere(array( '"ColumnOne" = ? OR "ColumnTwo" != ?' => array(1, 4), '"ID" != ?' => $value ));

// Using a dynamically generated condition (any object that implements SQLConditionGroup) $condition = new ObjectThatImplements_SQLConditionGroup(); $query->addWhere($condition);

Note that if giving multiple parameters for a single predicate the array of values must be given as an indexed array, not an associative array.

Also should be noted is that any null values for parameters may give unexpected behaviour. array('Column' => NULL) is shorthand for array('Column = ?', NULL), and will not match null values for that column, as 'Column IS NULL' is the correct syntax.

Additionally, be careful of key conflicts. Adding two predicates with the same condition but different parameters can cause a key conflict if added in the same array. This can be solved by wrapping each individual condition in an array. E.g.

// Multiple predicates with duplicate conditions $query->addWhere(array( array('ID != ?' => 5), array('ID != ?' => 6) ));

// Alternatively this can be added in two separate calls to addWhere $query->addWhere(array('ID != ?' => 5)); $query->addWhere(array('ID != ?' => 6));

// Or simply omit the outer array $query->addWhere(array('ID != ?' => 5), array('ID != ?' => 6));

If it's necessary to force the parameter to be considered as a specific data type by the database connector's prepared query processor any parameter can be cast to that type by using the following format.

// Treat this value as a double type, regardless of its type within PHP $query->addWhere(array( 'Column' => array( 'value' => $variable, 'type' => 'double' ) ));

Parameters

$where

Return Value

SQLConditionalExpression Self reference

in SQLConditionalExpression at line 482
SQLConditionalExpression setWhereAny($filters)

Parameters

$filters

Return Value

SQLConditionalExpression Self reference

See also

SQLConditionalExpression::addWhere()

in SQLConditionalExpression at line 496
SQLConditionalExpression addWhereAny($filters)

Parameters

$filters

Return Value

SQLConditionalExpression Self reference

See also

SQLConditionalExpression::addWhere()

at line 68
array getWhere()

Return a list of SQL where conditions (flattened as a list of strings)

Return Value

array

in SQLConditionalExpression at line 520
array getWhereParameterised(array $parameters)

Return a list of WHERE clauses used internally.

Parameters

array $parameters Out variable for parameters required for this query

Return Value

array

in SQLConditionalExpression at line 624
splitQueryParameters(array $conditions, array $predicates, array $parameters)

Given a list of conditions as per the format of $this->where, split this into an array of predicates, and a separate array of ordered parameters

Note, that any SQLConditionGroup objects will be evaluated here.

Parameters

array $conditions List of Conditions including parameters
array $predicates Out parameter for the list of string predicates
array $parameters Out parameter for the list of parameters

See also

SQLConditionGroup

in SQLConditionalExpression at line 652
boolean filtersOnID()

Checks whether this query is for a specific ID in a table

Return Value

boolean

in SQLConditionalExpression at line 669
boolean filtersOnFK()

Checks whether this query is filtering on a foreign key, ie finding a has_many relationship

Return Value

boolean

at line 107
SQLDelete toDelete()

Generates an SQLDelete object using the currently specified parameters

Return Value

SQLDelete

at line 96
SQLSelect toSelect()

Generates an SQLSelect object using the currently specified parameters.

Return Value

SQLSelect

in SQLConditionalExpression at line 713
SQLUpdate toUpdate()

Generates an SQLUpdate object using the currently specified parameters.

No fields will have any assigned values for the newly generated SQLUpdate object.

Return Value

SQLUpdate

in SQLSelect at line 74
static SQLSelect create(array $select = "*", array|string $from = array(), array $where = array(), array $orderby = array(), array $groupby = array(), array $having = array(), array|string $limit = array())

Construct a new SQLSelect.

Parameters

array $select An array of SELECT fields.
array|string $from An array of FROM clauses. The first one should be just the table name. Each should be ANSI quoted.
array $where An array of WHERE clauses.
array $orderby An array ORDER BY clause.
array $groupby An array of GROUP BY clauses.
array $having An array of HAVING clauses.
array|string $limit A LIMIT clause or array with limit and offset keys

Return Value

SQLSelect

in SQLSelect at line 121
$this setSelect($fields)

Set the list of columns to be selected by the query.

// pass fields to select as single parameter array $query->setSelect(array('"Col1"', '"Col2"'))->setFrom('"MyTable"');

// pass fields to select as multiple parameters $query->setSelect('"Col1"', '"Col2"')->setFrom('"MyTable"');

// Set a list of selected fields as aliases $query->setSelect(array('Name' => '"Col1"', 'Details' => '"Col2"')->setFrom('"MyTable"');

Parameters

$fields

Return Value

$this Self reference

in SQLSelect at line 139
$this addSelect(string|array $fields)

Add to the list of columns to be selected by the query.

Parameters

string|array $fields Field names should be ANSI SQL quoted. Array keys should be unquoted.

Return Value

$this Self reference

See also

setSelect for example usage

in SQLSelect at line 165
$this selectField(string $field, string|null $alias = null)

Select an additional field.

Parameters

string $field The field to select (ansi quoted SQL identifier or statement)
string|null $alias The alias of that field (unquoted SQL identifier). Defaults to the unquoted column name of the $field parameter.

Return Value

$this Self reference

in SQLSelect at line 182
string expressionForField(string $field)

Return the SQL expression for the given field alias.

Returns null if the given alias doesn't exist. See {@link selectField()} for details on alias generation.

Parameters

string $field

Return Value

string

in SQLSelect at line 192
SQLSelect setDistinct(bool $value)

Set distinct property.

Parameters

bool $value

Return Value

SQLSelect Self reference

in SQLSelect at line 202
bool getDistinct()

Get the distinct property.

Return Value

bool

in SQLSelect at line 210
array getLimit()

Get the limit property.

Return Value

array

in SQLSelect at line 224
SQLSelect setLimit(int|string|array $limit, int $offset)

Pass LIMIT clause either as SQL snippet or in array format.

Internally, limit will always be stored as a map containing the keys 'start' and 'limit'

Parameters

int|string|array $limit If passed as a string or array, assumes SQL escaped data. Only applies for positive values, or if an $offset is set as well.
int $offset

Return Value

SQLSelect Self reference

Exceptions

InvalidArgumentException

in SQLSelect at line 271
$this setOrderBy(string|array $clauses = null, string $direction = null)

Set ORDER BY clause either as SQL snippet or in array format.

Parameters

string|array $clauses Clauses to add (escaped SQL statement)
string $direction Sort direction, ASC or DESC

Return Value

$this Self reference

in SQLSelect at line 289
$this addOrderBy(string|array $clauses = null, string $direction = null)

Add ORDER BY clause either as SQL snippet or in array format.

Parameters

string|array $clauses Clauses to add (escaped SQL statements)
string $direction Sort direction, ASC or DESC

Return Value

$this Self reference

in SQLSelect at line 377
array getOrderBy()

Returns the current order by as array if not already. To handle legacy statements which are stored as strings. Without clauses and directions, convert the orderby clause to something readable.

Return Value

array

in SQLSelect at line 407
SQLSelect reverseOrderBy()

Reverses the order by clause by replacing ASC or DESC references in the current order by with it's corollary.

Return Value

SQLSelect Self reference

in SQLSelect at line 425
SQLSelect setGroupBy(string|array $groupby)

Set a GROUP BY clause.

Parameters

string|array $groupby Escaped SQL statement

Return Value

SQLSelect Self reference

in SQLSelect at line 436
SQLSelect addGroupBy(string|array $groupby)

Add a GROUP BY clause.

Parameters

string|array $groupby Escaped SQL statement

Return Value

SQLSelect Self reference

in SQLSelect at line 455
SQLSelect setHaving($having)

Set a HAVING clause.

Parameters

$having

Return Value

SQLSelect Self reference

See also

SQLSelect::addWhere() for syntax examples

in SQLSelect at line 470
SQLSelect addHaving($having)

Add a HAVING clause

Parameters

$having

Return Value

SQLSelect Self reference

See also

SQLSelect::addWhere() for syntax examples

in SQLSelect at line 483
array getHaving()

Return a list of HAVING clauses used internally.

Return Value

array

in SQLSelect at line 493
array getHavingParameterised(array $parameters)

Return a list of HAVING clauses used internally.

Parameters

array $parameters Out variable for parameters required for this query

Return Value

array

in SQLSelect at line 503
array getGroupBy()

Return a list of GROUP BY clauses used internally.

Return Value

array

in SQLSelect at line 515
array getSelect()

Return an itemised select list as a map, where keys are the aliases, and values are the column sources.

Aliases will always be provided (if the alias is implicit, the alias value will be inferred), and won't be quoted. E.g., 'Title' => '"SiteTree"."Title"'.

Return Value

array

in SQLSelect at line 527
int unlimitedRowCount(string $column = null)

Return the number of rows in this query if the limit were removed. Useful in paged data sets.

Parameters

string $column

Return Value

int

in SQLSelect at line 565
bool canSortBy(string $fieldName)

Returns true if this query can be sorted by the given field.

Parameters

string $fieldName

Return Value

bool

in SQLSelect at line 578
int count(string $column = null)

Return the number of rows in this query, respecting limit and offset.

Parameters

string $column Quoted, escaped column name

Return Value

int

in SQLSelect at line 622
SQLSelect aggregate(string $column, string $alias = null)

Return a new SQLSelect that calls the given aggregate functions on this data.

Parameters

string $column An aggregate expression, such as 'MAX("Balance")', or a set of them (as an escaped SQL statement)
string $alias An optional alias for the aggregate column.

Return Value

SQLSelect A clone of this object with the given aggregate function

in SQLSelect at line 654
SQLSelect firstRow()

Returns a query that returns only the first row of this query

Return Value

SQLSelect A clone of this object with the first row only

in SQLSelect at line 666
SQLSelect lastRow()

Returns a query that returns only the last row of this query

Return Value

SQLSelect A clone of this object with the last row only

at line 37
setDelete($value) deprecated

deprecated since version 4.0

Parameters

$value

at line 45
getDelete() deprecated

deprecated since version 4.0

at line 88
SQLExpression toAppropriateExpression()

Convert this SQLQuery to a SQLExpression based on its internal $delete state (Normally SQLSelect or SQLDelete)

Return Value

SQLExpression