# QueryBuilder

> QueryBuilder builds a SELECT SQL statement based on the specification given as a yii\db\Query object.

[API reference](/docs/api/index.md) › [yii](/docs/api/yii/index.md) › [db](/docs/api/yii/db/index.md) › QueryBuilder

| class | `yii\db\QueryBuilder` |
| --- | --- |
| Extends | [`yii\base\BaseObject`](/docs/api/yii/base/baseobject/index.md) |
| Implements | [`yii\base\Configurable`](/docs/api/yii/base/configurable/index.md) |
| Since | 2.0 |
| Source | [`framework/db/QueryBuilder.php`](https://github.com/yiisoft/yii2/blob/22.0/framework/db/QueryBuilder.php#L38) |

5 subclasses:

- [`yii\db\mssql\QueryBuilder`](/docs/api/yii/db/mssql/querybuilder/index.md)
- [`yii\db\mysql\QueryBuilder`](/docs/api/yii/db/mysql/querybuilder/index.md)
- [`yii\db\oci\QueryBuilder`](/docs/api/yii/db/oci/querybuilder/index.md)
- [`yii\db\pgsql\QueryBuilder`](/docs/api/yii/db/pgsql/querybuilder/index.md)
- [`yii\db\sqlite\QueryBuilder`](/docs/api/yii/db/sqlite/querybuilder/index.md)

QueryBuilder builds a SELECT SQL statement based on the specification given as a [`Query`](/docs/api/yii/db/query/index.md) object.

SQL statements are created from [`Query`](/docs/api/yii/db/query/index.md) objects using the [`build()`](/docs/api/yii/db/querybuilder/index.md#method-build)-method.

QueryBuilder is also used by [`Command`](/docs/api/yii/db/command/index.md) 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](/docs/guide/db-query-builder/index.md).

## Properties

| Property | Type | Description | Defined by |
| --- | --- | --- | --- |
| <span id="property-db"></span>`$db` | [`Connection`](/docs/api/yii/db/connection/index.md) | The database connection. Default: `null`. | `QueryBuilder` |
| [`$separator`](#property-separator) | `string` | The separator between different fragments of a SQL statement. Default: `' '`. | `QueryBuilder` |
| [`$typeMap`](#property-typeMap) | `array` | The abstract column types mapped to physical column types. Default: `[]`. | `QueryBuilder` |
| [`$conditionClasses`](#property-conditionClasses) protected | `array` | Map of condition aliases to condition classes. Default: `[]`. Available since 2.0.14. | `QueryBuilder` |
| [`$expressionBuilders`](#property-expressionBuilders) protected | `string[]\|`[`ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md)`[]` | Maps expression class to expression builder class. Default: `[]`. Available since 2.0.14. | `QueryBuilder` |

## Methods

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

## Constants

| Constant | Value | Description | Defined by |
| --- | --- | --- | --- |
| <span id="const-PARAM_PREFIX"></span>`PARAM_PREFIX` | `':qp'` | The prefix for automatically generated query binding parameters. | `QueryBuilder` |

## Property details

### $separator <span id="property-separator"></span>

```php
public string $separator = ' '
```

The separator between different fragments of a SQL statement.

Defaults to an empty space. This is mainly used by [`build()`](/docs/api/yii/db/querybuilder/index.md#method-build) when generating a SQL statement.

### $typeMap <span id="property-typeMap"></span>

```php
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 <span id="property-conditionClasses"></span>

```php
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()`](/docs/api/yii/db/querybuilder/index.md#method-createConditionFromArray) method.
See default condition classes list in [`defaultConditionClasses()`](/docs/api/yii/db/querybuilder/index.md#method-defaultConditionClasses) method.

In case you want to add custom conditions support, use the [`setConditionClasses()`](/docs/api/yii/db/querybuilder/index.md#method-setConditionClasses) method.

**Since**

2.0.14

### $expressionBuilders <span id="property-expressionBuilders"></span>

```php
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()`](/docs/api/yii/db/querybuilder/index.md#method-buildExpression) to build SQL expressions form expression objects.
See default values in [`defaultExpressionBuilders()`](/docs/api/yii/db/querybuilder/index.md#method-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()`](/docs/api/yii/db/querybuilder/index.md#method-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() <span id="method-__construct"></span>

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

Constructor.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$connection` | [`Connection`](/docs/api/yii/db/connection/index.md) | The database connection. |
| `$config` | `array` | Name-value pairs that will be used to initialize the object properties |

### addCheck() <span id="method-addCheck"></span>

```php
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() <span id="method-addColumn"></span>

```php
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()`](/docs/api/yii/db/querybuilder/index.md#method-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() <span id="method-addCommentOnColumn"></span>

```php
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() <span id="method-addCommentOnTable"></span>

```php
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() <span id="method-addDefaultValue"></span>

```php
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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

**Since**

2.0.13

### addForeignKey() <span id="method-addForeignKey"></span>

```php
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() <span id="method-addPrimaryKey"></span>

```php
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() <span id="method-addUnique"></span>

```php
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() <span id="method-alterColumn"></span>

```php
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()`](/docs/api/yii/db/querybuilder/index.md#method-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() <span id="method-batchInsert"></span>

```php
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`](https://www.php.net/manual/en/class.generator.php) | 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() <span id="method-bindParam"></span>

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

Helper method to add $value to $params array using [`PARAM_PREFIX`](/docs/api/yii/db/querybuilder/index.md#const-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() <span id="method-build"></span>

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

Generates a SELECT SQL statement from a [`Query`](/docs/api/yii/db/query/index.md) object.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$query` | [`Query`](/docs/api/yii/db/query/index.md) | The [`Query`](/docs/api/yii/db/query/index.md) 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() <span id="method-buildColumns"></span>

```php
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() <span id="method-buildCondition"></span>

```php
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`](/docs/api/yii/db/expressioninterface/index.md) | The condition specification. Please refer to [`Query::where()`](/docs/api/yii/db/query/index.md#method-where) on how to specify a condition. |
| `$params` | `array` | The binding parameters to be populated |

**Returns**

`string`: The generated SQL expression

### buildExpression() <span id="method-buildExpression"></span>

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

Builds given $expression

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$expression` | [`ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md) | 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`](/docs/api/yii/base/invalidargumentexception/index.md): when $expression building is not supported by this QueryBuilder.

**Since**

2.0.14

**See also**

- [`ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md)
- [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders)
- [`ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md)

### buildFrom() <span id="method-buildFrom"></span>

```php
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`](/docs/api/yii/db/query/index.md#property-from).

### buildGroupBy() <span id="method-buildGroupBy"></span>

```php
public function buildGroupBy(array $columns): string
```

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$columns` | `array` |  |

**Returns**

`string`: The GROUP BY clause

### buildHaving() <span id="method-buildHaving"></span>

```php
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`](/docs/api/yii/db/query/index.md#property-having).

### buildJoin() <span id="method-buildJoin"></span>

```php
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`](/docs/api/yii/db/query/index.md#property-join).

**Throws**

- [`Exception`](/docs/api/yii/db/exception/index.md): if the $joins parameter is not in proper format

### buildLimit() <span id="method-buildLimit"></span>

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

**Parameters**

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

**Returns**

`string`: The LIMIT and OFFSET clauses

### buildOrderBy() <span id="method-buildOrderBy"></span>

```php
public function buildOrderBy(array $columns): string
```

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$columns` | `array` |  |

**Returns**

`string`: The ORDER BY clause built from [`Query::$orderBy`](/docs/api/yii/db/querytrait/index.md#property-orderBy).

### buildOrderByAndLimit() <span id="method-buildOrderByAndLimit"></span>

```php
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()`](/docs/api/yii/db/querytrait/index.md#method-orderBy) for more details on how to specify this parameter. |
| `$limit` | `int` | The limit number. See [`Query::limit()`](/docs/api/yii/db/querytrait/index.md#method-limit) for more details. |
| `$offset` | `int` | The offset number. See [`Query::offset()`](/docs/api/yii/db/querytrait/index.md#method-offset) for more details. |

**Returns**

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

### buildSelect() <span id="method-buildSelect"></span>

```php
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`](/docs/api/yii/db/query/index.md#property-select).

### buildUnion() <span id="method-buildUnion"></span>

```php
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`](/docs/api/yii/db/query/index.md#property-union).

### buildUnionOrderByAndLimit() <span id="method-buildUnionOrderByAndLimit"></span>

```php
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`](/docs/api/yii/db/expressioninterface/index.md)`>\|null` | The global order by columns. |
| `$limit` | `int\|`[`ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md)`\|null` | The global limit. |
| `$offset` | `int\|`[`ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md)`\|null` | The global offset. |

**Returns**

`string`: The completed UNION SQL.

**Since**

22.0

### buildWhere() <span id="method-buildWhere"></span>

```php
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`](/docs/api/yii/db/querytrait/index.md#property-where).

### buildWithQueries() <span id="method-buildWithQueries"></span>

```php
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()`](/docs/api/yii/db/query/index.md#method-withQuery)

### checkIntegrity() <span id="method-checkIntegrity"></span>

```php
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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS

### createConditionFromArray() <span id="method-createConditionFromArray"></span>

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

Transforms $condition defined in array format (as described in [`Query::where()`](/docs/api/yii/db/query/index.md#method-where)
to instance of ConditionInterface according to
[`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses) map.

**Parameters**

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

**Returns**

[`yii\db\conditions\ConditionInterface`](/docs/api/yii/db/conditions/conditioninterface/index.md)

**Since**

2.0.14

**See also**

- [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses)

### createIndex() <span id="method-createIndex"></span>

```php
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() <span id="method-createTable"></span>

```php
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()`](/docs/api/yii/db/querybuilder/index.md#method-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() <span id="method-createView"></span>

```php
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`](/docs/api/yii/db/query/index.md) | The select statement which defines the view. This can be either a string or a [`Query`](/docs/api/yii/db/query/index.md) object. |

**Returns**

`string`: The `CREATE VIEW` SQL statement.

**Since**

2.0.14

### delete() <span id="method-delete"></span>

```php
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()`](/docs/api/yii/db/query/index.md#method-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() <span id="method-dropCheck"></span>

```php
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() <span id="method-dropColumn"></span>

```php
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() <span id="method-dropCommentFromColumn"></span>

```php
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() <span id="method-dropCommentFromTable"></span>

```php
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() <span id="method-dropDefaultValue"></span>

```php
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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

**Since**

2.0.13

### dropForeignKey() <span id="method-dropForeignKey"></span>

```php
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() <span id="method-dropIndex"></span>

```php
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() <span id="method-dropPrimaryKey"></span>

```php
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() <span id="method-dropTable"></span>

```php
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() <span id="method-dropUnique"></span>

```php
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() <span id="method-dropView"></span>

```php
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() <span id="method-executeResetSequence"></span>

```php
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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS

**Since**

2.0.16

### getColumnType() <span id="method-getColumnType"></span>

```php
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`](/docs/api/yii/db/querybuilder/index.md#property-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`](/docs/api/yii/db/querybuilder/index.md#property-typeMap), it will be returned without any change.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$type` | `string\|`[`ColumnSchemaBuilder`](/docs/api/yii/db/columnschemabuilder/index.md) | Abstract column type |

**Returns**

`string`: Physical column type.

### getExpressionBuilder() <span id="method-getExpressionBuilder"></span>

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

Gets object of [`ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md) that is suitable for $expression.

Uses [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) array to find a suitable builder class.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$expression` | [`ExpressionInterface`](/docs/api/yii/db/expressioninterface/index.md) |  |

**Returns**

[`ExpressionBuilderInterface`](/docs/api/yii/db/expressionbuilderinterface/index.md)

**Throws**

- [`yii\base\InvalidArgumentException`](/docs/api/yii/base/invalidargumentexception/index.md): when $expression building is not supported by this QueryBuilder.

**Since**

2.0.14

**See also**

- [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders)

### init() <span id="method-init"></span>

```php
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() <span id="method-insert"></span>

```php
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`](/docs/api/yii/db/query/index.md) | The column data (name => value) to be inserted into the table or instance of [Query](/docs/api/yii/db/query/index.md) to perform INSERT INTO ... SELECT SQL statement. Passing of [Query](/docs/api/yii/db/query/index.md) 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() <span id="method-renameColumn"></span>

```php
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() <span id="method-renameTable"></span>

```php
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() <span id="method-resetSequence"></span>

```php
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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

### selectExists() <span id="method-selectExists"></span>

```php
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() <span id="method-setConditionClasses"></span>

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

Setter for [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-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`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses)

### setExpressionBuilders() <span id="method-setExpressionBuilders"></span>

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

Setter for [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) property.

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$builders` | `string[]` | Array of builders that should be merged with the pre-defined ones in [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders) property. |

**Returns**

`mixed`

**Since**

2.0.14

**See also**

- [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders)

### truncateTable() <span id="method-truncateTable"></span>

```php
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() <span id="method-update"></span>

```php
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()`](/docs/api/yii/db/query/index.md#method-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() <span id="method-upsert"></span>

```php
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`](/docs/api/yii/db/query/index.md) | The column data (name => value) to be inserted into the table or instance of [`Query`](/docs/api/yii/db/query/index.md) 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`](/docs/api/yii/base/notsupportedexception/index.md): if this is not supported by the underlying DBMS.

**Since**

2.0.14

### concatUnionSql() <span id="method-concatUnionSql"></span>

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

Concatenates the first `SELECT` statement with the UNION clauses built by [`buildUnion()`](/docs/api/yii/db/querybuilder/index.md#method-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() <span id="method-defaultConditionClasses"></span>

```php
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/api/yii/db/querybuilder/index.md#property-conditionClasses) docs for details.

**Returns**

`array`

**Since**

2.0.14

**See also**

- [`$conditionClasses`](/docs/api/yii/db/querybuilder/index.md#property-conditionClasses)

### defaultExpressionBuilders() <span id="method-defaultExpressionBuilders"></span>

```php
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/api/yii/db/querybuilder/index.md#property-expressionBuilders) docs for details.

**Returns**

`array`

**Since**

2.0.14

**See also**

- [`$expressionBuilders`](/docs/api/yii/db/querybuilder/index.md#property-expressionBuilders)

### extractAlias() <span id="method-extractAlias"></span>

```php
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() <span id="method-hasLimit"></span>

```php
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() <span id="method-hasOffset"></span>

```php
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() <span id="method-prepareInsertSelectSubQuery"></span>

```php
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`](/docs/api/yii/db/query/index.md) | Object, which represents select query. |
| `$schema` | [`Schema`](/docs/api/yii/db/schema/index.md) | 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`](/docs/api/yii/base/invalidargumentexception/index.md): if query's select does not contain named parameters only.

**Since**

2.0.11

### prepareInsertValues() <span id="method-prepareInsertValues"></span>

```php
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`](/docs/api/yii/db/query/index.md) | The column data (name => value) to be inserted into the table or instance of [Query](/docs/api/yii/db/query/index.md) 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() <span id="method-prepareSelectQuery"></span>

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

Performs driver-specific preparation after [`Query::prepare()`](/docs/api/yii/db/query/index.md#method-prepare).

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

**Parameters**

| Parameter | Type | Description |
| --- | --- | --- |
| `$query` | [`Query`](/docs/api/yii/db/query/index.md) | The prepared query. |

**Returns**

[`Query`](/docs/api/yii/db/query/index.md): The query to build.

**Since**

22.0

### prepareUpdateSets() <span id="method-prepareUpdateSets"></span>

```php
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() <span id="method-prepareUpsertColumns"></span>

```php
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`](/docs/api/yii/db/query/index.md) |  |
| `$updateColumns` | `array\|bool` |  |
| `$constraints` | [`Constraint`](/docs/api/yii/db/constraint/index.md)`[]` | This parameter receives a matched constraint list. The constraints will be unique by their column names. |

**Returns**

`array`

**Since**

2.0.14

### prepareUpsertSets() <span id="method-prepareUpsertSets"></span>

```php
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`](https://www.php.net/manual/en/class.closure.php)`(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