QueryBuilder

Type
Class
Namespace
craft\db\mysql
Inherits
craft\db\mysql\QueryBuilder » yii\db\mysql\QueryBuilder (opens new window) » yii\db\QueryBuilder (opens new window) » yii\base\BaseObject (opens new window)
Implements
yii\base\Configurable (opens new window)
Since
3.0.0

View source (opens new window)

# Public Properties

Property Description
db craft\db\Connection – Connection the DB connection that this command is associated with.
separator (opens new window) string (opens new window) – The separator between different fragments of a SQL statement.
typeMap (opens new window) array (opens new window) – Mapping from abstract column types (keys) to physical column types (values).

# db

Type
craft\db\Connection
Default value
null

Connection the DB connection that this command is associated with.

View source (opens new window)

# Protected Properties

Property Description
conditionBuilders (opens new window) array (opens new window) – Map of query condition to builder methods.
conditionClasses (opens new window) array (opens new window) – Map of condition aliases to condition classes.
expressionBuilders (opens new window) string (opens new window)[], yii\db\ExpressionBuilderInterface (opens new window) – Maps expression class to expression builder class.

# Public Methods

Method Description
__call() (opens new window) Calls the named method which is not a class method.
__construct() (opens new window) Constructor.
__get() (opens new window) Returns the value of an object property.
__isset() (opens new window) Checks if a property is set, i.e. defined and not null.
__set() (opens new window) Sets value of an object property.
__unset() (opens new window) Sets an object property to null.
addCheck() (opens new window) Creates a SQL command for adding a check constraint to an existing table.
addColumn() (opens new window) Builds a SQL statement for adding a new DB column.
addCommentOnColumn() (opens new window) Builds a SQL command for adding comment to column.
addCommentOnTable() (opens new window) Builds a SQL command for adding comment to table.
addDefaultValue() (opens new window) Creates a SQL command for adding a default value constraint to an existing table.
addForeignKey() (opens new window) Builds a SQL statement for adding a foreign key constraint to an existing table.
addPrimaryKey() (opens new window) Builds a SQL statement for adding a primary key constraint to an existing table.
addUnique() (opens new window) Creates a SQL command for adding an unique constraint to an existing table.
alterColumn() (opens new window) Builds a SQL statement for changing the definition of a column.
batchInsert() (opens new window) Generates a batch INSERT SQL statement.
bindParam() (opens new window) Helper method to add $value to $params array using PARAM_PREFIX (opens new window).
build() (opens new window) Generates a SELECT SQL statement from a yii\db\Query (opens new window) object.
buildAndCondition() (opens new window) Connects two or more SQL expressions with the AND or OR operator.
buildBetweenCondition() (opens new window) Creates an SQL expressions with the BETWEEN operator.
buildColumns() (opens new window) Processes columns and properly quotes them if necessary.
buildCondition() (opens new window) Parses the condition specification and generates the corresponding SQL expression.
buildExistsCondition() (opens new window) Creates an SQL expressions with the EXISTS operator.
buildExpression() (opens new window) Builds given $expression
buildFrom() (opens new window)
buildGroupBy() (opens new window)
buildHashCondition() (opens new window) Creates a condition based on column-value pairs.
buildHaving() (opens new window)
buildInCondition() (opens new window) Creates an SQL expressions with the IN operator.
buildJoin() (opens new window)
buildLikeCondition() (opens new window) Creates an SQL expressions with the LIKE operator.
buildLimit() (opens new window)
buildNotCondition() (opens new window) Inverts an SQL expressions with NOT operator.
buildOrderBy() (opens new window)
buildOrderByAndLimit() (opens new window) Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
buildSelect() (opens new window)
buildSimpleCondition() (opens new window) Creates an SQL expressions like "column" operator value.
buildUnion() (opens new window)
buildWhere() (opens new window)
buildWithQueries() (opens new window)
canGetProperty() (opens new window) Returns a value indicating whether a property can be read.
canSetProperty() (opens new window) Returns a value indicating whether a property can be set.
checkIntegrity() (opens new window) Builds a SQL statement for enabling or disabling integrity check.
className() (opens new window) Returns the fully qualified name of this class.
createConditionFromArray() (opens new window) Transforms $condition defined in array format (as described in yii\db\Query::where() (opens new window) to instance of \yii\db\yii\db\condition\ConditionInterface according to conditionClasses (opens new window) map.
createIndex() (opens new window) Builds a SQL statement for creating a new index.
createTable() Builds a SQL statement for creating a new DB table.
createView() (opens new window) Creates a SQL View.
delete() (opens new window) Creates a DELETE SQL statement.
deleteDuplicates() Builds the SQL expression used to delete duplicate rows from a table.
dropCheck() (opens new window) Creates a SQL command for dropping a check constraint.
dropColumn() (opens new window) Builds a SQL statement for dropping a DB column.
dropCommentFromColumn() (opens new window) Builds a SQL command for adding comment to column.
dropCommentFromTable() (opens new window) Builds a SQL command for adding comment to table.
dropDefaultValue() (opens new window) Creates a SQL command for dropping a default value constraint.
dropForeignKey() (opens new window) Builds a SQL statement for dropping a foreign key constraint.
dropIndex() (opens new window) Builds a SQL statement for dropping an index.
dropPrimaryKey() (opens new window) Builds a SQL statement for removing a primary key constraint to an existing table.
dropTable() (opens new window) Builds a SQL statement for dropping a DB table.
dropTableIfExists() Builds a SQL statement for dropping a DB table if it exists.
dropUnique() (opens new window) Creates a SQL command for dropping an unique constraint.
dropView() (opens new window) Drops a SQL View.
executeResetSequence() (opens new window) Execute a SQL statement for resetting the sequence value of a table's primary key.
fixedOrder() Builds the SQL expression used to return a DB result in a fixed order.
getColumnType() (opens new window) Converts an abstract column type into a physical column type.
getExpressionBuilder() (opens new window) Gets object of yii\db\ExpressionBuilderInterface (opens new window) that is suitable for $expression.
hasMethod() (opens new window) Returns a value indicating whether a method is defined.
hasProperty() (opens new window) Returns a value indicating whether a property is defined.
init() Initializes the object.
insert() (opens new window) Creates an INSERT SQL statement.
renameColumn() (opens new window) Builds a SQL statement for renaming a column.
renameSequence() Builds a SQL statement for renaming a DB sequence.
renameTable() (opens new window) Builds a SQL statement for renaming a DB table.
replace() Builds a SQL statement for replacing some text with other text in a given table column.
resetSequence() (opens new window) Creates a SQL statement for resetting the sequence value of a table's primary key.
selectExists() (opens new window) Creates a SELECT EXISTS() SQL statement.
setConditionClasses() (opens new window) Setter for conditionClasses (opens new window) property.
setExpressionBuilders() (opens new window) Setter for expressionBuilders (opens new window) property.
truncateTable() (opens new window) Builds a SQL statement for truncating a DB table.
update() (opens new window) Creates an UPDATE SQL statement.
upsert() (opens new window) 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.

# createTable()

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 can contain an abstract DB type. The getColumnType() (opens new window) 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',
]);

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL statement for creating a new DB table.

# deleteDuplicates()

Since
3.5.2

Builds the SQL expression used to delete duplicate rows from a table.

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL expression

# dropTableIfExists()

Builds a SQL statement for dropping a DB table if it exists.

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL statement for dropping a DB table.

# fixedOrder()

Builds the SQL expression used to return a DB result in a fixed order.

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL expression.

# init()

Initializes the object.

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

View source (opens new window)

# renameSequence()

Builds a SQL statement for renaming a DB sequence.

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL statement for renaming a DB table.

Throws

# replace()

Builds a SQL statement for replacing some text with other text in a given table column.

View source (opens new window)

Arguments

Returns

string (opens new window) – The SQL statement for replacing some text in a given table.

# Protected Methods

Method Description
defaultConditionClasses() (opens new window) Contains array of default condition classes. Extend this method, if you want to change default condition classes for the query builder. See conditionClasses (opens new window) docs for details.
defaultExpressionBuilders() (opens new window) 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 (opens new window) docs for details.
extractAlias() (opens new window) Extracts table alias if there is one or returns false
hasLimit() (opens new window) Checks to see if the given limit is effective.
hasOffset() (opens new window) Checks to see if the given offset is effective.
prepareInsertSelectSubQuery() (opens new window) Prepare select-subquery and field names for INSERT INTO ... SELECT SQL statement.
prepareInsertValues() (opens new window) Prepares a VALUES part for an INSERT SQL statement.
prepareUpdateSets() (opens new window) Prepares a SET parts for an UPDATE SQL statement.
prepareUpsertColumns() (opens new window)

# Constants

Constant Description
PARAM_PREFIX The prefix for automatically generated query binding parameters.