Command

API reference › yii › db › Command

class	yii\db\Command
Extends	yii\base\Component » yii\base\BaseObject
Implements	yii\base\Configurable
Since	2.0
Source	framework/db/Command.php

3 subclasses

• yii\db\mssql\Command
• yii\db\oci\Command
• yii\db\sqlite\Command

Command represents a SQL statement to be executed against a database.

A command object is usually created by calling Connection::createCommand(). The SQL statement it represents can be set via the $sql property.

To execute a non-query SQL (such as INSERT, DELETE, UPDATE), call execute(). To execute a SQL statement that returns a result data set (such as SELECT), use queryAll(), queryOne(), queryColumn(), queryScalar(), or query().

For example,

$users = $connection->createCommand('SELECT * FROM user')->queryAll();

Command supports SQL statement preparation and parameter binding. Call bindValue() to bind a value to a SQL parameter; Call bindParam() to bind a PHP variable to a SQL parameter. When binding a parameter, the SQL statement is automatically prepared. You may also call prepare() explicitly to prepare a SQL statement.

Command also supports building SQL statements by providing methods such as insert(), update(), etc. For example, the following code will create and execute an INSERT SQL statement:

$connection->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

To build SELECT SQL statements, please use Query instead.

For more details and usage information on Command, see the guide article on Database Access Objects.

Properties

Property	Type	Description	Defined by
$behaviors read-only	array<yii\base\Behavior<static>>	List of behaviors attached to this component.	yii\base\Component
$db	Connection	The DB connection that this command is associated with Default: null.	Command
$fetchMode	int	The default fetch mode for this command. Default: \PDO::FETCH_ASSOC.	Command
$params	array	The parameters (name => value) that are bound to the current PDO statement. Default: [].	Command
$pdoStatement	PDOStatement|null	The PDOStatement object that this command is associated with Default: null.	Command
$queryCacheDependency	yii\caching\Dependency	The dependency to be associated with the cached query result for this command Default: null.	Command
$queryCacheDuration	int	The default number of seconds that query results can remain valid in cache. Default: null.	Command
$rawSql	string	The raw SQL with parameter values inserted into the corresponding placeholders in $sql.	Command
$sql	string	The SQL statement to be executed.	Command
$pendingParams protected	array	Pending parameters to be bound to the current PDO statement. Default: []. Available since 2.0.33.	Command

Methods

Method	Description	Defined by
__construct()	Constructor.	yii\base\BaseObject
__call()	Calls the named method which is not a class method.	yii\base\Component
__clone()	This method is called after the object is created by cloning an existing one.	yii\base\Component
__get()	Returns the value of a component property.	yii\base\Component
__isset()	Checks if a property is set, i.e. defined and not null.	yii\base\Component
__set()	Sets the value of a component property.	yii\base\Component
__unset()	Sets a component property to be null.	yii\base\Component
addCheck()	Creates a SQL command for adding a check constraint to an existing table.	Command
addColumn()	Creates a SQL command for adding a new DB column.	Command
addCommentOnColumn()	Builds a SQL command for adding comment to column.	Command
addCommentOnTable()	Builds a SQL command for adding comment to table.	Command
addDefaultValue()	Creates a SQL command for adding a default value constraint to an existing table.	Command
addForeignKey()	Creates a SQL command for adding a foreign key constraint to an existing table.	Command
addPrimaryKey()	Creates a SQL command for adding a primary key constraint to an existing table.	Command
addUnique()	Creates a SQL command for adding an unique constraint to an existing table.	Command
alterColumn()	Creates a SQL command for changing the definition of a column.	Command
attachBehavior()	Attaches a behavior to this component.	yii\base\Component
attachBehaviors()	Attaches a list of behaviors to the component.	yii\base\Component
batchInsert()	Creates a batch INSERT command.	Command
behaviors()	Returns a list of behaviors that this component should behave as.	yii\base\Component
bindParam()	Binds a parameter to the SQL statement to be executed.	Command
bindValue()	Binds a value to a parameter.	Command
bindValues()	Binds a list of values to the corresponding parameters.	Command
cache()	Enables query cache for this command.	Command
cancel()	Cancels the execution of the SQL statement.	Command
canGetProperty()	Returns a value indicating whether a property can be read.	yii\base\Component
canSetProperty()	Returns a value indicating whether a property can be set.	yii\base\Component
checkIntegrity()	Builds a SQL command for enabling or disabling integrity check.	Command
className() static	Returns the fully qualified name of this class.	yii\base\BaseObject
createIndex()	Creates a SQL command for creating a new index.	Command
createTable()	Creates a SQL command for creating a new DB table.	Command
createView()	Creates a SQL View.	Command
delete()	Creates a DELETE command.	Command
detachBehavior()	Detaches a behavior from the component.	yii\base\Component
detachBehaviors()	Detaches all behaviors from the component.	yii\base\Component
dropCheck()	Creates a SQL command for dropping a check constraint.	Command
dropColumn()	Creates a SQL command for dropping a DB column.	Command
dropCommentFromColumn()	Builds a SQL command for dropping comment from column.	Command
dropCommentFromTable()	Builds a SQL command for dropping comment from table.	Command
dropDefaultValue()	Creates a SQL command for dropping a default value constraint.	Command
dropForeignKey()	Creates a SQL command for dropping a foreign key constraint.	Command
dropIndex()	Creates a SQL command for dropping an index.	Command
dropPrimaryKey()	Creates a SQL command for removing a primary key constraint to an existing table.	Command
dropTable()	Creates a SQL command for dropping a DB table.	Command
dropUnique()	Creates a SQL command for dropping an unique constraint.	Command
dropView()	Drops a SQL View.	Command
ensureBehaviors()	Makes sure that the behaviors declared in behaviors() are attached to this component.	yii\base\Component
execute()	Executes the SQL statement.	Command
executeResetSequence()	Executes a db command resetting the sequence value of a table’s primary key.	Command
getBehavior()	Returns the named behavior object.	yii\base\Component
getBehaviors()	Returns all behaviors attached to this component.	yii\base\Component
getRawSql()	Returns the raw SQL by inserting parameter values into the corresponding placeholders in $sql.	Command
getSql()	Returns the SQL statement for this command.	Command
hasEventHandlers()	Returns a value indicating whether there is any handler attached to the named event.	yii\base\Component
hasMethod()	Returns a value indicating whether a method is defined.	yii\base\Component
hasProperty()	Returns a value indicating whether a property is defined for this component.	yii\base\Component
init()	Initializes the object.	yii\base\BaseObject
insert()	Creates an INSERT command.	Command
noCache()	Disables query cache for this command.	Command
off()	Detaches an existing event handler from this component.	yii\base\Component
on()	Attaches an event handler to an event.	yii\base\Component
prepare()	Prepares the SQL statement to be executed.	Command
query()	Executes the SQL statement and returns query result.	Command
queryAll()	Executes the SQL statement and returns ALL rows at once.	Command
queryColumn()	Executes the SQL statement and returns the first column of the result.	Command
queryOne()	Executes the SQL statement and returns the first row of the result.	Command
queryScalar()	Executes the SQL statement and returns the value of the first column in the first row of data.	Command
renameColumn()	Creates a SQL command for renaming a column.	Command
renameTable()	Creates a SQL command for renaming a DB table.	Command
resetSequence()	Creates a SQL command for resetting the sequence value of a table’s primary key.	Command
setRawSql()	Specifies the SQL statement to be executed. The SQL statement will not be modified in any way.	Command
setSql()	Specifies the SQL statement to be executed. The SQL statement will be quoted using Connection::quoteSql().	Command
trigger()	Triggers an event.	yii\base\Component
truncateTable()	Creates a SQL command for truncating a DB table.	Command
update()	Creates an UPDATE command.	Command
upsert()	Creates a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.	Command
bindPendingParams() protected	Binds pending parameters that were registered via bindValue() and bindValues().	Command
getCacheKey() protected	Returns the cache key for the query.	Command
internalExecute() protected	Executes a prepared statement.	Command
logQuery() protected	Logs the current database query if query logging is enabled and returns the profiling token if profiling is enabled.	Command
queryInternal() protected	Performs the actual DB query of a SQL statement.	Command
refreshTableSchema() protected	Refreshes table schema, which was marked by requireTableSchemaRefresh().	Command
requireTableSchemaRefresh() protected	Marks a specified table schema to be refreshed after command execution.	Command
requireTransaction() protected	Marks the command to be executed in transaction.	Command
reset() protected	Resets command properties to their initial state.	Command
setRetryHandler() protected	Sets a callable (e.g. anonymous function) that is called when Exception is thrown when executing the command. The signature of the callable should be:	Command

Property details

$params

public array $params = []

The parameters (name => value) that are bound to the current PDO statement.

This property is maintained by methods such as bindValue(). It is mainly provided for logging purpose and is used to generate $rawSql. Do not modify it directly.

$queryCacheDuration

public int $queryCacheDuration = null

The default number of seconds that query results can remain valid in cache.

Use 0 to indicate that the cached data will never expire. And use a negative number to indicate query cache should not be used.

Method details

addCheck()

public function addCheck(string $name, string $table, string $expression): $this

Creates a SQL command for adding a check constraint to an existing table.

Parameters

Parameter	Type	Description
$name	string	The name of the check constraint. The name will be properly quoted by the method.
$table	string	The table that the check constraint will be added to. The name will be properly quoted by the method.
$expression	string	The SQL of the CHECK constraint.

Returns

$this: The command object itself.

Since

2.0.13

addColumn()

public function addColumn(string $table, string $column, string $type): $this

Creates a SQL command for adding a new DB column.

Parameters

Parameter	Type	Description
$table	string	The table that the new column will be added to. The table name will be properly quoted by the method.
$column	string	The name of the new column. The name will be properly quoted by the method.
$type	string	The column type. QueryBuilder::getColumnType() will be called to convert the given column type to the physical one. For example, string will be converted as varchar(255), and string not null becomes varchar(255) not null.

Returns

$this: The command object itself

addCommentOnColumn()

public function addCommentOnColumn(string $table, string $column, string $comment): $this

Builds a SQL command for adding comment to column.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be commented. The table name will be properly quoted by the method.
$column	string	The name of the column to be commented. The column name will be properly quoted by the method.
$comment	string	The text of the comment to be added. The comment will be properly quoted by the method.

Returns

$this: The command object itself

Since

2.0.8

addCommentOnTable()

public function addCommentOnTable(string $table, string $comment): $this

Builds a SQL command for adding comment to table.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be commented. The table name will be properly quoted by the method.
$comment	string	The text of the comment to be added. The comment will be properly quoted by the method.

Returns

$this: The command object itself

Since

2.0.8

addDefaultValue()

public function addDefaultValue(string $name, string $table, string $column, mixed $value): $this

Creates a SQL command for adding a default value constraint to an existing table.

Parameters

Parameter	Type	Description
$name	string	The name of the default value constraint. The name will be properly quoted by the method.
$table	string	The table that the default value constraint will be added to. The name will be properly quoted by the method.
$column	string	The name of the column to that the constraint will be added on. The name will be properly quoted by the method.
$value	mixed	Default value.

Returns

$this: The command object itself.

Since

2.0.13

addForeignKey()

public function addForeignKey(string $name, string $table, string|array $columns, string $refTable, string|array $refColumns, string|null $delete = null, string|null $update = null): $this

Creates a SQL command for adding a foreign key constraint to an existing table.

The method will properly quote the table and column names.

Parameters

Parameter	Type	Description
$name	string	The name of the foreign key constraint.
$table	string	The table that the foreign key constraint will be added to.
$columns	string|array	The name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas.
$refTable	string	The table that the foreign key references to.
$refColumns	string|array	The name of the column that the foreign key references to. If there are multiple columns, separate them with commas.
$delete	string|null	The ON DELETE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL
$update	string|null	The ON UPDATE option. Most DBMS support these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL

Returns

$this: The command object itself

addPrimaryKey()

public function addPrimaryKey(string $name, string $table, string|array $columns): $this

Creates a SQL command for adding a primary key constraint to an existing table.

The method will properly quote the table and column names.

Parameters

Parameter	Type	Description
$name	string	The name of the primary key constraint.
$table	string	The table that the primary key constraint will be added to.
$columns	string|array	Comma separated string or array of columns that the primary key will consist of.

Returns

$this: The command object itself.

addUnique()

public function addUnique(string $name, string $table, string|array $columns): $this

Creates a SQL command for adding an unique constraint to an existing table.

Parameters

Parameter	Type	Description
$name	string	The name of the unique constraint. The name will be properly quoted by the method.
$table	string	The table that the unique constraint will be added to. The name will be properly quoted by the method.
$columns	string|array	The name of the column to that the constraint will be added on. If there are multiple columns, separate them with commas. The name will be properly quoted by the method.

Returns

$this: The command object itself.

Since

2.0.13

alterColumn()

public function alterColumn(string $table, string $column, string $type): $this

Creates a SQL command for changing the definition of a column.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be changed. The table name will be properly quoted by the method.
$column	string	The name of the column to be changed. The name will be properly quoted by the method.
$type	string	The column type. QueryBuilder::getColumnType() will be called to convert the give column type to the physical one. For example, string will be converted as varchar(255), and string not null becomes varchar(255) not null.

Returns

$this: The command object itself

batchInsert()

public function batchInsert(string $table, array $columns, array|Generator $rows): $this

Creates a batch INSERT command.

For example,

$connection->createCommand()->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
])->execute();

The method will properly escape the column names, and quote the values to be inserted.

Note that the values in each row must match the corresponding column names.

Also note that the created command is not executed until execute() is called.

Parameters

Parameter	Type	Description
$table	string	The table that new rows will be inserted into.
$columns	array	The column names
$rows	array|Generator	The rows to be batch inserted into the table

Returns

$this: The command object itself

bindParam()

public function bindParam(string|int $name, mixed &$value, int|null $dataType = null, int|null $length = null, mixed $driverOptions = null): $this

Binds a parameter to the SQL statement to be executed.

Parameters

Parameter	Type	Description
$name	string|int	Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
$value	mixed	The PHP variable to bind to the SQL statement parameter (passed by reference)
$dataType	int|null	SQL data type of the parameter. If null, the type is determined by the PHP type of the value.
$length	int|null	Length of the data type
$driverOptions	mixed	The driver-specific options

Returns

$this: The current command being executed

See also

• https://www.php.net/manual/en/function.PDOStatement-bindParam.php

bindValue()

public function bindValue(string|int $name, mixed $value, int|null $dataType = null): $this

Binds a value to a parameter.

Parameters

Parameter	Type	Description
$name	string|int	Parameter identifier. For a prepared statement using named placeholders, this will be a parameter name of the form :name. For a prepared statement using question mark placeholders, this will be the 1-indexed position of the parameter.
$value	mixed	The value to bind to the parameter
$dataType	int|null	SQL data type of the parameter. If null, the type is determined by the PHP type of the value.

Returns

$this: The current command being executed

See also

• https://www.php.net/manual/en/function.PDOStatement-bindValue.php

bindValues()

public function bindValues(array $values): $this

Binds a list of values to the corresponding parameters.

This is similar to bindValue() except that it binds multiple values at a time. Note that the SQL data type of each value is determined by its PHP type.

Parameters

Parameter	Type	Description
$values	array	The values to be bound. This must be given in terms of an associative array with array keys being the parameter names, and array values the corresponding parameter values, e.g. [':name' => 'John', ':age' => 25]. By default, the PDO type of each value is determined by its PHP type. You may explicitly specify the PDO type by using a PdoValue class: new PdoValue(value, type), e.g. [':name' => 'John', ':profile' => new PdoValue($profile, \PDO::PARAM_LOB)].

Returns

$this: The current command being executed

cache()

public function cache(int|null $duration = null, yii\caching\Dependency|null $dependency = null): $this

Enables query cache for this command.

Parameters

Parameter	Type	Description
$duration	int|null	The number of seconds that query result of this command can remain valid in the cache. If this is not set, the value of Connection::$queryCacheDuration will be used instead. Use 0 to indicate that the cached data will never expire.
$dependency	yii\caching\Dependency|null	The cache dependency associated with the cached query result.

Returns

$this: The command object itself

cancel()

public function cancel(): mixed

Cancels the execution of the SQL statement.

This method mainly sets $pdoStatement to be null.

Returns

mixed

checkIntegrity()

public function checkIntegrity(bool $check = true, string $schema = '', string $table = ''): $this

Builds a SQL command for enabling or disabling integrity check.

Parameters

Parameter	Type	Description
$check	bool	Whether to turn on or off the integrity check.
$schema	string	The schema name of the tables. Defaults to empty string, meaning the current or default schema.
$table	string	The table name.

Returns

$this: The command object itself

Throws

• yii\base\NotSupportedException: if this is not supported by the underlying DBMS

createIndex()

public function createIndex(string $name, string $table, string|array $columns, bool $unique = false): $this

Creates a SQL command for creating a new index.

Parameters

Parameter	Type	Description
$name	string	The name of the index. The name will be properly quoted by the method.
$table	string	The table that the new index will be created for. The table name will be properly quoted by the method.
$columns	string|array	The column(s) that should be included in the index. If there are multiple columns, please separate them by commas. The column names will be properly quoted by the method.
$unique	bool	Whether to add UNIQUE constraint on the created index.

Returns

$this: The command object itself

createTable()

public function createTable(string $table, array $columns, string|null $options = null): $this

Creates a SQL command for creating a new DB table.

The columns in the new table should be specified as name-definition pairs (e.g. ‘name’ => ‘string’), where name stands for a column name which will be properly quoted by the method, and definition stands for the column type which must contain an abstract DB type.

The method QueryBuilder::getColumnType() will be called to convert the abstract column types to physical ones. For example, string will be converted as varchar(255), and string not null becomes varchar(255) not null.

If a column is specified with definition only (e.g. ‘PRIMARY KEY (name, type)’), it will be directly inserted into the generated SQL.

Example usage:

Yii::$app->db->createCommand()->createTable('post', [
    'id' => 'pk',
    'title' => 'string',
    'text' => 'text',
    'column_name double precision null default null',
]);

Parameters

Parameter	Type	Description
$table	string	The name of the table to be created. The name will be properly quoted by the method.
$columns	array	The columns (name => definition) in the new table.
$options	string|null	Additional SQL fragment that will be appended to the generated SQL.

Returns

$this: The command object itself

createView()

public function createView(string $viewName, string|yii\db\Query $subquery): $this

Creates a SQL View.

Parameters

Parameter	Type	Description
$viewName	string	The name of the view to be created.
$subquery	string|Query	The select statement which defines the view. This can be either a string or a Query object.

Returns

$this: The command object itself.

Since

2.0.14

delete()

public function delete(string $table, string|array $condition = '', array $params = []): $this

Creates a DELETE command.

For example,

$connection->createCommand()->delete('user', 'status = 0')->execute();

or with using parameter binding for the condition:

$status = 0;
$connection->createCommand()->delete('user', 'status = :status', [':status' => $status])->execute();

The method will properly escape the table and column names.

Note that the created command is not executed until execute() is called.

Parameters

Parameter	Type	Description
$table	string	The table where the data will be deleted from.
$condition	string|array	The condition that will be put in the WHERE part. Please refer to Query::where() on how to specify condition.
$params	array	The parameters to be bound to the command

Returns

$this: The command object itself

dropCheck()

public function dropCheck(string $name, string $table): $this

Creates a SQL command for dropping a check constraint.

Parameters

Parameter	Type	Description
$name	string	The name of the check constraint to be dropped. The name will be properly quoted by the method.
$table	string	The table whose check constraint is to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself.

Since

2.0.13

dropColumn()

public function dropColumn(string $table, string $column): $this

Creates a SQL command for dropping a DB column.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be dropped. The name will be properly quoted by the method.
$column	string	The name of the column to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself

dropCommentFromColumn()

public function dropCommentFromColumn(string $table, string $column): $this

Builds a SQL command for dropping comment from column.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be commented. The table name will be properly quoted by the method.
$column	string	The name of the column to be commented. The column name will be properly quoted by the method.

Returns

$this: The command object itself

Since

2.0.8

dropCommentFromTable()

public function dropCommentFromTable(string $table): $this

Builds a SQL command for dropping comment from table.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be commented. The table name will be properly quoted by the method.

Returns

$this: The command object itself

Since

2.0.8

dropDefaultValue()

public function dropDefaultValue(string $name, string $table): $this

Creates a SQL command for dropping a default value constraint.

Parameters

Parameter	Type	Description
$name	string	The name of the default value constraint to be dropped. The name will be properly quoted by the method.
$table	string	The table whose default value constraint is to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself.

Since

2.0.13

dropForeignKey()

public function dropForeignKey(string $name, string $table): $this

Creates a SQL command for dropping a foreign key constraint.

Parameters

Parameter	Type	Description
$name	string	The name of the foreign key constraint to be dropped. The name will be properly quoted by the method.
$table	string	The table whose foreign is to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself

dropIndex()

public function dropIndex(string $name, string $table): $this

Creates a SQL command for dropping an index.

Parameters

Parameter	Type	Description
$name	string	The name of the index to be dropped. The name will be properly quoted by the method.
$table	string	The table whose index is to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself

dropPrimaryKey()

public function dropPrimaryKey(string $name, string $table): $this

Creates a SQL command for removing a primary key constraint to an existing table.

Parameters

Parameter	Type	Description
$name	string	The name of the primary key constraint to be removed.
$table	string	The table that the primary key constraint will be removed from.

Returns

$this: The command object itself

dropTable()

public function dropTable(string $table): $this

Creates a SQL command for dropping a DB table.

Parameters

Parameter	Type	Description
$table	string	The table to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself

dropUnique()

public function dropUnique(string $name, string $table): $this

Creates a SQL command for dropping an unique constraint.

Parameters

Parameter	Type	Description
$name	string	The name of the unique constraint to be dropped. The name will be properly quoted by the method.
$table	string	The table whose unique constraint is to be dropped. The name will be properly quoted by the method.

Returns

$this: The command object itself.

Since

2.0.13

dropView()

public function dropView(string $viewName): $this

Drops a SQL View.

Parameters

Parameter	Type	Description
$viewName	string	The name of the view to be dropped.

Returns

$this: The command object itself.

Since

2.0.14

execute()

public function execute(): int

Executes the SQL statement.

This method should only be used for executing non-query SQL statement, such as INSERT, DELETE, UPDATE SQLs. No result set will be returned.

Returns

int: Number of rows affected by the execution.

Throws

• Exception: execution failed

executeResetSequence()

public function executeResetSequence(string $table, int|null $value = null): mixed

Executes a db command resetting the sequence value of a table’s primary key.

Reason for execute is that some databases (Oracle) need several queries to do so.

The sequence is reset such that the primary key of the next new row inserted will have the specified value or the maximum existing value +1.

Parameters

Parameter	Type	Description
$table	string	The name of the table whose primary key sequence is reset.
$value	int|null	The integer value for the primary key of the next new row inserted. If this is not set, the next new row’s primary key will have the maximum existing value +1.

Returns

mixed

Throws

• yii\base\NotSupportedException: if this is not supported by the underlying DBMS.

Since

2.0.16

getRawSql()

public function getRawSql(): string

Returns the raw SQL by inserting parameter values into the corresponding placeholders in $sql.

Note that the return value of this method should mainly be used for logging purpose. It is likely that this method returns an invalid SQL due to improper replacement of parameter placeholders.

Returns

string: The raw SQL with parameter values inserted into the corresponding placeholders in $sql.

getSql()

public function getSql(): string

Returns the SQL statement for this command.

Returns

string: The SQL statement to be executed

insert()

public function insert(string $table, array|yii\db\Query $columns): $this

Creates an INSERT command.

For example,

$connection->createCommand()->insert('user', [
    'name' => 'Sam',
    'age' => 30,
])->execute();

The method will properly escape the column names, and bind the values to be inserted.

Note that the created command is not executed until execute() is called.

Parameters

Parameter	Type	Description
$table	string	The table that new rows will be inserted into.
$columns	array|Query	The column data (name => value) to be inserted into the table or instance of Query to perform INSERT INTO … SELECT SQL statement. Passing of Query is available since version 2.0.11.

Returns

$this: The command object itself

noCache()

public function noCache(): $this

Disables query cache for this command.

Returns

$this: The command object itself

prepare()

public function prepare(bool|null $forRead = null): mixed

Prepares the SQL statement to be executed.

For complex SQL statement that is to be executed multiple times, this may improve performance. For SQL statement with binding parameters, this method is invoked automatically.

Parameters

Parameter	Type	Description
$forRead	bool|null	Whether this method is called for a read query. If null, it means the SQL statement should be used to determine whether it is for read or write.

Returns

mixed

Throws

• Exception: if there is any DB error

query()

public function query(): yii\db\DataReader

Executes the SQL statement and returns query result.

This method is for executing a SQL query that returns result set, such as SELECT.

Returns

DataReader: The reader object for fetching the query result

Throws

• Exception: execution failed

queryAll()

public function queryAll(int|null $fetchMode = null): array

Executes the SQL statement and returns ALL rows at once.

Parameters

Parameter	Type	Description
$fetchMode	int|null	The result fetch mode. Please refer to PHP manual for valid fetch modes. If this parameter is null, the value set in $fetchMode will be used.

Returns

array: All rows of the query result. Each array element is an array representing a row of data. An empty array is returned if the query results in nothing.

Throws

• Exception: execution failed

queryColumn()

public function queryColumn(): array

Executes the SQL statement and returns the first column of the result.

This method is best used when only the first column of result (i.e. the first element in each row) is needed for a query.

Returns

array: The first column of the query result. Empty array is returned if the query results in nothing.

Throws

• Exception: execution failed

queryOne()

public function queryOne(int|null $fetchMode = null): array|false

Executes the SQL statement and returns the first row of the result.

This method is best used when only the first row of result is needed for a query.

Parameters

Parameter	Type	Description
$fetchMode	int|null	The result fetch mode. Please refer to PHP manual for valid fetch modes. If this parameter is null, the value set in $fetchMode will be used.

Returns

array|false: The first row (in terms of an array) of the query result. False is returned if the query results in nothing.

Throws

• Exception: execution failed

queryScalar()

public function queryScalar(): string|int|null|false

Executes the SQL statement and returns the value of the first column in the first row of data.

This method is best used when only a single value is needed for a query.

Returns

string|int|null|false: The value of the first column in the first row of the query result. False is returned if there is no value.

Throws

• Exception: execution failed

renameColumn()

public function renameColumn(string $table, string $oldName, string $newName): $this

Creates a SQL command for renaming a column.

Parameters

Parameter	Type	Description
$table	string	The table whose column is to be renamed. The name will be properly quoted by the method.
$oldName	string	The old name of the column. The name will be properly quoted by the method.
$newName	string	The new name of the column. The name will be properly quoted by the method.

Returns

$this: The command object itself

renameTable()

public function renameTable(string $table, string $newName): $this

Creates a SQL command for renaming a DB table.

Parameters

Parameter	Type	Description
$table	string	The table to be renamed. The name will be properly quoted by the method.
$newName	string	The new table name. The name will be properly quoted by the method.

Returns

$this: The command object itself

resetSequence()

public function resetSequence(string $table, int|null $value = null): $this

Creates a SQL command for resetting the sequence value of a table’s primary key.

The sequence will be reset such that the primary key of the next new row inserted will have the specified value or the maximum existing value +1.

Parameters

Parameter	Type	Description
$table	string	The name of the table whose primary key sequence will be reset.
$value	int|null	The integer value for the primary key of the next new row inserted. If this is not set, the next new row’s primary key will have the maximum existing value +1.

Returns

$this: The command object itself.

Throws

• yii\base\NotSupportedException: if this is not supported by the underlying DBMS.

setRawSql()

public function setRawSql(string $sql): $this

Specifies the SQL statement to be executed. The SQL statement will not be modified in any way.

The previous SQL (if any) will be discarded, and $params will be cleared as well. See reset() for details.

Parameters

Parameter	Type	Description
$sql	string	The SQL statement to be set.

Returns

$this: This command instance

Since

2.0.13

See also

• reset()
• cancel()

setSql()

public function setSql(string $sql): $this

Specifies the SQL statement to be executed. The SQL statement will be quoted using Connection::quoteSql().

The previous SQL (if any) will be discarded, and $params will be cleared as well. See reset() for details.

Parameters

Parameter	Type	Description
$sql	string	The SQL statement to be set.

Returns

$this: This command instance

See also

• reset()
• cancel()

truncateTable()

public function truncateTable(string $table): $this

Creates a SQL command for truncating a DB table.

Parameters

Parameter	Type	Description
$table	string	The table to be truncated. The name will be properly quoted by the method.

Returns

$this: The command object itself

update()

public function update(string $table, array $columns, string|array $condition = '', array $params = []): $this

Creates an UPDATE command.

For example,

$connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();

or with using parameter binding for the condition:

$minAge = 30;
$connection->createCommand()->update('user', ['status' => 1], 'age > :minAge', [':minAge' => $minAge])->execute();

The method will properly escape the column names and bind the values to be updated.

Note that the created command is not executed until execute() is called.

Parameters

Parameter	Type	Description
$table	string	The table to be updated.
$columns	array	The column data (name => value) to be updated.
$condition	string|array	The condition that will be put in the WHERE part. Please refer to Query::where() on how to specify condition.
$params	array	The parameters to be bound to the command

Returns

$this: The command object itself

upsert()

public function upsert(string $table, array|yii\db\Query $insertColumns, array|bool $updateColumns = true, array $params = []): $this

Creates a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.

For example,

$sql = $queryBuilder->upsert('pages', [
    'name' => 'Front page',
    'url' => 'https://example.com/', // url is unique
    'visits' => 0,
], [
    'visits' => new \yii\db\Expression('visits + 1'),
], $params);

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
$table	string	The table that new rows will be inserted into/updated in.
$insertColumns	array|Query	The column data (name => value) to be inserted into the table or instance of Query to perform INSERT INTO ... SELECT SQL statement.
$updateColumns	array|bool	The column data (name => value) to be updated if they already exist. If true is passed, the column data will be updated to match the insert column data. If false is passed, no update will be performed if the column data already exists.
$params	array	The parameters to be bound to the command.

Returns

$this: The command object itself.

Since

2.0.14

bindPendingParams()

protected function bindPendingParams(): mixed

Binds pending parameters that were registered via bindValue() and bindValues().

Note that this method requires an active $pdoStatement.

Returns

mixed

getCacheKey()

protected function getCacheKey(string $method, int $fetchMode, mixed $rawSql): array

Returns the cache key for the query.

Parameters

Parameter	Type	Description
$method	string	Method of PDOStatement to be called
$fetchMode	int	The result fetch mode. Please refer to PHP manual for valid fetch modes.
$rawSql	mixed

Returns

array: The cache key

Since

2.0.16

internalExecute()

protected function internalExecute(string|null $rawSql): mixed

Executes a prepared statement.

It’s a wrapper around PDOStatement::execute() to support transactions and retry handlers.

Parameters

Parameter	Type	Description
$rawSql	string|null	The rawSql if it has been created.

Returns

mixed

Throws

• Exception: if execution failed.

Since

2.0.14

logQuery()

protected function logQuery(string $category): array

Logs the current database query if query logging is enabled and returns the profiling token if profiling is enabled.

Parameters

Parameter	Type	Description
$category	string	The log category.

Returns

array: Array of two elements, the first is boolean of whether profiling is enabled or not. The second is the rawSql if it has been created.

queryInternal()

protected function queryInternal(string $method, int|null $fetchMode = null): mixed

Performs the actual DB query of a SQL statement.

Parameters

Parameter	Type	Description
$method	string	Method of PDOStatement to be called
$fetchMode	int|null	The result fetch mode. Please refer to PHP manual for valid fetch modes. If this parameter is null, the value set in $fetchMode will be used.

Returns

mixed: The method execution result

Throws

• Exception: if the query causes any problem

refreshTableSchema()

protected function refreshTableSchema(): mixed

Refreshes table schema, which was marked by requireTableSchemaRefresh().

Returns

mixed

Since

2.0.6

requireTableSchemaRefresh()

protected function requireTableSchemaRefresh(string $name): $this

Marks a specified table schema to be refreshed after command execution.

Parameters

Parameter	Type	Description
$name	string	Name of the table, which schema should be refreshed.

Returns

$this: This command instance

Since

2.0.6

requireTransaction()

protected function requireTransaction(string|null $isolationLevel = null): $this

Marks the command to be executed in transaction.

Parameters

Parameter	Type	Description
$isolationLevel	string|null	The isolation level to use for this transaction. See Transaction::begin() for details.

Returns

$this: This command instance.

Since

2.0.14

reset()

protected function reset(): mixed

Resets command properties to their initial state.

Returns

mixed

Since

2.0.13

setRetryHandler()

protected function setRetryHandler(callable $handler): $this

Sets a callable (e.g. anonymous function) that is called when Exception is thrown when executing the command. The signature of the callable should be:

function (\yii\db\Exception $e, $attempt)
{
    // return true or false (whether to retry the command or rethrow $e)
}

The callable will receive a database exception thrown and a current attempt (to execute the command) number starting from 1.

Parameters

Parameter	Type	Description
$handler	callable	A PHP callback to handle database exceptions.

Returns

$this: This command instance.

Since

2.0.14