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

replaceText(string $old, string $new)

Swap some text in the SQL query with another.

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 = [])

Generate the SQL statement for this query.

execute()

Execute this query.

__construct(string $table = null, array $assignment = [], array $where = [])

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 = [])

Add a LEFT JOIN criteria to the tables list.

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

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 = [])

Retrieves the finalized list of joins

$this
setWhere(mixed $where)

Set a WHERE clause.

$this
addWhere(mixed $where)

Adds a WHERE clause.

$this
setWhereAny(mixed $filters)

No description

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

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

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

toDelete()

Generates an SQLDelete object using the currently specified parameters

toSelect()

Generates an SQLSelect object using the currently specified parameters.

toUpdate()

Generates an SQLUpdate object using the currently specified parameters.

static SQLUpdate
create(string $table = null, array $assignment = [], array $where = [])

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.

$this
setAssignments(array $assignments)

Sets the list of assignments to the given list

array
getAssignments()

Retrieves the list of assignments in parameterised format

$this
assign(string $field, mixed $value)

Set the value for a single field

$this
assignSQL(string $field, string $sql)

Assigns a value to a field using the literal SQL expression, rather than a value to be escaped

$this
clear()

Clears all currently set assignment 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

Parameters

string $old

The old text (escaped)

string $new

The new text (escaped)

string __toString()

Return the generated SQL string for this query

Return Value

string

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)

bool isEmpty()

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

Return Value

bool

Flag indicating that this query is empty

string sql(array $parameters = [])

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

Query execute()

Execute this query.

Return Value

Query

__construct(string $table = null, array $assignment = [], array $where = [])

Construct a new SQLUpdate object

Parameters

string $table

Table name to update (ANSI quoted)

array $assignment

List of column assignments

array $where

An array of WHERE clauses.

$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

$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

setConnective(string $value)

Set the connective property.

Parameters

string $value

either 'AND' or 'OR'

string getConnective()

Get the connective property.

Return Value

string

'AND' or 'OR'

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 = [])

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

Return Value

$this

Self reference

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

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

Return Value

$this

Self reference

$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

$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

bool isJoinedTo(string $tableAlias)

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

Parameters

string $tableAlias

Table name

Return Value

bool

array queriedTables()

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

Return Value

array

Unquoted table names

array getFrom()

Return a list of tables queried

Return Value

array

array getJoins(array $parameters = [])

Retrieves the finalized 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')

$this setWhere(mixed $where)

Set a WHERE clause.

Parameters

mixed $where

Predicate(s) to set, as escaped SQL statements or parameterized queries

Return Value

$this

Self reference

See also

SQLConditionalExpression::addWhere for syntax examples

$this addWhere(mixed $where)

Adds a WHERE clause.

Note that the database will execute any parameterized 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

mixed $where

Predicate(s) to set, as escaped SQL statements or parameterized queries

Return Value

$this

Self reference

$this setWhereAny(mixed $filters)

Parameters

mixed $filters

Predicate(s) to set, as escaped SQL statements or parameterized queries

Return Value

$this

Self reference

See also

SQLConditionalExpression::addWhere

$this addWhereAny(mixed $filters)

Parameters

mixed $filters

Predicate(s) to set, as escaped SQL statements or parameterized queries

Return Value

$this

Self reference

See also

SQLConditionalExpression::addWhere

array getWhere()

Return a list of WHERE clauses used internally.

Return Value

array

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

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

bool filtersOnID()

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

Return Value

bool

bool filtersOnFK()

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

Return Value

bool

SQLDelete toDelete()

Generates an SQLDelete object using the currently specified parameters

Return Value

SQLDelete

SQLSelect toSelect()

Generates an SQLSelect object using the currently specified parameters.

Return Value

SQLSelect

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

static SQLUpdate create(string $table = null, array $assignment = [], array $where = [])

Construct a new SQLUpdate object

Parameters

string $table

Table name to update (ANSI quoted)

array $assignment

List of column assignments

array $where

List of where clauses

Return Value

SQLUpdate

$this setTable(string $table)

Sets the table name to update

Parameters

string $table

Return Value

$this

Self reference

string getTable()

Gets the table name to update

Return Value

string

Name of the table

$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([ '"Object"."Title"' => 'Bob', '"Object"."Description"' => 'Bob was here' ])

// Parameterised assignments $query->addAssignments([ '"Object"."Title"' => ['?' => 'Bob'], '"Object"."Description"' => ['?' => null] ])

// Complex parameters $query->addAssignments([ '"Object"."Score"' => ['MAX(?,?)' => [1, 3]] ]);

// Assignment of literal SQL for a field. The empty array is // important to denote the zero-number paramater list $query->addAssignments([ '"Object"."Score"' => ['NOW()' => []] ]);

Parameters

array $assignments

The list of fields to assign

Return Value

$this

Self reference

$this setAssignments(array $assignments)

Sets the list of assignments to the given list

For multi-row objects this applies this to the current row.

Parameters

array $assignments

Return Value

$this

Self reference

array getAssignments()

Retrieves the list of assignments in parameterised format

For multi-row objects returns assignments for the current row.

Return Value

array

List of assignments. The key of this array will be the column to assign, and the value a parameterised array in the format ['SQL' => [parameters]];

$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"', ['?' => 'Bob']);

// Complex parameters $query->assign('"Object"."Score"', ['MAX(?,?)' => [1, 3]]);

Parameters

string $field

The field name to update

mixed $value

The value to assign to this field. This could be an array containing a parameterised SQL query of any number of parameters, or a single literal value.

Return Value

$this

Self reference

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

Parameters

string $field

The field name to update

string $sql

The SQL to use for this update. E.g. "NOW()"

Return Value

$this

Self reference

$this clear()

Clears all currently set assignment values

Return Value

$this

The self reference to this query