1. class
  2. SilverStripe
  3. \
  4. ORM
  5. \
  6. Queries
  7. \
  8. SQLDelete

SQLDelete

class SQLDelete extends SQLConditionalExpression

Object representing a SQL DELETE query.

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

Methods

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.

from SQLExpression
Query
execute()

Execute this query.

from SQLExpression
__construct(array|string $from = array(), array $where = array(), array|string $delete = array())

Construct a new SQLDelete.

$this
setFrom(string|array $from)

Sets the list of tables to query from or update

from SQLConditionalExpression
$this
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
$this
addLeftJoin(string $table, string $onPredicate, string $tableAlias = '', int $order = 20, array $parameters = array())

Add a LEFT JOIN criteria to the tables list.

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

Add an INNER JOIN criteria

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

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

from SQLConditionalExpression
$this
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
$this
setWhere($where)

Set a WHERE clause.

from SQLConditionalExpression
$this
addWhere($where)

Adds a WHERE clause.

from SQLConditionalExpression
$this
setWhereAny($filters)

No description

from SQLConditionalExpression
$this
addWhereAny($filters)

No description

from SQLConditionalExpression
array
getWhere()

Return a list of WHERE clauses used internally.

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

from SQLConditionalExpression
SQLSelect
toSelect()

Generates an SQLSelect object using the currently specified parameters.

from SQLConditionalExpression
SQLUpdate
toUpdate()

Generates an SQLUpdate object using the currently specified parameters.

from SQLConditionalExpression
static SQLDelete
create(array|string $from = array(), array $where = array(), array|string $delete = array())

Construct a new SQLDelete.

array
getDelete()

List of tables to limit the delete to, if multiple tables are specified in the condition clause

$this
setDelete(string|array $tables)

Sets the list of tables to limit the delete to, if multiple tables are specified in the condition clause

$this
addDelete(string|array $tables)

Sets the list of tables to limit the delete to, if multiple tables are specified in the condition clause

Details

in SQLExpression at line 39
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 52
string __toString()

Return the generated SQL string for this query

Return Value

string

in SQLExpression at line 71
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 747
bool isEmpty()

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

Return Value

bool Flag indicating that this query is empty

in SQLExpression at line 91
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 112
Query execute()

Execute this query.

Return Value

Query

at line 46
__construct(array|string $from = array(), array $where = array(), array|string $delete = array())

Construct a new SQLDelete.

Parameters

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|string $delete The table(s) to delete, if multiple tables are queried from

in SQLConditionalExpression at line 65
$this 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

$this

in SQLConditionalExpression at line 79
$this 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

$this Self reference

in SQLConditionalExpression at line 95
setConnective(string $value)

Set the connective property.

Parameters

string $value either 'AND' or 'OR'

in SQLConditionalExpression at line 105
string getConnective()

Get the connective property.

Return Value

string 'AND' or 'OR'

in SQLConditionalExpression at line 113
useDisjunction()

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

in SQLConditionalExpression at line 121
useConjunction()

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

in SQLConditionalExpression at line 139
$this 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

$this Self reference

in SQLConditionalExpression at line 167
$this 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

$this Self reference

in SQLConditionalExpression at line 189
$this 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

$this Self reference

in SQLConditionalExpression at line 202
$this 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

$this Self reference

in SQLConditionalExpression at line 214
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 224
array queriedTables()

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

Return Value

array Unquoted table names

in SQLConditionalExpression at line 253
array getFrom()

Return a list of tables queried

Return Value

array

in SQLConditionalExpression at line 266
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 426
$this setWhere($where)

Set a WHERE clause.

Parameters

$where

Return Value

$this Self reference

See also

SQLConditionalExpression::addWhere() for syntax examples

in SQLConditionalExpression at line 513
$this 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

$this Self reference

in SQLConditionalExpression at line 530
$this setWhereAny($filters)

Parameters

$filters

Return Value

$this Self reference

See also

SQLConditionalExpression::addWhere()

in SQLConditionalExpression at line 545
$this addWhereAny($filters)

Parameters

$filters

Return Value

$this Self reference

See also

SQLConditionalExpression::addWhere()

in SQLConditionalExpression at line 560
array getWhere()

Return a list of WHERE clauses used internally.

Return Value

array

in SQLConditionalExpression at line 571
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 684
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 713
boolean filtersOnID()

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

Return Value

boolean

in SQLConditionalExpression at line 733
boolean filtersOnFK()

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

Return Value

boolean

in SQLConditionalExpression at line 757
SQLDelete toDelete()

Generates an SQLDelete object using the currently specified parameters

Return Value

SQLDelete

in SQLConditionalExpression at line 769
SQLSelect toSelect()

Generates an SQLSelect object using the currently specified parameters.

Return Value

SQLSelect

in SQLConditionalExpression at line 783
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

at line 33
static SQLDelete create(array|string $from = array(), array $where = array(), array|string $delete = array())

Construct a new SQLDelete.

Parameters

array|string $from An array of Tables (FROM clauses). The first one should be just the table name. Each should be ANSI quoted.
array $where An array of WHERE clauses.
array|string $delete The table(s) to delete, if multiple tables are queried from

Return Value

SQLDelete

at line 58
array getDelete()

List of tables to limit the delete to, if multiple tables are specified in the condition clause

Return Value

array

at line 70
$this setDelete(string|array $tables)

Sets the list of tables to limit the delete to, if multiple tables are specified in the condition clause

Parameters

string|array $tables Escaped SQL statement, usually an unquoted table name

Return Value

$this Self reference

at line 83
$this addDelete(string|array $tables)

Sets the list of tables to limit the delete to, if multiple tables are specified in the condition clause

Parameters

string|array $tables Escaped SQL statement, usually an unquoted table name

Return Value

$this Self reference