SQLSelect
class SQLSelect extends SQLConditionalExpression (View source)
Object representing a SQL SELECT query.
The various parts of the SQL query can be manipulated individually.
Properties
protected | array | $replacementsOld | Keep an internal register of find/replace pairs to execute when it's time to actually get the query SQL. |
from SQLExpression |
protected | array | $replacementsNew | Keep an internal register of find/replace pairs to execute when it's time to actually get the query SQL. |
from SQLExpression |
protected | array | $where | An array of WHERE clauses. |
from SQLConditionalExpression |
protected | string | $connective | The logical connective used to join WHERE clauses. Defaults to AND. |
from SQLConditionalExpression |
protected | array | $from | An array of tables. The first one is just the table name. |
from SQLConditionalExpression |
protected | array | $select | An array of SELECT fields, keyed by an optional alias. |
|
protected | array | $groupby | An array of GROUP BY clauses. |
|
protected | array | $having | An array of having clauses. |
|
protected | bool | $distinct | If this is true DISTINCT will be added to the SQL. |
|
protected | string | $orderby | An array of ORDER BY clauses, functions. Stores as an associative array of column / function to direction. |
|
protected | array | $limit | An array containing limit and offset keys for LIMIT clause. |
Methods
Swap some text in the SQL query with another.
Swap the use of one table with another.
Generate the SQL statement for this query.
Copies the query parameters contained in this object to another SQLExpression
Construct a new SQLSelect.
Sets the list of tables to query from or update
Add a table to include in the query or update
Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.
Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.
Add a LEFT JOIN criteria to the tables list.
Add an INNER JOIN criteria
Add an additional filter (part of the ON clause) on a join.
Set the filter (part of the ON clause) on a join.
Returns true if we are already joining to the given table alias
Ensure that framework "auto-generated" table JOINs are first in the finalised SQL query.
Since uasort don't preserve the order of an array if the comparison is equal we have to resort to a merge sort. It's quick and stable: O(n*log(n)).
Set a WHERE clause.
Adds a WHERE clause.
No description
No description
Return a list of WHERE clauses used internally.
Given a key / value pair, extract the predicate and any potential paramaters in a format suitable for storing internally as a list of paramaterised conditions.
Given a list of conditions in any user-acceptable format, convert this to an array of paramaterised predicates suitable for merging with $this->where.
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
Checks whether this query is filtering on a foreign key, ie finding a has_many relationship
Generates an SQLDelete object using the currently specified parameters
Generates an SQLSelect object using the currently specified parameters.
Generates an SQLUpdate object using the currently specified parameters.
Construct a new SQLSelect.
Add to the list of columns to be selected by the query.
Return the SQL expression for the given field alias.
Pass LIMIT clause either as SQL snippet or in array format.
Set ORDER BY clause either as SQL snippet or in array format.
Add ORDER BY clause either as SQL snippet or in array format.
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.
Reverses the order by clause by replacing ASC or DESC references in the current order by with it's corollary.
Return a list of HAVING clauses used internally.
Return an itemised select list as a map, where keys are the aliases, and values are the column sources.
Return the number of rows in this query if the limit were removed. Useful in paged data sets.
Returns true if this query can be sorted by the given field.
Return the number of rows in this query, respecting limit and offset.
Return a new SQLSelect that calls the given aggregate functions on this data.
Details
__get($field)
deprecated
deprecated
No description
__set($field, $value)
deprecated
deprecated
No description
replaceText(string $old, string $new)
Swap some text in the SQL query with another.
Note that values in parameters will not be replaced
string
__toString()
Return the generated SQL string for this query
renameTable(string $old, string $new)
Swap the use of one table with another.
bool
isEmpty()
Determine if this query is empty, and thus cannot be executed
string
sql(array $parameters = array())
Generate the SQL statement for this query.
SS_Query
execute()
Execute this query.
protected
copyTo(SQLExpression $object)
Copies the query parameters contained in this object to another SQLExpression
__construct(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.
SQLConditionalExpression
setFrom(string|array $from)
Sets the list of tables to query from or update
SQLConditionalExpression
addFrom(string|array $from)
Add a table to include in the query or update
setConnective(string $value)
Set the connective property.
string
getConnective()
Get the connective property.
useDisjunction()
Use the disjunctive operator 'OR' to join filter expressions in the WHERE clause.
useConjunction()
Use the conjunctive operator 'AND' to join filter expressions in the WHERE clause.
SQLConditionalExpression
addLeftJoin(string $table, string $onPredicate, string $tableAlias = '', int $order = 20, array $parameters = array())
Add a LEFT JOIN criteria to the tables list.
SQLConditionalExpression
addInnerJoin(string $table, string $onPredicate, string $tableAlias = null, int $order = 20, array $parameters = array())
Add an INNER JOIN criteria
SQLConditionalExpression
addFilterToJoin(string $table, string $filter)
Add an additional filter (part of the ON clause) on a join.
SQLConditionalExpression
setJoinFilter(string $table, string $filter)
Set the filter (part of the ON clause) on a join.
bool
isJoinedTo(string $tableAlias)
Returns true if we are already joining to the given table alias
array
queriedTables()
Return a list of tables that this query is selecting from.
array
getFrom()
Return a list of tables queried
array
getJoins(array $parameters = array())
Retrieves the finalised list of joins
protected array
getOrderedJoins($from)
Ensure that framework "auto-generated" table JOINs are first in the finalised SQL query.
This prevents issues where developer-initiated JOINs attempt to JOIN using relations that haven't actually yet been scaffolded by the framework. Demonstrated by PostGres in errors like: "...ERROR: missing FROM-clause..."
protected
mergesort(array $array, callable $cmpFunction = 'strcmp')
Since uasort don't preserve the order of an array if the comparison is equal we have to resort to a merge sort. It's quick and stable: O(n*log(n)).
SQLConditionalExpression
setWhere(mixed $where)
Set a WHERE clause.
SQLConditionalExpression
addWhere(mixed $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'
)
));
SQLConditionalExpression
setWhereAny(mixed $filters)
No description
SQLConditionalExpression
addWhereAny(mixed $filters)
No description
array
getWhere()
Return a list of WHERE clauses used internally.
array
getWhereParameterised(array $parameters)
Return a list of WHERE clauses used internally.
protected array|SQLConditionGroup
parsePredicate(string|int $key, mixed $value)
Given a key / value pair, extract the predicate and any potential paramaters in a format suitable for storing internally as a list of paramaterised conditions.
protected array
normalisePredicates(array $predicates)
Given a list of conditions in any user-acceptable format, convert this to an array of paramaterised predicates suitable for merging with $this->where.
Normalised predicates are in the below format, in order to avoid key collisions.
array(
array('Condition != ?' => array('parameter')),
array('Condition != ?' => array('otherparameter')),
array('Condition = 3' => array()),
array('Condition = ? OR Condition = ?' => array('parameter1', 'parameter2))
)
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.
bool
filtersOnID()
Checks whether this query is for a specific ID in a table
bool
filtersOnFK()
Checks whether this query is filtering on a foreign key, ie finding a has_many relationship
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.
No fields will have any assigned values for the newly generated SQLUpdate object.
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.
$this
setSelect(string|array $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"');
$this
addSelect(string|array $fields)
Add to the list of columns to be selected by the query.
$this
selectField(string $field, string|null $alias = null)
Select an additional field.
string
expressionForField(string $field)
Return the SQL expression for the given field alias.
Returns null if the given alias doesn't exist. See selectField() for details on alias generation.
SQLSelect
setDistinct(bool $value)
Set distinct property.
bool
getDistinct()
Get the distinct property.
array
getLimit()
Get the limit property.
SQLSelect
setLimit(int|string|array $limit, int $offset = 0)
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'
$this
setOrderBy(string|array $clauses = null, string $direction = null)
Set ORDER BY clause either as SQL snippet or in array format.
$this
addOrderBy(string|array $clauses = null, string $direction = null)
Add ORDER BY clause either as SQL snippet or in array format.
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.
SQLSelect
reverseOrderBy()
Reverses the order by clause by replacing ASC or DESC references in the current order by with it's corollary.
SQLSelect
setGroupBy(string|array $groupby)
Set a GROUP BY clause.
SQLSelect
addGroupBy(string|array $groupby)
Add a GROUP BY clause.
SQLSelect
setHaving(mixed $having)
Set a HAVING clause.
SQLSelect
addHaving(mixed $having)
Add a HAVING clause
array
getHaving()
Return a list of HAVING clauses used internally.
array
getHavingParameterised(array $parameters)
Return a list of HAVING clauses used internally.
array
getGroupBy()
Return a list of GROUP BY clauses used internally.
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"'.
int
unlimitedRowCount(string $column = null)
Return the number of rows in this query if the limit were removed. Useful in paged data sets.
bool
canSortBy(string $fieldName)
Returns true if this query can be sorted by the given field.
int
count(string $column = null)
Return the number of rows in this query, respecting limit and offset.
SQLSelect
aggregate(string $column, string $alias = null)
Return a new SQLSelect that calls the given aggregate functions on this data.
SQLSelect
firstRow()
Returns a query that returns only the first row of this query
SQLSelect
lastRow()
Returns a query that returns only the last row of this query