QueryBuilder

API reference › yii › db › QueryBuilder

class	`yii\db\QueryBuilder`
Extends	`yii\base\BaseObject`
Implements	`yii\base\Configurable`
Since	2.0
Source	`framework/db/QueryBuilder.php`

5 subclasses

QueryBuilder builds a SELECT SQL statement based on the specification given as a Query object.

SQL statements are created from Query objects using the build()-method.

QueryBuilder is also used by Command to build SQL statements such as INSERT, UPDATE, DELETE, CREATE TABLE.

For more details and usage information on QueryBuilder, see the guide article on query builders.

Properties

Property	Type	Description	Defined by
`$db`	`Connection`	The database connection. Default: `null`.	`QueryBuilder`
`$separator`	`string`	The separator between different fragments of a SQL statement. Default: `' '`.	`QueryBuilder`
`$typeMap`	`array`	The abstract column types mapped to physical column types. Default: `[]`.	`QueryBuilder`
`$conditionClasses` protected	`array`	Map of condition aliases to condition classes. Default: `[]`. Available since 2.0.14.	`QueryBuilder`
`$expressionBuilders` protected	`string[]\|ExpressionBuilderInterface[]`	Maps expression class to expression builder class. Default: `[]`. Available since 2.0.14.	`QueryBuilder`

Methods

Method	Description	Defined by
`__construct()`	Constructor.	`QueryBuilder`
`__call()`	Calls the named method which is not a class method.	`yii\base\BaseObject`
`__get()`	Returns the value of an object property.	`yii\base\BaseObject`
`__isset()`	Checks if a property is set, i.e. defined and not null.	`yii\base\BaseObject`
`__set()`	Sets value of an object property.	`yii\base\BaseObject`
`__unset()`	Sets an object property to null.	`yii\base\BaseObject`
`addCheck()`	Creates a SQL command for adding a check constraint to an existing table.	`QueryBuilder`
`addColumn()`	Builds a SQL statement for adding a new DB column.	`QueryBuilder`
`addCommentOnColumn()`	Builds a SQL command for adding comment to column.	`QueryBuilder`
`addCommentOnTable()`	Builds a SQL command for adding comment to table.	`QueryBuilder`
`addDefaultValue()`	Creates a SQL command for adding a default value constraint to an existing table.	`QueryBuilder`
`addForeignKey()`	Builds a SQL statement for adding a foreign key constraint to an existing table.	`QueryBuilder`
`addPrimaryKey()`	Builds a SQL statement for adding a primary key constraint to an existing table.	`QueryBuilder`
`addUnique()`	Creates a SQL command for adding an unique constraint to an existing table.	`QueryBuilder`
`alterColumn()`	Builds a SQL statement for changing the definition of a column.	`QueryBuilder`
`batchInsert()`	Generates a batch INSERT SQL statement.	`QueryBuilder`
`bindParam()`	Helper method to add $value to $params array using `PARAM_PREFIX`.	`QueryBuilder`
`build()`	Generates a SELECT SQL statement from a `Query` object.	`QueryBuilder`
`buildColumns()`	Processes columns and properly quotes them if necessary.	`QueryBuilder`
`buildCondition()`	Parses the condition specification and generates the corresponding SQL expression.	`QueryBuilder`
`buildExpression()`	Builds given $expression	`QueryBuilder`
`buildFrom()`		`QueryBuilder`
`buildGroupBy()`		`QueryBuilder`
`buildHaving()`		`QueryBuilder`
`buildJoin()`		`QueryBuilder`
`buildLimit()`		`QueryBuilder`
`buildOrderBy()`		`QueryBuilder`
`buildOrderByAndLimit()`	Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.	`QueryBuilder`
`buildSelect()`		`QueryBuilder`
`buildUnion()`		`QueryBuilder`
`buildUnionOrderByAndLimit()`	Builds the global ORDER BY and LIMIT/OFFSET clauses for a UNION query.	`QueryBuilder`
`buildWhere()`		`QueryBuilder`
`buildWithQueries()`		`QueryBuilder`
`canGetProperty()`	Returns a value indicating whether a property can be read.	`yii\base\BaseObject`
`canSetProperty()`	Returns a value indicating whether a property can be set.	`yii\base\BaseObject`
`checkIntegrity()`	Builds a SQL statement for enabling or disabling integrity check.	`QueryBuilder`
`className()` static	Returns the fully qualified name of this class.	`yii\base\BaseObject`
`createConditionFromArray()`	Transforms $condition defined in array format (as described in `Query::where()` to instance of ConditionInterface according to `$conditionClasses` map.	`QueryBuilder`
`createIndex()`	Builds a SQL statement for creating a new index.	`QueryBuilder`
`createTable()`	Builds a SQL statement for creating a new DB table.	`QueryBuilder`
`createView()`	Creates a SQL View.	`QueryBuilder`
`delete()`	Creates a DELETE SQL statement.	`QueryBuilder`
`dropCheck()`	Creates a SQL command for dropping a check constraint.	`QueryBuilder`
`dropColumn()`	Builds a SQL statement for dropping a DB column.	`QueryBuilder`
`dropCommentFromColumn()`	Builds a SQL command for adding comment to column.	`QueryBuilder`
`dropCommentFromTable()`	Builds a SQL command for adding comment to table.	`QueryBuilder`
`dropDefaultValue()`	Creates a SQL command for dropping a default value constraint.	`QueryBuilder`
`dropForeignKey()`	Builds a SQL statement for dropping a foreign key constraint.	`QueryBuilder`
`dropIndex()`	Builds a SQL statement for dropping an index.	`QueryBuilder`
`dropPrimaryKey()`	Builds a SQL statement for removing a primary key constraint to an existing table.	`QueryBuilder`
`dropTable()`	Builds a SQL statement for dropping a DB table.	`QueryBuilder`
`dropUnique()`	Creates a SQL command for dropping an unique constraint.	`QueryBuilder`
`dropView()`	Drops a SQL View.	`QueryBuilder`
`executeResetSequence()`	Execute a SQL statement for resetting the sequence value of a table’s primary key.	`QueryBuilder`
`getColumnType()`	Converts an abstract column type into a physical column type.	`QueryBuilder`
`getExpressionBuilder()`	Gets object of `ExpressionBuilderInterface` that is suitable for $expression.	`QueryBuilder`
`hasMethod()`	Returns a value indicating whether a method is defined.	`yii\base\BaseObject`
`hasProperty()`	Returns a value indicating whether a property is defined.	`yii\base\BaseObject`
`init()`	Initializes the object.	`QueryBuilder`
`insert()`	Creates an INSERT SQL statement.	`QueryBuilder`
`renameColumn()`	Builds a SQL statement for renaming a column.	`QueryBuilder`
`renameTable()`	Builds a SQL statement for renaming a DB table.	`QueryBuilder`
`resetSequence()`	Creates a SQL statement for resetting the sequence value of a table’s primary key.	`QueryBuilder`
`selectExists()`	Creates a SELECT EXISTS() SQL statement.	`QueryBuilder`
`setConditionClasses()`	Setter for `$conditionClasses` property.	`QueryBuilder`
`setExpressionBuilders()`	Setter for `$expressionBuilders` property.	`QueryBuilder`
`truncateTable()`	Builds a SQL statement for truncating a DB table.	`QueryBuilder`
`update()`	Creates an UPDATE SQL statement.	`QueryBuilder`
`upsert()`	Creates an SQL statement to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.	`QueryBuilder`
`concatUnionSql()` protected	Concatenates the first `SELECT` statement with the UNION clauses built by `buildUnion()`.	`QueryBuilder`
`defaultConditionClasses()` protected	Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See `$conditionClasses` docs for details.	`QueryBuilder`
`defaultExpressionBuilders()` protected	Contains array of default expression builders. Extend this method and override it, if you want to change default expression builders for this query builder. See `$expressionBuilders` docs for details.	`QueryBuilder`
`extractAlias()` protected	Extracts table alias if there is one or returns false	`QueryBuilder`
`hasLimit()` protected	Checks to see if the given limit is effective.	`QueryBuilder`
`hasOffset()` protected	Checks to see if the given offset is effective.	`QueryBuilder`
`prepareInsertSelectSubQuery()` protected	Prepare select-subquery and field names for INSERT INTO … SELECT SQL statement.	`QueryBuilder`
`prepareInsertValues()` protected	Prepares a `VALUES` part for an `INSERT` SQL statement.	`QueryBuilder`
`prepareSelectQuery()` protected	Performs driver-specific preparation after `Query::prepare()`.	`QueryBuilder`
`prepareUpdateSets()` protected	Prepares a `SET` parts for an `UPDATE` SQL statement.	`QueryBuilder`
`prepareUpsertColumns()` protected		`QueryBuilder`
`prepareUpsertSets()` protected	Prepares the `SET` assignments for the update branch of an upsert statement.	`QueryBuilder`

Constants

Constant	Value	Description	Defined by
`PARAM_PREFIX`	`':qp'`	The prefix for automatically generated query binding parameters.	`QueryBuilder`

Property details

$separator

public string $separator = ' '

The separator between different fragments of a SQL statement.

Defaults to an empty space. This is mainly used by build() when generating a SQL statement.

$typeMap

public array $typeMap = []

The abstract column types mapped to physical column types.

This is mainly used to support creating/modifying tables using DB-independent data type specifications. Child classes should override this property to declare supported type mappings.

$conditionClasses

protected array $conditionClasses = []

Map of condition aliases to condition classes.

For example:

return [
    'LIKE' => yii\db\condition\LikeCondition::class,
];

This property is used by createConditionFromArray() method. See default condition classes list in defaultConditionClasses() method.

In case you want to add custom conditions support, use the setConditionClasses() method.

Since

2.0.14

$expressionBuilders

protected string[]|yii\db\ExpressionBuilderInterface[] $expressionBuilders = []

Maps expression class to expression builder class.

For example:

[
   yii\db\Expression::class => yii\db\ExpressionBuilder::class
]

This property is mainly used by buildExpression() to build SQL expressions form expression objects. See default values in defaultExpressionBuilders() method.

To override existing builders or add custom, use setExpressionBuilder() method. New items will be added to the end of this array.

To find a builder, buildExpression() will check the expression class for its exact presence in this map. In case it is NOT present, the array will be iterated in reverse direction, checking whether the expression extends the class, defined in this map.

Since

2.0.14

Method details

__construct()

public function __construct(yii\db\Connection $connection, array $config = [])

Constructor.

Parameters

Parameter	Type	Description
`$connection`	`Connection`	The database connection.
`$config`	`array`	Name-value pairs that will be used to initialize the object properties

addCheck()

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

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

string: The SQL statement for adding a check constraint to an existing table.

Since

2.0.13

addColumn()

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

Builds a SQL statement 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. The `getColumnType()` method will be invoked to convert abstract column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. For example, ‘string’ will be turned into ‘varchar(255)’, while ‘string not null’ will become ‘varchar(255) not null’.

Returns

string: The SQL statement for adding a new column.

addCommentOnColumn()

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

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

string: The SQL statement for adding comment on column

Since

2.0.8

addCommentOnTable()

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

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

string: The SQL statement for adding comment on table

Since

2.0.8

addDefaultValue()

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

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

string: The SQL statement for adding a default value constraint to an existing table.

Throws

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

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): string

Builds a SQL statement 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 or use an array to represent them.
`$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 or use an array to represent them.
`$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

string: The SQL statement for adding a foreign key constraint to an existing table.

addPrimaryKey()

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

Builds a SQL statement for adding a primary key constraint to an existing table.

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

string: The SQL statement for adding a primary key constraint to an existing table.

addUnique()

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

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

string: The SQL statement for adding an unique constraint to an existing table.

Since

2.0.13

alterColumn()

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

Builds a SQL statement 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 new column type. The `getColumnType()` method will be invoked to convert abstract column type (if any) into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. For example, ‘string’ will be turned into ‘varchar(255)’, while ‘string not null’ will become ‘varchar(255) not null’.

Returns

string: The SQL statement for changing the definition of a column.

batchInsert()

public function batchInsert(string $table, array $columns, array|Generator $rows, array &$params = []): string

Generates a batch INSERT SQL statement.

For example,

$sql = $queryBuilder->batchInsert('user', ['name', 'age'], [
    ['Tom', 30],
    ['Jane', 20],
    ['Linda', 25],
]);

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

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

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
`$params`	`array`	The binding parameters. This parameter exists since 2.0.14

Returns

string: The batch INSERT SQL statement

bindParam()

public function bindParam(string|null $value, array &$params): string

Helper method to add $value to $params array using PARAM_PREFIX.

Parameters

Parameter	Type	Description
`$value`	`string\|null`
`$params`	`array`	Passed by reference

Returns

string: The placeholder name in $params array

Since

2.0.14

build()

public function build(yii\db\Query $query, array $params = []): array

Generates a SELECT SQL statement from a Query object.

Parameters

Parameter	Type	Description
`$query`	`Query`	The `Query` object from which the SQL statement will be generated.
`$params`	`array`	The parameters to be bound to the generated SQL statement. These parameters will be included in the result with the additional parameters generated during the query building process.

Returns

array: The generated SQL statement (the first array element) and the corresponding parameters to be bound to the SQL statement (the second array element). The parameters returned include those provided in $params.

buildColumns()

public function buildColumns(string|array $columns): string

Processes columns and properly quotes them if necessary.

It will join all columns into a string with comma as separators.

Parameters

Parameter	Type	Description
`$columns`	`string\|array`	The columns to be processed

Returns

string: The processing result

buildCondition()

public function buildCondition(string|array|yii\db\ExpressionInterface $condition, array &$params): string

Parses the condition specification and generates the corresponding SQL expression.

Parameters

Parameter	Type	Description
`$condition`	`string\|array\|ExpressionInterface`	The condition specification. Please refer to `Query::where()` on how to specify a condition.
`$params`	`array`	The binding parameters to be populated

Returns

string: The generated SQL expression

buildExpression()

public function buildExpression(yii\db\ExpressionInterface $expression, array &$params = []): string

Builds given $expression

Parameters

Parameter	Type	Description
`$expression`	`ExpressionInterface`	The expression to be built
`$params`	`array`	The parameters to be bound to the generated SQL statement. These parameters will be included in the result with the additional parameters generated during the expression building process.

Returns

string: The SQL statement that will not be neither quoted nor encoded before passing to DBMS

Throws

yii\base\InvalidArgumentException: when $expression building is not supported by this QueryBuilder.

Since

2.0.14

See also

buildFrom()

public function buildFrom(array $tables, array &$params): string

Parameters

Parameter	Type	Description
`$tables`	`array`
`$params`	`array`	The binding parameters to be populated

Returns

string: The FROM clause built from Query::$from.

buildGroupBy()

public function buildGroupBy(array $columns): string

Parameters

Parameter	Type	Description
`$columns`	`array`

Returns

string: The GROUP BY clause

buildHaving()

public function buildHaving(string|array $condition, array &$params): string

Parameters

Parameter	Type	Description
`$condition`	`string\|array`
`$params`	`array`	The binding parameters to be populated

Returns

string: The HAVING clause built from Query::$having.

buildJoin()

public function buildJoin(array $joins, array &$params): string

Parameters

Parameter	Type	Description
`$joins`	`array`
`$params`	`array`	The binding parameters to be populated

Returns

string: The JOIN clause built from Query::$join.

Throws

Exception: if the $joins parameter is not in proper format

buildLimit()

public function buildLimit(int $limit, int $offset): string

Parameters

Parameter	Type	Description
`$limit`	`int`
`$offset`	`int`

Returns

string: The LIMIT and OFFSET clauses

buildOrderBy()

public function buildOrderBy(array $columns): string

Parameters

Parameter	Type	Description
`$columns`	`array`

Returns

string: The ORDER BY clause built from Query::$orderBy.

buildOrderByAndLimit()

public function buildOrderByAndLimit(string $sql, array $orderBy, int $limit, int $offset): string

Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.

Parameters

Parameter	Type	Description
`$sql`	`string`	The existing SQL (without ORDER BY/LIMIT/OFFSET)
`$orderBy`	`array`	The order by columns. See `Query::orderBy()` for more details on how to specify this parameter.
`$limit`	`int`	The limit number. See `Query::limit()` for more details.
`$offset`	`int`	The offset number. See `Query::offset()` for more details.

Returns

string: The SQL completed with ORDER BY/LIMIT/OFFSET (if any)

buildSelect()

public function buildSelect(array $columns, array &$params, bool $distinct = false, string|null $selectOption = null): string

Parameters

Parameter	Type	Description
`$columns`	`array`
`$params`	`array`	The binding parameters to be populated
`$distinct`	`bool`
`$selectOption`	`string\|null`

Returns

string: The SELECT clause built from Query::$select.

buildUnion()

public function buildUnion(array $unions, array &$params): string

Parameters

Parameter	Type	Description
`$unions`	`array`
`$params`	`array`	The binding parameters to be populated

Returns

string: The UNION clause built from Query::$union.

buildUnionOrderByAndLimit()

public function buildUnionOrderByAndLimit(string $sql, array<int|string, int|yii\db\ExpressionInterface>|null $orderBy, int|yii\db\ExpressionInterface|null $limit, int|yii\db\ExpressionInterface|null $offset): string

Builds the global ORDER BY and LIMIT/OFFSET clauses for a UNION query.

Parameters

Parameter	Type	Description
`$sql`	`string`	The complete UNION SQL without global ORDER BY/LIMIT/OFFSET clauses.
`$orderBy`	`array<int\|string, int\|ExpressionInterface>\|null`	The global order by columns.
`$limit`	`int\|ExpressionInterface\|null`	The global limit.
`$offset`	`int\|ExpressionInterface\|null`	The global offset.

Returns

string: The completed UNION SQL.

Since

22.0

buildWhere()

public function buildWhere(string|array $condition, array &$params): string

Parameters

Parameter	Type	Description
`$condition`	`string\|array`
`$params`	`array`	The binding parameters to be populated

Returns

string: The WHERE clause built from Query::$where.

buildWithQueries()

public function buildWithQueries(array $withs, array &$params): string

Parameters

Parameter	Type	Description
`$withs`	`array`	Of configurations for each WITH query
`$params`	`array`	The binding parameters to be populated

Returns

string: Compiled WITH prefix of query including nested queries

Since

2.0.35

See also

Query::withQuery()

checkIntegrity()

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

Builds a SQL statement 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 of the tables. Defaults to empty string, meaning the current or default schema.
`$table`	`string`	The table name. Defaults to empty string, meaning that no table will be changed.

Returns

string: The SQL statement for checking integrity

Throws

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

createConditionFromArray()

public function createConditionFromArray(string|array $condition): yii\db\conditions\ConditionInterface

Transforms $condition defined in array format (as described in Query::where() to instance of ConditionInterface according to $conditionClasses map.

Parameters

Parameter	Type	Description
`$condition`	`string\|array`

Returns

yii\db\conditions\ConditionInterface

Since

2.0.14

See also

$conditionClasses

createIndex()

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

Builds a SQL statement 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, separate them with commas or use an array to represent them. Each column name will be properly quoted by the method, unless a parenthesis is found in the name.
`$unique`	`bool`	Whether to add UNIQUE constraint on the created index.

Returns

string: The SQL statement for creating a new index.

createTable()

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

Builds a SQL statement 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 getColumnType() method will be invoked to convert any abstract type into a physical one.

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

For example,

$sql = $queryBuilder->createTable('user', [
 'id' => 'pk',
 'name' => 'string',
 'age' => 'integer',
 'column_name double precision null default null', # definition only example
]);

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

string: The SQL statement for creating a new DB table.

createView()

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

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

string: The CREATE VIEW SQL statement.

Since

2.0.14

delete()

public function delete(string $table, array|string $condition, array &$params): string

Creates a DELETE SQL statement.

For example,

$sql = $queryBuilder->delete('user', 'status = 0');

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
`$table`	`string`	The table where the data will be deleted from.
`$condition`	`array\|string`	The condition that will be put in the WHERE part. Please refer to `Query::where()` on how to specify condition.
`$params`	`array`	The binding parameters that will be modified by this method so that they can be bound to the DB command later.

Returns

string: The DELETE SQL

dropCheck()

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

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

string: The SQL statement for dropping a check constraint.

Since

2.0.13

dropColumn()

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

Builds a SQL statement 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

string: The SQL statement for dropping a DB column.

dropCommentFromColumn()

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

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.

Returns

string: The SQL statement for adding comment on column

Since

2.0.8

dropCommentFromTable()

public function dropCommentFromTable(string $table): string

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.

Returns

string: The SQL statement for adding comment on column

Since

2.0.8

dropDefaultValue()

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

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

string: The SQL statement for dropping a default value constraint.

Throws

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

Since

2.0.13

dropForeignKey()

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

Builds a SQL statement 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

string: The SQL statement for dropping a foreign key constraint.

dropIndex()

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

Builds a SQL statement 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

string: The SQL statement for dropping an index.

dropPrimaryKey()

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

Builds a SQL statement 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

string: The SQL statement for removing a primary key constraint from an existing table.

dropTable()

public function dropTable(string $table): string

Builds a SQL statement 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

string: The SQL statement for dropping a DB table.

dropUnique()

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

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

string: The SQL statement for dropping an unique constraint.

Since

2.0.13

dropView()

public function dropView(string $viewName): string

Drops a SQL View.

Parameters

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

Returns

string: The DROP VIEW SQL statement.

Since

2.0.14

executeResetSequence()

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

Execute a SQL statement for 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

getColumnType()

public function getColumnType(string|yii\db\ColumnSchemaBuilder $type): string

Converts an abstract column type into a physical column type.

The conversion is done using the type map specified in $typeMap. The following abstract column types are supported (using MySQL as an example to explain the corresponding physical types):

pk: an auto-incremental primary key type, will be converted into “int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY”
bigpk: an auto-incremental primary key type, will be converted into “bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY”
upk: an unsigned auto-incremental primary key type, will be converted into “int(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY”
char: char type, will be converted into “char(1)”
string: string type, will be converted into “varchar(255)”
text: a long string type, will be converted into “text”
smallint: a small integer type, will be converted into “smallint(6)”
integer: integer type, will be converted into “int(11)”
bigint: a big integer type, will be converted into “bigint(20)”
boolean: boolean type, will be converted into “tinyint(1)”
`float“: float number type, will be converted into “float”
decimal: decimal number type, will be converted into “decimal”
datetime: datetime type, will be converted into “datetime”
timestamp: timestamp type, will be converted into “timestamp”
time: time type, will be converted into “time”
date: date type, will be converted into “date”
money: money type, will be converted into “decimal(19,4)”
binary: binary data type, will be converted into “blob”

If the abstract type contains two or more parts separated by spaces (e.g. “string NOT NULL”), then only the first part will be converted, and the rest of the parts will be appended to the converted result. For example, ‘string NOT NULL’ is converted to ‘varchar(255) NOT NULL’.

For some of the abstract types you can also specify a length or precision constraint by appending it in round brackets directly to the type. For example string(32) will be converted into “varchar(32)” on a MySQL database. If the underlying DBMS does not support these kind of constraints for a type it will be ignored.

If a type cannot be found in $typeMap, it will be returned without any change.

Parameters

Parameter	Type	Description
`$type`	`string\|ColumnSchemaBuilder`	Abstract column type

Returns

string: Physical column type.

getExpressionBuilder()

public function getExpressionBuilder(yii\db\ExpressionInterface $expression): yii\db\ExpressionBuilderInterface

Gets object of ExpressionBuilderInterface that is suitable for $expression.

Uses $expressionBuilders array to find a suitable builder class.

Parameters

Parameter	Type	Description
`$expression`	`ExpressionInterface`

Returns

ExpressionBuilderInterface

Throws

yii\base\InvalidArgumentException: when $expression building is not supported by this QueryBuilder.

Since

2.0.14

See also

$expressionBuilders

init()

public function init(): mixed

Initializes the object.

This method is invoked at the end of the constructor after the object is initialized with the given configuration.

Returns

mixed

insert()

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

Creates an INSERT SQL statement.

For example,

$sql = $queryBuilder->insert('user', [
    'name' => 'Sam',
    'age' => 30,
], $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.
`$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.
`$params`	`array`	The binding parameters that will be generated by this method. They should be bound to the DB command later.

Returns

string: The INSERT SQL

renameColumn()

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

Builds a SQL statement 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

string: The SQL statement for renaming a DB column.

renameTable()

public function renameTable(string $oldName, string $newName): string

Builds a SQL statement for renaming a DB table.

Parameters

Parameter	Type	Description
`$oldName`	`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

string: The SQL statement for renaming a DB table.

resetSequence()

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

Creates a SQL statement 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
`$tableName`	`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

string: The SQL statement for resetting sequence.

Throws

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

selectExists()

public function selectExists(string $rawSql): string

Creates a SELECT EXISTS() SQL statement.

Parameters

Parameter	Type	Description
`$rawSql`	`string`	The subquery in a raw form to select from.

Returns

string: The SELECT EXISTS() SQL statement.

Since

2.0.8

setConditionClasses()

public function setConditionClasses(string[] $classes): mixed

Setter for $conditionClasses property.

Parameters

Parameter	Type	Description
`$classes`	`string[]`	Map of condition aliases to condition classes. For example: [‘LIKE’ => yii\db\condition\LikeCondition::class]

Returns

mixed

Since

2.0.14.2

See also

$conditionClasses

setExpressionBuilders()

public function setExpressionBuilders(string[] $builders): mixed

Setter for $expressionBuilders property.

Parameters

Parameter	Type	Description
`$builders`	`string[]`	Array of builders that should be merged with the pre-defined ones in `$expressionBuilders` property.

Returns

mixed

Since

2.0.14

See also

$expressionBuilders

truncateTable()

public function truncateTable(string $table): string

Builds a SQL statement 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

string: The SQL statement for truncating a DB table.

update()

public function update(string $table, array $columns, array|string $condition, array &$params): string

Creates an UPDATE SQL statement.

For example,

$params = [];
$sql = $queryBuilder->update('user', ['status' => 1], 'age > 30', $params);

The method will properly escape the table and column names.

Parameters

Parameter	Type	Description
`$table`	`string`	The table to be updated.
`$columns`	`array`	The column data (name => value) to be updated.
`$condition`	`array\|string`	The condition that will be put in the WHERE part. Please refer to `Query::where()` on how to specify condition.
`$params`	`array`	The binding parameters that will be modified by this method so that they can be bound to the DB command later.

Returns

string: The UPDATE SQL

upsert()

public function upsert(string $table, array|yii\db\Query $insertColumns, array|bool $updateColumns, array &$params): string

Creates an SQL statement 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 binding parameters that will be generated by this method. They should be bound to the DB command later.

Returns

string: The resulting SQL.

Throws

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

Since

2.0.14

concatUnionSql()

protected function concatUnionSql(string $sql, string $union): string

Concatenates the first SELECT statement with the UNION clauses built by buildUnion().

Parameters

Parameter	Type	Description
`$sql`	`string`	The SQL of the first `SELECT` statement.
`$union`	`string`	The UNION clauses.

Returns

string: The compound SQL with the first statement parenthesized.

Since

22.0

defaultConditionClasses()

protected function defaultConditionClasses(): array

Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See $conditionClasses docs for details.

Returns

array

Since

2.0.14

See also

$conditionClasses

defaultExpressionBuilders()

protected function defaultExpressionBuilders(): array

Contains array of default expression builders. Extend this method and override it, if you want to change default expression builders for this query builder. See $expressionBuilders docs for details.

Returns

array

Since

2.0.14

See also

$expressionBuilders

extractAlias()

protected function extractAlias(mixed $table): bool|array

Extracts table alias if there is one or returns false

Parameters

Parameter	Type	Description
`$table`	`mixed`

Returns

bool|array

Since

2.0.24

hasLimit()

protected function hasLimit(mixed $limit): bool

Checks to see if the given limit is effective.

Parameters

Parameter	Type	Description
`$limit`	`mixed`	The given limit

Returns

bool: Whether the limit is effective

hasOffset()

protected function hasOffset(mixed $offset): bool

Checks to see if the given offset is effective.

Parameters

Parameter	Type	Description
`$offset`	`mixed`	The given offset

Returns

bool: Whether the offset is effective

prepareInsertSelectSubQuery()

protected function prepareInsertSelectSubQuery(yii\db\Query $columns, yii\db\Schema $schema, array $params = []): array

Prepare select-subquery and field names for INSERT INTO … SELECT SQL statement.

Parameters

Parameter	Type	Description
`$columns`	`Query`	Object, which represents select query.
`$schema`	`Schema`	Schema object to quote column name.
`$params`	`array`	The parameters to be bound to the generated SQL statement. These parameters will be included in the result with the additional parameters generated during the query building process.

Returns

array: Array of column names, values and params.

Throws

yii\base\InvalidArgumentException: if query’s select does not contain named parameters only.

Since

2.0.11

prepareInsertValues()

protected function prepareInsertValues(string $table, array|yii\db\Query $columns, array $params = []): array

Prepares a VALUES part for an INSERT SQL statement.

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.
`$params`	`array`	The binding parameters that will be generated by this method. They should be bound to the DB command later.

Returns

array: Array of column names, placeholders, values and params.

Since

2.0.14

prepareSelectQuery()

protected function prepareSelectQuery(yii\db\Query $query): yii\db\Query

Performs driver-specific preparation after Query::prepare().

Implementations must not modify the given query; they must return a modified clone instead.

Parameters

Parameter	Type	Description
`$query`	`Query`	The prepared query.

Returns

Query: The query to build.

Since

22.0

prepareUpdateSets()

protected function prepareUpdateSets(string $table, array $columns, array $params = []): array

Prepares a SET parts for an UPDATE SQL statement.

Parameters

Parameter	Type	Description
`$table`	`string`	The table to be updated.
`$columns`	`array`	The column data (name => value) to be updated.
`$params`	`array`	The binding parameters that will be modified by this method so that they can be bound to the DB command later.

Returns

array: An array SET parts for an UPDATE SQL statement (the first array element) and params (the second array element).

Since

2.0.14

prepareUpsertColumns()

protected function prepareUpsertColumns(string $table, array|yii\db\Query $insertColumns, array|bool $updateColumns, yii\db\Constraint[] &$constraints = []): array

Parameters

Parameter	Type	Description
`$table`	`string`
`$insertColumns`	`array\|Query`
`$updateColumns`	`array\|bool`
`$constraints`	`Constraint[]`	This parameter receives a matched constraint list. The constraints will be unique by their column names.

Returns

array

Since

2.0.14

prepareUpsertSets()

protected function prepareUpsertSets(string $table, array<string, mixed>|bool $updateColumns, string[]|null $updateNames, array<string, mixed> $params, Closure(string): string $sourceColumnRef): array{string[], array<string, mixed>}

Prepares the SET assignments for the update branch of an upsert statement.

Parameters

Parameter	Type	Description
`$table`	`string`	The table that the upsert targets.
`$updateColumns`	`array<string, mixed>\|bool`	`true` for every non-unique inserted column, or an explicit `name => value` map.
`$updateNames`	`string[]\|null`	Quoted column names eligible for update.
`$params`	`array<string, mixed>`	The binding parameters modified by this method.
`$sourceColumnRef`	`Closure(string): string`	Maps a quoted column name to its source reference.

Returns

array{string[], array<string, mixed>}: The SET assignments (first element) and params (second element).

Since

22.0