SQLUpdate
class SQLUpdate extends SQLConditionalExpression implements SQLWriteExpression (View source)
Object representing a SQL UPDATE query.
The various parts of the SQL query can be manipulated individually.
Methods
Swap some text in the SQL query with another.
Determine if this query is empty, and thus cannot be executed
Generate the SQL statement for this query.
Construct a new SQLUpdate object
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
Return a list of tables that this query is selecting from.
Retrieves the finalised list of joins
Return a list of WHERE clauses used internally.
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 for a specific ID in a table
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 SQLUpdate object
Sets the table name to update
Gets the table name to update
Adds assignments for a list of several fields.
Sets the list of assignments to the given list
Retrieves the list of assignments in parameterised format
Set the value for a single field
Assigns a value to a field using the literal SQL expression, rather than a value to be escaped
Clears all currently set assigment values
Details
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.
Query
execute()
Execute this query.
__construct(string $table = null, array $assignment = array(), array $where = array())
Construct a new SQLUpdate object
$this
setFrom(string|array $from)
Sets the list of tables to query from or update
$this
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.
$this
addLeftJoin(string $table, string $onPredicate, string $tableAlias = '', int $order = 20, array $parameters = array())
Add a LEFT JOIN criteria to the tables list.
$this
addInnerJoin(string $table, string $onPredicate, string $tableAlias = null, int $order = 20, array $parameters = array())
Add an INNER JOIN criteria
$this
addFilterToJoin(string $table, string $filter)
Add an additional filter (part of the ON clause) on a join.
$this
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
$this
setWhere(mixed $where)
Set a WHERE clause.
$this
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'
)
));
$this
setWhereAny(mixed $filters)
$this
addWhereAny(mixed $filters)
array
getWhere()
Return a list of WHERE clauses used internally.
array
getWhereParameterised(array $parameters)
Return a list of WHERE clauses used internally.
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 SQLUpdate
create(string $table = null, array $assignment = array(), array $where = array())
Construct a new SQLUpdate object
$this
setTable(string $table)
Sets the table name to update
string
getTable()
Gets the table name to update
$this
addAssignments(array $assignments)
Adds assignments for a list of several fields.
For multi-row objects this applies this to the current row.
Note that field values must not be escaped, as these will be internally parameterised by the database engine.
// Basic assignments $query->addAssignments(array( '"Object"."Title"' => 'Bob', '"Object"."Description"' => 'Bob was here' ))
// Parameterised assignments $query->addAssignments(array( '"Object"."Title"' => array('?' => 'Bob')), '"Object"."Description"' => array('?' => null)) ))
// Complex parameters $query->addAssignments(array( '"Object"."Score"' => array('MAX(?,?)' => array(1, 3)) ));
// Assigment of literal SQL for a field. The empty array is // important to denote the zero-number paramater list $query->addAssignments(array( '"Object"."Score"' => array('NOW()' => array()) ));
$this
setAssignments(array $assignments)
Sets the list of assignments to the given list
For multi-row objects this applies this to the current row.
array
getAssignments()
Retrieves the list of assignments in parameterised format
For multi-row objects returns assignments for the current row.
$this
assign(string $field, mixed $value)
Set the value for a single field
For multi-row objects this applies this to the current row.
E.g.
// Literal assignment $query->assign('"Object"."Description"', 'lorum ipsum'));
// Single parameter $query->assign('"Object"."Title"', array('?' => 'Bob'));
// Complex parameters $query->assign('"Object"."Score"', array('MAX(?,?)' => array(1, 3));
$this
assignSQL(string $field, string $sql)
Assigns a value to a field using the literal SQL expression, rather than a value to be escaped
For multi-row objects this applies this to the current row.
$this
clear()
Clears all currently set assigment values