Class yii\db\pgsql\QueryBuilder
Inheritance | yii\db\pgsql\QueryBuilder » yii\db\QueryBuilder » yii\base\BaseObject |
---|---|
Implements | yii\base\Configurable |
Available since version | 2.0 |
Source Code | https://github.com/yiisoft/yii2/blob/master/framework/db/pgsql/QueryBuilder.php |
QueryBuilder is the query builder for PostgreSQL databases.
Public Properties
Property | Type | Description | Defined By |
---|---|---|---|
$conditionBuilders | array | Map of query condition to builder methods. | yii\db\QueryBuilder |
$conditionClasses | array | Map of condition aliases to condition classes. | yii\db\QueryBuilder |
$db | yii\db\Connection | The database connection. | yii\db\QueryBuilder |
$expressionBuilders | string[]|yii\db\ExpressionBuilderInterface[] | Maps expression class to expression builder class. | yii\db\QueryBuilder |
$separator | string | The separator between different fragments of a SQL statement. | yii\db\QueryBuilder |
$typeMap | array | Mapping from abstract column types (keys) to physical column types (values). | yii\db\pgsql\QueryBuilder |
Public Methods
Method | Description | Defined By |
---|---|---|
__call() | Calls the named method which is not a class method. | yii\base\BaseObject |
__construct() | Constructor. | yii\db\QueryBuilder |
__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. | yii\db\QueryBuilder |
addColumn() | Builds a SQL statement for adding a new DB column. | yii\db\QueryBuilder |
addCommentOnColumn() | Builds a SQL command for adding comment to column. | yii\db\QueryBuilder |
addCommentOnTable() | Builds a SQL command for adding comment to table. | yii\db\QueryBuilder |
addDefaultValue() | Creates a SQL command for adding a default value constraint to an existing table. | yii\db\QueryBuilder |
addForeignKey() | Builds a SQL statement for adding a foreign key constraint to an existing table. | yii\db\QueryBuilder |
addPrimaryKey() | Builds a SQL statement for adding a primary key constraint to an existing table. | yii\db\QueryBuilder |
addUnique() | Creates a SQL command for adding an unique constraint to an existing table. | yii\db\QueryBuilder |
alterColumn() | Builds a SQL statement for changing the definition of a column. | yii\db\pgsql\QueryBuilder |
batchInsert() | Generates a batch INSERT SQL statement. | yii\db\pgsql\QueryBuilder |
bindParam() | Helper method to add $value to $params array using PARAM_PREFIX. | yii\db\QueryBuilder |
build() | Generates a SELECT SQL statement from a yii\db\Query object. | yii\db\QueryBuilder |
buildAndCondition() | Connects two or more SQL expressions with the AND or OR operator. |
yii\db\QueryBuilder |
buildBetweenCondition() | Creates an SQL expressions with the BETWEEN operator. |
yii\db\QueryBuilder |
buildColumns() | Processes columns and properly quotes them if necessary. | yii\db\QueryBuilder |
buildCondition() | Parses the condition specification and generates the corresponding SQL expression. | yii\db\QueryBuilder |
buildExistsCondition() | Creates an SQL expressions with the EXISTS operator. |
yii\db\QueryBuilder |
buildExpression() | Builds given $expression | yii\db\QueryBuilder |
buildFrom() | yii\db\QueryBuilder | |
buildGroupBy() | yii\db\QueryBuilder | |
buildHashCondition() | Creates a condition based on column-value pairs. | yii\db\QueryBuilder |
buildHaving() | yii\db\QueryBuilder | |
buildInCondition() | Creates an SQL expressions with the IN operator. |
yii\db\QueryBuilder |
buildJoin() | yii\db\QueryBuilder | |
buildLikeCondition() | Creates an SQL expressions with the LIKE operator. |
yii\db\QueryBuilder |
buildLimit() | yii\db\QueryBuilder | |
buildNotCondition() | Inverts an SQL expressions with NOT operator. |
yii\db\QueryBuilder |
buildOrderBy() | yii\db\QueryBuilder | |
buildOrderByAndLimit() | Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL. | yii\db\QueryBuilder |
buildSelect() | yii\db\QueryBuilder | |
buildSimpleCondition() | Creates an SQL expressions like "column" operator value . |
yii\db\QueryBuilder |
buildUnion() | yii\db\QueryBuilder | |
buildWhere() | yii\db\QueryBuilder | |
buildWithQueries() | yii\db\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. | yii\db\pgsql\QueryBuilder |
className() | Returns the fully qualified name of this class. | yii\base\BaseObject |
createConditionFromArray() | Transforms $condition defined in array format (as described in yii\db\Query::where() to instance of yii\db\condition\ConditionInterface according to $conditionClasses map. | yii\db\QueryBuilder |
createIndex() | Builds a SQL statement for creating a new index. | yii\db\pgsql\QueryBuilder |
createTable() | Builds a SQL statement for creating a new DB table. | yii\db\QueryBuilder |
createView() | Creates a SQL View. | yii\db\QueryBuilder |
delete() | Creates a DELETE SQL statement. | yii\db\QueryBuilder |
dropCheck() | Creates a SQL command for dropping a check constraint. | yii\db\QueryBuilder |
dropColumn() | Builds a SQL statement for dropping a DB column. | yii\db\QueryBuilder |
dropCommentFromColumn() | Builds a SQL command for adding comment to column. | yii\db\QueryBuilder |
dropCommentFromTable() | Builds a SQL command for adding comment to table. | yii\db\QueryBuilder |
dropDefaultValue() | Creates a SQL command for dropping a default value constraint. | yii\db\QueryBuilder |
dropForeignKey() | Builds a SQL statement for dropping a foreign key constraint. | yii\db\QueryBuilder |
dropIndex() | Builds a SQL statement for dropping an index. | yii\db\pgsql\QueryBuilder |
dropPrimaryKey() | Builds a SQL statement for removing a primary key constraint to an existing table. | yii\db\QueryBuilder |
dropTable() | Builds a SQL statement for dropping a DB table. | yii\db\QueryBuilder |
dropUnique() | Creates a SQL command for dropping an unique constraint. | yii\db\QueryBuilder |
dropView() | Drops a SQL View. | yii\db\QueryBuilder |
executeResetSequence() | Execute a SQL statement for resetting the sequence value of a table's primary key. | yii\db\QueryBuilder |
getColumnType() | Converts an abstract column type into a physical column type. | yii\db\QueryBuilder |
getExpressionBuilder() | Gets object of yii\db\ExpressionBuilderInterface that is suitable for $expression. | yii\db\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. | yii\db\QueryBuilder |
insert() | Creates an INSERT SQL statement. | yii\db\pgsql\QueryBuilder |
renameColumn() | Builds a SQL statement for renaming a column. | yii\db\QueryBuilder |
renameTable() | Builds a SQL statement for renaming a DB table. | yii\db\pgsql\QueryBuilder |
resetSequence() | Creates a SQL statement for resetting the sequence value of a table's primary key. | yii\db\pgsql\QueryBuilder |
selectExists() | Creates a SELECT EXISTS() SQL statement. | yii\db\QueryBuilder |
setConditionClasses() | Setter for $conditionClasses property. | yii\db\QueryBuilder |
setExpressionBuilders() | Setter for $expressionBuilders property. | yii\db\QueryBuilder |
truncateTable() | Builds a SQL statement for truncating a DB table. | yii\db\pgsql\QueryBuilder |
update() | Creates an UPDATE SQL statement. | yii\db\pgsql\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. | yii\db\pgsql\QueryBuilder |
Protected Methods
Method | Description | Defined By |
---|---|---|
defaultConditionClasses() | 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. | yii\db\pgsql\QueryBuilder |
defaultExpressionBuilders() | 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. | yii\db\pgsql\QueryBuilder |
extractAlias() | Extracts table alias if there is one or returns false | yii\db\QueryBuilder |
hasLimit() | Checks to see if the given limit is effective. | yii\db\QueryBuilder |
hasOffset() | Checks to see if the given offset is effective. | yii\db\QueryBuilder |
prepareInsertSelectSubQuery() | Prepare select-subquery and field names for INSERT INTO ... SELECT SQL statement. | yii\db\QueryBuilder |
prepareInsertValues() | Prepares a VALUES part for an INSERT SQL statement. |
yii\db\QueryBuilder |
prepareUpdateSets() | Prepares a SET parts for an UPDATE SQL statement. |
yii\db\QueryBuilder |
prepareUpsertColumns() | yii\db\QueryBuilder |
Constants
Constant | Value | Description | Defined By |
---|---|---|---|
INDEX_B_TREE | 'btree' | Defines a B-tree index for createIndex(). | yii\db\pgsql\QueryBuilder |
INDEX_GIN | 'gin' | Defines a GIN index for createIndex(). | yii\db\pgsql\QueryBuilder |
INDEX_GIST | 'gist' | Defines a GiST index for createIndex(). | yii\db\pgsql\QueryBuilder |
INDEX_HASH | 'hash' | Defines a hash index for createIndex(). | yii\db\pgsql\QueryBuilder |
INDEX_UNIQUE | 'unique' | Defines a UNIQUE index for createIndex(). | yii\db\pgsql\QueryBuilder |
PARAM_PREFIX | ':qp' | The prefix for automatically generated query binding parameters. | yii\db\QueryBuilder |
Property Details
Mapping from abstract column types (keys) to physical column types (values).
\yii\db\pgsql\Schema::TYPE_PK => 'serial NOT NULL PRIMARY KEY',
\yii\db\pgsql\Schema::TYPE_UPK => 'serial NOT NULL PRIMARY KEY',
\yii\db\pgsql\Schema::TYPE_BIGPK => 'bigserial NOT NULL PRIMARY KEY',
\yii\db\pgsql\Schema::TYPE_UBIGPK => 'bigserial NOT NULL PRIMARY KEY',
\yii\db\pgsql\Schema::TYPE_CHAR => 'char(1)',
\yii\db\pgsql\Schema::TYPE_STRING => 'varchar(255)',
\yii\db\pgsql\Schema::TYPE_TEXT => 'text',
\yii\db\pgsql\Schema::TYPE_TINYINT => 'smallint',
\yii\db\pgsql\Schema::TYPE_SMALLINT => 'smallint',
\yii\db\pgsql\Schema::TYPE_INTEGER => 'integer',
\yii\db\pgsql\Schema::TYPE_BIGINT => 'bigint',
\yii\db\pgsql\Schema::TYPE_FLOAT => 'double precision',
\yii\db\pgsql\Schema::TYPE_DOUBLE => 'double precision',
\yii\db\pgsql\Schema::TYPE_DECIMAL => 'numeric(10,0)',
\yii\db\pgsql\Schema::TYPE_DATETIME => 'timestamp(0)',
\yii\db\pgsql\Schema::TYPE_TIMESTAMP => 'timestamp(0)',
\yii\db\pgsql\Schema::TYPE_TIME => 'time(0)',
\yii\db\pgsql\Schema::TYPE_DATE => 'date',
\yii\db\pgsql\Schema::TYPE_BINARY => 'bytea',
\yii\db\pgsql\Schema::TYPE_BOOLEAN => 'boolean',
\yii\db\pgsql\Schema::TYPE_MONEY => 'numeric(19,4)',
\yii\db\pgsql\Schema::TYPE_JSON => 'jsonb',
]
Method Details
Defined in: yii\base\BaseObject::__call()
Calls the named method which is not a class method.
Do not call this method directly as it is a PHP magic method that will be implicitly called when an unknown method is being invoked.
public mixed __call ( $name, $params ) | ||
$name | string |
The method name |
$params | array |
Method parameters |
return | mixed |
The method return value |
---|---|---|
throws | yii\base\UnknownMethodException |
when calling unknown method |
public function __call($name, $params)
{
throw new UnknownMethodException('Calling unknown method: ' . get_class($this) . "::$name()");
}
Defined in: yii\db\QueryBuilder::__construct()
Constructor.
public void __construct ( $connection, $config = [] ) | ||
$connection | yii\db\Connection |
The database connection. |
$config | array |
Name-value pairs that will be used to initialize the object properties |
public function __construct($connection, $config = [])
{
$this->db = $connection;
parent::__construct($config);
}
Defined in: yii\base\BaseObject::__get()
Returns the value of an object property.
Do not call this method directly as it is a PHP magic method that
will be implicitly called when executing $value = $object->property;
.
See also __set().
public mixed __get ( $name ) | ||
$name | string |
The property name |
return | mixed |
The property value |
---|---|---|
throws | yii\base\UnknownPropertyException |
if the property is not defined |
throws | yii\base\InvalidCallException |
if the property is write-only |
public function __get($name)
{
$getter = 'get' . $name;
if (method_exists($this, $getter)) {
return $this->$getter();
} elseif (method_exists($this, 'set' . $name)) {
throw new InvalidCallException('Getting write-only property: ' . get_class($this) . '::' . $name);
}
throw new UnknownPropertyException('Getting unknown property: ' . get_class($this) . '::' . $name);
}
Defined in: yii\base\BaseObject::__isset()
Checks if a property is set, i.e. defined and not null.
Do not call this method directly as it is a PHP magic method that
will be implicitly called when executing isset($object->property)
.
Note that if the property is not defined, false will be returned.
public boolean __isset ( $name ) | ||
$name | string |
The property name or the event name |
return | boolean |
Whether the named property is set (not null). |
---|
public function __isset($name)
{
$getter = 'get' . $name;
if (method_exists($this, $getter)) {
return $this->$getter() !== null;
}
return false;
}
Defined in: yii\base\BaseObject::__set()
Sets value of an object property.
Do not call this method directly as it is a PHP magic method that
will be implicitly called when executing $object->property = $value;
.
See also __get().
public void __set ( $name, $value ) | ||
$name | string |
The property name or the event name |
$value | mixed |
The property value |
throws | yii\base\UnknownPropertyException |
if the property is not defined |
---|---|---|
throws | yii\base\InvalidCallException |
if the property is read-only |
public function __set($name, $value)
{
$setter = 'set' . $name;
if (method_exists($this, $setter)) {
$this->$setter($value);
} elseif (method_exists($this, 'get' . $name)) {
throw new InvalidCallException('Setting read-only property: ' . get_class($this) . '::' . $name);
} else {
throw new UnknownPropertyException('Setting unknown property: ' . get_class($this) . '::' . $name);
}
}
Defined in: yii\base\BaseObject::__unset()
Sets an object property to null.
Do not call this method directly as it is a PHP magic method that
will be implicitly called when executing unset($object->property)
.
Note that if the property is not defined, this method will do nothing. If the property is read-only, it will throw an exception.
public void __unset ( $name ) | ||
$name | string |
The property name |
throws | yii\base\InvalidCallException |
if the property is read only. |
---|
public function __unset($name)
{
$setter = 'set' . $name;
if (method_exists($this, $setter)) {
$this->$setter(null);
} elseif (method_exists($this, 'get' . $name)) {
throw new InvalidCallException('Unsetting read-only property: ' . get_class($this) . '::' . $name);
}
}
Defined in: yii\db\QueryBuilder::addCheck()
Creates a SQL command for adding a check constraint to an existing table.
public string addCheck ( $name, $table, $expression ) | ||
$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 |
return | string |
The SQL statement for adding a check constraint to an existing table. |
---|
public function addCheck($name, $table, $expression)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
. $this->db->quoteColumnName($name) . ' CHECK (' . $this->db->quoteSql($expression) . ')';
}
Defined in: yii\db\QueryBuilder::addColumn()
Builds a SQL statement for adding a new DB column.
public string addColumn ( $table, $column, $type ) | ||
$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'. |
return | string |
The SQL statement for adding a new column. |
---|
public function addColumn($table, $column, $type)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' ADD ' . $this->db->quoteColumnName($column) . ' '
. $this->getColumnType($type);
}
Defined in: yii\db\QueryBuilder::addCommentOnColumn()
Builds a SQL command for adding comment to column.
public string addCommentOnColumn ( $table, $column, $comment ) | ||
$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. |
return | string |
The SQL statement for adding comment on column |
---|
public function addCommentOnColumn($table, $column, $comment)
{
return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . ' IS ' . $this->db->quoteValue($comment);
}
Defined in: yii\db\QueryBuilder::addCommentOnTable()
Builds a SQL command for adding comment to table.
public string addCommentOnTable ( $table, $comment ) | ||
$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. |
return | string |
The SQL statement for adding comment on table |
---|
public function addCommentOnTable($table, $comment)
{
return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . ' IS ' . $this->db->quoteValue($comment);
}
Defined in: yii\db\QueryBuilder::addDefaultValue()
Creates a SQL command for adding a default value constraint to an existing table.
public string addDefaultValue ( $name, $table, $column, $value ) | ||
$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. |
return | 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. |
public function addDefaultValue($name, $table, $column, $value)
{
throw new NotSupportedException($this->db->getDriverName() . ' does not support adding default value constraints.');
}
Defined in: yii\db\QueryBuilder::addForeignKey()
Builds a SQL statement for adding a foreign key constraint to an existing table.
The method will properly quote the table and column names.
public string addForeignKey ( $name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null ) | ||
$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 |
return | string |
The SQL statement for adding a foreign key constraint to an existing table. |
---|
public function addForeignKey($name, $table, $columns, $refTable, $refColumns, $delete = null, $update = null)
{
$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' ADD CONSTRAINT ' . $this->db->quoteColumnName($name)
. ' FOREIGN KEY (' . $this->buildColumns($columns) . ')'
. ' REFERENCES ' . $this->db->quoteTableName($refTable)
. ' (' . $this->buildColumns($refColumns) . ')';
if ($delete !== null) {
$sql .= ' ON DELETE ' . $delete;
}
if ($update !== null) {
$sql .= ' ON UPDATE ' . $update;
}
return $sql;
}
Defined in: yii\db\QueryBuilder::addPrimaryKey()
Builds a SQL statement for adding a primary key constraint to an existing table.
public string addPrimaryKey ( $name, $table, $columns ) | ||
$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. |
return | string |
The SQL statement for adding a primary key constraint to an existing table. |
---|
public function addPrimaryKey($name, $table, $columns)
{
if (is_string($columns)) {
$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
}
foreach ($columns as $i => $col) {
$columns[$i] = $this->db->quoteColumnName($col);
}
return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
. $this->db->quoteColumnName($name) . ' PRIMARY KEY ('
. implode(', ', $columns) . ')';
}
Defined in: yii\db\QueryBuilder::addUnique()
Creates a SQL command for adding an unique constraint to an existing table.
public string addUnique ( $name, $table, $columns ) | ||
$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. |
return | string |
The SQL statement for adding an unique constraint to an existing table. |
---|
public function addUnique($name, $table, $columns)
{
if (is_string($columns)) {
$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
}
foreach ($columns as $i => $col) {
$columns[$i] = $this->db->quoteColumnName($col);
}
return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
. $this->db->quoteColumnName($name) . ' UNIQUE ('
. implode(', ', $columns) . ')';
}
Builds a SQL statement for changing the definition of a column.
public string alterColumn ( $table, $column, $type ) | ||
$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'. You can also use PostgreSQL-specific syntax such as |
return | string |
The SQL statement for changing the definition of a column. |
---|
public function alterColumn($table, $column, $type)
{
$columnName = $this->db->quoteColumnName($column);
$tableName = $this->db->quoteTableName($table);
// https://github.com/yiisoft/yii2/issues/4492
// https://www.postgresql.org/docs/9.1/sql-altertable.html
if (preg_match('/^(DROP|SET|RESET)\s+/i', $type)) {
return "ALTER TABLE {$tableName} ALTER COLUMN {$columnName} {$type}";
}
$type = 'TYPE ' . $this->getColumnType($type);
$multiAlterStatement = [];
$constraintPrefix = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column);
if (preg_match('/\s+DEFAULT\s+(["\']?\w*["\']?)/i', $type, $matches)) {
$type = preg_replace('/\s+DEFAULT\s+(["\']?\w*["\']?)/i', '', $type);
$multiAlterStatement[] = "ALTER COLUMN {$columnName} SET DEFAULT {$matches[1]}";
} else {
// safe to drop default even if there was none in the first place
$multiAlterStatement[] = "ALTER COLUMN {$columnName} DROP DEFAULT";
}
$type = preg_replace('/\s+NOT\s+NULL/i', '', $type, -1, $count);
if ($count) {
$multiAlterStatement[] = "ALTER COLUMN {$columnName} SET NOT NULL";
} else {
// remove additional null if any
$type = preg_replace('/\s+NULL/i', '', $type);
// safe to drop not null even if there was none in the first place
$multiAlterStatement[] = "ALTER COLUMN {$columnName} DROP NOT NULL";
}
if (preg_match('/\s+CHECK\s+\((.+)\)/i', $type, $matches)) {
$type = preg_replace('/\s+CHECK\s+\((.+)\)/i', '', $type);
$multiAlterStatement[] = "ADD CONSTRAINT {$constraintPrefix}_check CHECK ({$matches[1]})";
}
$type = preg_replace('/\s+UNIQUE/i', '', $type, -1, $count);
if ($count) {
$multiAlterStatement[] = "ADD UNIQUE ({$columnName})";
}
// add what's left at the beginning
array_unshift($multiAlterStatement, "ALTER COLUMN {$columnName} {$type}");
return 'ALTER TABLE ' . $tableName . ' ' . implode(', ', $multiAlterStatement);
}
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.
public string batchInsert ( $table, $columns, $rows, &$params = [] ) | ||
$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 |
return | string |
The batch INSERT SQL statement |
---|
public function batchInsert($table, $columns, $rows, &$params = [])
{
if (empty($rows)) {
return '';
}
$schema = $this->db->getSchema();
if (($tableSchema = $schema->getTableSchema($table)) !== null) {
$columnSchemas = $tableSchema->columns;
} else {
$columnSchemas = [];
}
$values = [];
foreach ($rows as $row) {
$vs = [];
foreach ($row as $i => $value) {
if (isset($columns[$i], $columnSchemas[$columns[$i]])) {
$value = $columnSchemas[$columns[$i]]->dbTypecast($value);
}
if (is_string($value)) {
$value = $schema->quoteValue($value);
} elseif (is_float($value)) {
// ensure type cast always has . as decimal separator in all locales
$value = StringHelper::floatToString($value);
} elseif ($value === true) {
$value = 'TRUE';
} elseif ($value === false) {
$value = 'FALSE';
} elseif ($value === null) {
$value = 'NULL';
} elseif ($value instanceof ExpressionInterface) {
$value = $this->buildExpression($value, $params);
}
$vs[] = $value;
}
$values[] = '(' . implode(', ', $vs) . ')';
}
if (empty($values)) {
return '';
}
foreach ($columns as $i => $name) {
$columns[$i] = $schema->quoteColumnName($name);
}
return 'INSERT INTO ' . $schema->quoteTableName($table)
. ' (' . implode(', ', $columns) . ') VALUES ' . implode(', ', $values);
}
Defined in: yii\db\QueryBuilder::bindParam()
Helper method to add $value to $params array using PARAM_PREFIX.
public string bindParam ( $value, &$params ) | ||
$value | string|null | |
$params | array |
Passed by reference |
return | string |
The placeholder name in $params array |
---|
public function bindParam($value, &$params)
{
$phName = self::PARAM_PREFIX . count($params);
$params[$phName] = $value;
return $phName;
}
Defined in: yii\db\QueryBuilder::build()
Generates a SELECT SQL statement from a yii\db\Query object.
public array build ( $query, $params = [] ) | ||
$query | yii\db\Query |
The yii\db\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. |
return | 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 |
---|
public function build($query, $params = [])
{
$query = $query->prepare($this);
$params = empty($params) ? $query->params : array_merge($params, $query->params);
$clauses = [
$this->buildSelect($query->select, $params, $query->distinct, $query->selectOption),
$this->buildFrom($query->from, $params),
$this->buildJoin($query->join, $params),
$this->buildWhere($query->where, $params),
$this->buildGroupBy($query->groupBy),
$this->buildHaving($query->having, $params),
];
$sql = implode($this->separator, array_filter($clauses));
$sql = $this->buildOrderByAndLimit($sql, $query->orderBy, $query->limit, $query->offset);
if (!empty($query->orderBy)) {
foreach ($query->orderBy as $expression) {
if ($expression instanceof ExpressionInterface) {
$this->buildExpression($expression, $params);
}
}
}
if (!empty($query->groupBy)) {
foreach ($query->groupBy as $expression) {
if ($expression instanceof ExpressionInterface) {
$this->buildExpression($expression, $params);
}
}
}
$union = $this->buildUnion($query->union, $params);
if ($union !== '') {
$sql = "($sql){$this->separator}$union";
}
$with = $this->buildWithQueries($query->withQueries, $params);
if ($with !== '') {
$sql = "$with{$this->separator}$sql";
}
return [$sql, $params];
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildAndCondition()
Connects two or more SQL expressions with the AND
or OR
operator.
public string buildAndCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use for connecting the given operands |
$operands | array |
The SQL expressions to connect. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|
public function buildAndCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildBetweenCondition()
Creates an SQL expressions with the BETWEEN
operator.
public string buildBetweenCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use (e.g. |
$operands | array |
The first operand is the column name. The second and third operands describe the interval that column value should be in. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\base\InvalidArgumentException |
if wrong number of operands have been given. |
public function buildBetweenCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
Defined in: yii\db\QueryBuilder::buildColumns()
Processes columns and properly quotes them if necessary.
It will join all columns into a string with comma as separators.
public string buildColumns ( $columns ) | ||
$columns | string|array |
The columns to be processed |
return | string |
The processing result |
---|
public function buildColumns($columns)
{
if (!is_array($columns)) {
if (strpos($columns, '(') !== false) {
return $columns;
}
$rawColumns = $columns;
$columns = preg_split('/\s*,\s*/', $columns, -1, PREG_SPLIT_NO_EMPTY);
if ($columns === false) {
throw new InvalidArgumentException("$rawColumns is not valid columns.");
}
}
foreach ($columns as $i => $column) {
if ($column instanceof ExpressionInterface) {
$columns[$i] = $this->buildExpression($column);
} elseif (strpos($column, '(') === false) {
$columns[$i] = $this->db->quoteColumnName($column);
}
}
return implode(', ', $columns);
}
Defined in: yii\db\QueryBuilder::buildCondition()
Parses the condition specification and generates the corresponding SQL expression.
public string buildCondition ( $condition, &$params ) | ||
$condition | string|array|yii\db\ExpressionInterface |
The condition specification. Please refer to yii\db\Query::where() on how to specify a condition. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|
public function buildCondition($condition, &$params)
{
if (is_array($condition)) {
if (empty($condition)) {
return '';
}
$condition = $this->createConditionFromArray($condition);
}
if ($condition instanceof ExpressionInterface) {
return $this->buildExpression($condition, $params);
}
return (string)$condition;
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildExistsCondition()
Creates an SQL expressions with the EXISTS
operator.
public string buildExistsCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use (e.g. |
$operands | array |
Contains only one element which is a yii\db\Query object representing the sub-query. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\base\InvalidArgumentException |
if the operand is not a yii\db\Query object. |
public function buildExistsCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
public string buildExpression ( yii\db\ExpressionInterface $expression, &$params = [] ) | ||
$expression | yii\db\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. |
return | 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. |
public function buildExpression(ExpressionInterface $expression, &$params = [])
{
$builder = $this->getExpressionBuilder($expression);
return $builder->build($expression, $params);
}
Defined in: yii\db\QueryBuilder::buildFrom()
public string buildFrom ( $tables, &$params ) | ||
$tables | array | |
$params | array |
The binding parameters to be populated |
return | string |
The FROM clause built from yii\db\Query::$from. |
---|
public function buildFrom($tables, &$params)
{
if (empty($tables)) {
return '';
}
$tables = $this->quoteTableNames($tables, $params);
return 'FROM ' . implode(', ', $tables);
}
Defined in: yii\db\QueryBuilder::buildGroupBy()
public string buildGroupBy ( $columns ) | ||
$columns | array | |
return | string |
The GROUP BY clause |
---|
public function buildGroupBy($columns)
{
if (empty($columns)) {
return '';
}
foreach ($columns as $i => $column) {
if ($column instanceof ExpressionInterface) {
$columns[$i] = $this->buildExpression($column);
} elseif (strpos($column, '(') === false) {
$columns[$i] = $this->db->quoteColumnName($column);
}
}
return 'GROUP BY ' . implode(', ', $columns);
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildHashCondition()
Creates a condition based on column-value pairs.
public string buildHashCondition ( $condition, &$params ) | ||
$condition | array |
The condition specification. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|
public function buildHashCondition($condition, &$params)
{
return $this->buildCondition(new HashCondition($condition), $params);
}
Defined in: yii\db\QueryBuilder::buildHaving()
public string buildHaving ( $condition, &$params ) | ||
$condition | string|array | |
$params | array |
The binding parameters to be populated |
return | string |
The HAVING clause built from yii\db\Query::$having. |
---|
public function buildHaving($condition, &$params)
{
$having = $this->buildCondition($condition, $params);
return $having === '' ? '' : 'HAVING ' . $having;
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildInCondition()
Creates an SQL expressions with the IN
operator.
public string buildInCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use (e.g. |
$operands | array |
The first operand is the column name. If it is an array
a composite IN condition will be generated.
The second operand is an array of values that column value should be among.
If it is an empty array the generated expression will be a |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\db\Exception |
if wrong number of operands have been given. |
public function buildInCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
Defined in: yii\db\QueryBuilder::buildJoin()
public string buildJoin ( $joins, &$params ) | ||
$joins | array | |
$params | array |
The binding parameters to be populated |
return | string |
The JOIN clause built from yii\db\Query::$join. |
---|---|---|
throws | yii\db\Exception |
if the $joins parameter is not in proper format |
public function buildJoin($joins, &$params)
{
if (empty($joins)) {
return '';
}
foreach ($joins as $i => $join) {
if (!is_array($join) || !isset($join[0], $join[1])) {
throw new Exception('A join clause must be specified as an array of join type, join table, and optionally join condition.');
}
// 0:join type, 1:join table, 2:on-condition (optional)
list($joinType, $table) = $join;
$tables = $this->quoteTableNames((array)$table, $params);
$table = reset($tables);
$joins[$i] = "$joinType $table";
if (isset($join[2])) {
$condition = $this->buildCondition($join[2], $params);
if ($condition !== '') {
$joins[$i] .= ' ON ' . $condition;
}
}
}
return implode($this->separator, $joins);
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildLikeCondition()
Creates an SQL expressions with the LIKE
operator.
public string buildLikeCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use (e.g. |
$operands | array |
An array of two or three operands
|
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\base\InvalidArgumentException |
if wrong number of operands have been given. |
public function buildLikeCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
Defined in: yii\db\QueryBuilder::buildLimit()
public string buildLimit ( $limit, $offset ) | ||
$limit | integer | |
$offset | integer | |
return | string |
The LIMIT and OFFSET clauses |
---|
public function buildLimit($limit, $offset)
{
$sql = '';
if ($this->hasLimit($limit)) {
$sql = 'LIMIT ' . $limit;
}
if ($this->hasOffset($offset)) {
$sql .= ' OFFSET ' . $offset;
}
return ltrim($sql);
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildNotCondition()
Inverts an SQL expressions with NOT
operator.
public string buildNotCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use for connecting the given operands |
$operands | array |
The SQL expressions to connect. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\base\InvalidArgumentException |
if wrong number of operands have been given. |
public function buildNotCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
Defined in: yii\db\QueryBuilder::buildOrderBy()
public string buildOrderBy ( $columns ) | ||
$columns | array | |
return | string |
The ORDER BY clause built from yii\db\Query::$orderBy. |
---|
public function buildOrderBy($columns)
{
if (empty($columns)) {
return '';
}
$orders = [];
foreach ($columns as $name => $direction) {
if ($direction instanceof ExpressionInterface) {
$orders[] = $this->buildExpression($direction);
} else {
$orders[] = $this->db->quoteColumnName($name) . ($direction === SORT_DESC ? ' DESC' : '');
}
}
return 'ORDER BY ' . implode(', ', $orders);
}
Defined in: yii\db\QueryBuilder::buildOrderByAndLimit()
Builds the ORDER BY and LIMIT/OFFSET clauses and appends them to the given SQL.
public string buildOrderByAndLimit ( $sql, $orderBy, $limit, $offset ) | ||
$sql | string |
The existing SQL (without ORDER BY/LIMIT/OFFSET) |
$orderBy | array |
The order by columns. See yii\db\Query::orderBy() for more details on how to specify this parameter. |
$limit | integer |
The limit number. See yii\db\Query::limit() for more details. |
$offset | integer |
The offset number. See yii\db\Query::offset() for more details. |
return | string |
The SQL completed with ORDER BY/LIMIT/OFFSET (if any) |
---|
public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
$orderBy = $this->buildOrderBy($orderBy);
if ($orderBy !== '') {
$sql .= $this->separator . $orderBy;
}
$limit = $this->buildLimit($limit, $offset);
if ($limit !== '') {
$sql .= $this->separator . $limit;
}
return $sql;
}
Defined in: yii\db\QueryBuilder::buildSelect()
public string buildSelect ( $columns, &$params, $distinct = false, $selectOption = null ) | ||
$columns | array | |
$params | array |
The binding parameters to be populated |
$distinct | boolean | |
$selectOption | string|null | |
return | string |
The SELECT clause built from yii\db\Query::$select. |
---|
public function buildSelect($columns, &$params, $distinct = false, $selectOption = null)
{
$select = $distinct ? 'SELECT DISTINCT' : 'SELECT';
if ($selectOption !== null) {
$select .= ' ' . $selectOption;
}
if (empty($columns)) {
return $select . ' *';
}
foreach ($columns as $i => $column) {
if ($column instanceof ExpressionInterface) {
if (is_int($i)) {
$columns[$i] = $this->buildExpression($column, $params);
} else {
$columns[$i] = $this->buildExpression($column, $params) . ' AS ' . $this->db->quoteColumnName($i);
}
} elseif ($column instanceof Query) {
list($sql, $params) = $this->build($column, $params);
$columns[$i] = "($sql) AS " . $this->db->quoteColumnName($i);
} elseif (is_string($i) && $i !== $column) {
if (strpos($column, '(') === false) {
$column = $this->db->quoteColumnName($column);
}
$columns[$i] = "$column AS " . $this->db->quoteColumnName($i);
} elseif (strpos($column, '(') === false) {
if (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $column, $matches)) {
$columns[$i] = $this->db->quoteColumnName($matches[1]) . ' AS ' . $this->db->quoteColumnName($matches[2]);
} else {
$columns[$i] = $this->db->quoteColumnName($column);
}
}
}
return $select . ' ' . implode(', ', $columns);
}
buildCondition()
instead.
Defined in: yii\db\QueryBuilder::buildSimpleCondition()
Creates an SQL expressions like "column" operator value
.
public string buildSimpleCondition ( $operator, $operands, &$params ) | ||
$operator | string |
The operator to use. Anything could be used e.g. |
$operands | array |
Contains two column names. |
$params | array |
The binding parameters to be populated |
return | string |
The generated SQL expression |
---|---|---|
throws | yii\base\InvalidArgumentException |
if wrong number of operands have been given. |
public function buildSimpleCondition($operator, $operands, &$params)
{
array_unshift($operands, $operator);
return $this->buildCondition($operands, $params);
}
Defined in: yii\db\QueryBuilder::buildUnion()
public string buildUnion ( $unions, &$params ) | ||
$unions | array | |
$params | array |
The binding parameters to be populated |
return | string |
The UNION clause built from yii\db\Query::$union. |
---|
public function buildUnion($unions, &$params)
{
if (empty($unions)) {
return '';
}
$result = '';
foreach ($unions as $i => $union) {
$query = $union['query'];
if ($query instanceof Query) {
list($unions[$i]['query'], $params) = $this->build($query, $params);
}
$result .= 'UNION ' . ($union['all'] ? 'ALL ' : '') . '( ' . $unions[$i]['query'] . ' ) ';
}
return trim($result);
}
Defined in: yii\db\QueryBuilder::buildWhere()
public string buildWhere ( $condition, &$params ) | ||
$condition | string|array | |
$params | array |
The binding parameters to be populated |
return | string |
The WHERE clause built from yii\db\Query::$where. |
---|
public function buildWhere($condition, &$params)
{
$where = $this->buildCondition($condition, $params);
return $where === '' ? '' : 'WHERE ' . $where;
}
public string buildWithQueries ( $withs, &$params ) | ||
$withs | array |
Of configurations for each WITH query |
$params | array |
The binding parameters to be populated |
return | string |
Compiled WITH prefix of query including nested queries |
---|
public function buildWithQueries($withs, &$params)
{
if (empty($withs)) {
return '';
}
$recursive = false;
$result = [];
foreach ($withs as $i => $with) {
if ($with['recursive']) {
$recursive = true;
}
$query = $with['query'];
if ($query instanceof Query) {
list($with['query'], $params) = $this->build($query, $params);
}
$result[] = $with['alias'] . ' AS (' . $with['query'] . ')';
}
return 'WITH ' . ($recursive ? 'RECURSIVE ' : '') . implode(', ', $result);
}
Defined in: yii\base\BaseObject::canGetProperty()
Returns a value indicating whether a property can be read.
A property is readable if:
- the class has a getter method associated with the specified name (in this case, property name is case-insensitive);
- the class has a member variable with the specified name (when
$checkVars
is true);
See also canSetProperty().
public boolean canGetProperty ( $name, $checkVars = true ) | ||
$name | string |
The property name |
$checkVars | boolean |
Whether to treat member variables as properties |
return | boolean |
Whether the property can be read |
---|
public function canGetProperty($name, $checkVars = true)
{
return method_exists($this, 'get' . $name) || $checkVars && property_exists($this, $name);
}
Defined in: yii\base\BaseObject::canSetProperty()
Returns a value indicating whether a property can be set.
A property is writable if:
- the class has a setter method associated with the specified name (in this case, property name is case-insensitive);
- the class has a member variable with the specified name (when
$checkVars
is true);
See also canGetProperty().
public boolean canSetProperty ( $name, $checkVars = true ) | ||
$name | string |
The property name |
$checkVars | boolean |
Whether to treat member variables as properties |
return | boolean |
Whether the property can be written |
---|
public function canSetProperty($name, $checkVars = true)
{
return method_exists($this, 'set' . $name) || $checkVars && property_exists($this, $name);
}
Builds a SQL statement for enabling or disabling integrity check.
public string checkIntegrity ( $check = true, $schema = '', $table = '' ) | ||
$check | boolean |
Whether to turn on or off the integrity check. |
$schema | string |
The schema of the tables. |
$table | string |
The table name. |
return | string |
The SQL statement for checking integrity |
---|
public function checkIntegrity($check = true, $schema = '', $table = '')
{
$enable = $check ? 'ENABLE' : 'DISABLE';
$schema = $schema ?: $this->db->getSchema()->defaultSchema;
$tableNames = $table ? [$table] : $this->db->getSchema()->getTableNames($schema);
$viewNames = $this->db->getSchema()->getViewNames($schema);
$tableNames = array_diff($tableNames, $viewNames);
$command = '';
foreach ($tableNames as $tableName) {
$tableName = $this->db->quoteTableName("{$schema}.{$tableName}");
$command .= "ALTER TABLE $tableName $enable TRIGGER ALL; ";
}
// enable to have ability to alter several tables
$this->db->getMasterPdo()->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true);
return $command;
}
::class
instead.
Defined in: yii\base\BaseObject::className()
Returns the fully qualified name of this class.
public static string className ( ) | ||
return | string |
The fully qualified name of this class. |
---|
public static function className()
{
return get_called_class();
}
Defined in: yii\db\QueryBuilder::createConditionFromArray()
Transforms $condition defined in array format (as described in yii\db\Query::where() to instance of yii\db\condition\ConditionInterface according to $conditionClasses map.
See also $conditionClasses.
public yii\db\conditions\ConditionInterface createConditionFromArray ( $condition ) | ||
$condition | string|array |
public function createConditionFromArray($condition)
{
if (isset($condition[0])) { // operator format: operator, operand 1, operand 2, ...
$operator = strtoupper(array_shift($condition));
if (isset($this->conditionClasses[$operator])) {
$className = $this->conditionClasses[$operator];
} else {
$className = 'yii\db\conditions\SimpleCondition';
}
/** @var ConditionInterface $className */
return $className::fromArrayDefinition($operator, $condition);
}
// hash format: 'column1' => 'value1', 'column2' => 'value2', ...
return new HashCondition($condition);
}
Builds a SQL statement for creating a new index.
See also https://www.postgresql.org/docs/8.2/sql-createindex.html.
public string createIndex ( $name, $table, $columns, $unique = false ) | ||
$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 | boolean|string |
Whether to make this a UNIQUE index constraint. You can pass |
return | string |
The SQL statement for creating a new index. |
---|
public function createIndex($name, $table, $columns, $unique = false)
{
if ($unique === self::INDEX_UNIQUE || $unique === true) {
$index = false;
$unique = true;
} else {
$index = $unique;
$unique = false;
}
return ($unique ? 'CREATE UNIQUE INDEX ' : 'CREATE INDEX ') .
$this->db->quoteTableName($name) . ' ON ' .
$this->db->quoteTableName($table) .
($index !== false ? " USING $index" : '') .
' (' . $this->buildColumns($columns) . ')';
}
Defined in: yii\db\QueryBuilder::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 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
]);
public string createTable ( $table, $columns, $options = null ) | ||
$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. |
return | string |
The SQL statement for creating a new DB table. |
---|
public function createTable($table, $columns, $options = null)
{
$cols = [];
foreach ($columns as $name => $type) {
if (is_string($name)) {
$cols[] = "\t" . $this->db->quoteColumnName($name) . ' ' . $this->getColumnType($type);
} else {
$cols[] = "\t" . $type;
}
}
$sql = 'CREATE TABLE ' . $this->db->quoteTableName($table) . " (\n" . implode(",\n", $cols) . "\n)";
return $options === null ? $sql : $sql . ' ' . $options;
}
Defined in: yii\db\QueryBuilder::createView()
Creates a SQL View.
public string createView ( $viewName, $subQuery ) | ||
$viewName | string |
The name of the view to be created. |
$subQuery | string|yii\db\Query |
The select statement which defines the view. This can be either a string or a yii\db\Query object. |
return | string |
The |
---|
public function createView($viewName, $subQuery)
{
if ($subQuery instanceof Query) {
list($rawQuery, $params) = $this->build($subQuery);
array_walk(
$params,
function (&$param) {
$param = $this->db->quoteValue($param);
}
);
$subQuery = strtr($rawQuery, $params);
}
return 'CREATE VIEW ' . $this->db->quoteTableName($viewName) . ' AS ' . $subQuery;
}
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.
protected array defaultConditionClasses ( ) |
protected function defaultConditionClasses()
{
return array_merge(parent::defaultConditionClasses(), [
'ILIKE' => 'yii\db\conditions\LikeCondition',
'NOT ILIKE' => 'yii\db\conditions\LikeCondition',
'OR ILIKE' => 'yii\db\conditions\LikeCondition',
'OR NOT ILIKE' => 'yii\db\conditions\LikeCondition',
]);
}
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.
protected array defaultExpressionBuilders ( ) |
protected function defaultExpressionBuilders()
{
return array_merge(parent::defaultExpressionBuilders(), [
'yii\db\ArrayExpression' => 'yii\db\pgsql\ArrayExpressionBuilder',
'yii\db\JsonExpression' => 'yii\db\pgsql\JsonExpressionBuilder',
]);
}
Defined in: yii\db\QueryBuilder::delete()
Creates a DELETE SQL statement.
For example,
$sql = $queryBuilder->delete('user', 'status = 0');
The method will properly escape the table and column names.
public string delete ( $table, $condition, &$params ) | ||
$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 yii\db\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. |
return | string |
The DELETE SQL |
---|
public function delete($table, $condition, &$params)
{
$sql = 'DELETE FROM ' . $this->db->quoteTableName($table);
$where = $this->buildWhere($condition, $params);
return $where === '' ? $sql : $sql . ' ' . $where;
}
Defined in: yii\db\QueryBuilder::dropCheck()
Creates a SQL command for dropping a check constraint.
public string dropCheck ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for dropping a check constraint. |
---|
public function dropCheck($name, $table)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
}
Defined in: yii\db\QueryBuilder::dropColumn()
Builds a SQL statement for dropping a DB column.
public string dropColumn ( $table, $column ) | ||
$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. |
return | string |
The SQL statement for dropping a DB column. |
---|
public function dropColumn($table, $column)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' DROP COLUMN ' . $this->db->quoteColumnName($column);
}
Defined in: yii\db\QueryBuilder::dropCommentFromColumn()
Builds a SQL command for adding comment to column.
public string dropCommentFromColumn ( $table, $column ) | ||
$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. |
return | string |
The SQL statement for adding comment on column |
---|
public function dropCommentFromColumn($table, $column)
{
return 'COMMENT ON COLUMN ' . $this->db->quoteTableName($table) . '.' . $this->db->quoteColumnName($column) . ' IS NULL';
}
Defined in: yii\db\QueryBuilder::dropCommentFromTable()
Builds a SQL command for adding comment to table.
public string dropCommentFromTable ( $table ) | ||
$table | string |
The table whose column is to be commented. The table name will be properly quoted by the method. |
return | string |
The SQL statement for adding comment on column |
---|
public function dropCommentFromTable($table)
{
return 'COMMENT ON TABLE ' . $this->db->quoteTableName($table) . ' IS NULL';
}
Defined in: yii\db\QueryBuilder::dropDefaultValue()
Creates a SQL command for dropping a default value constraint.
public string dropDefaultValue ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for dropping a default value constraint. |
---|---|---|
throws | yii\base\NotSupportedException |
if this is not supported by the underlying DBMS. |
public function dropDefaultValue($name, $table)
{
throw new NotSupportedException($this->db->getDriverName() . ' does not support dropping default value constraints.');
}
Defined in: yii\db\QueryBuilder::dropForeignKey()
Builds a SQL statement for dropping a foreign key constraint.
public string dropForeignKey ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for dropping a foreign key constraint. |
---|
public function dropForeignKey($name, $table)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
}
Builds a SQL statement for dropping an index.
public string dropIndex ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for dropping an index. |
---|
public function dropIndex($name, $table)
{
if (strpos($table, '.') !== false && strpos($name, '.') === false) {
if (strpos($table, '{{') !== false) {
$table = preg_replace('/\\{\\{(.*?)\\}\\}/', '\1', $table);
list($schema, $table) = explode('.', $table);
if (strpos($schema, '%') === false) {
$name = $schema . '.' . $name;
} else {
$name = '{{' . $schema . '.' . $name . '}}';
}
} else {
list($schema) = explode('.', $table);
$name = $schema . '.' . $name;
}
}
return 'DROP INDEX ' . $this->db->quoteTableName($name);
}
Defined in: yii\db\QueryBuilder::dropPrimaryKey()
Builds a SQL statement for removing a primary key constraint to an existing table.
public string dropPrimaryKey ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for removing a primary key constraint from an existing table. |
---|
public function dropPrimaryKey($name, $table)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
}
Defined in: yii\db\QueryBuilder::dropTable()
Builds a SQL statement for dropping a DB table.
public string dropTable ( $table ) | ||
$table | string |
The table to be dropped. The name will be properly quoted by the method. |
return | string |
The SQL statement for dropping a DB table. |
---|
public function dropTable($table)
{
return 'DROP TABLE ' . $this->db->quoteTableName($table);
}
Defined in: yii\db\QueryBuilder::dropUnique()
Creates a SQL command for dropping an unique constraint.
public string dropUnique ( $name, $table ) | ||
$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. |
return | string |
The SQL statement for dropping an unique constraint. |
---|
public function dropUnique($name, $table)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
}
Defined in: yii\db\QueryBuilder::dropView()
Drops a SQL View.
public string dropView ( $viewName ) | ||
$viewName | string |
The name of the view to be dropped. |
return | string |
The |
---|
public function dropView($viewName)
{
return 'DROP VIEW ' . $this->db->quoteTableName($viewName);
}
Defined in: yii\db\QueryBuilder::executeResetSequence()
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.
public void executeResetSequence ( $table, $value = null ) | ||
$table | string |
The name of the table whose primary key sequence is reset |
$value | array|string|null |
The 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. |
throws | yii\base\NotSupportedException |
if this is not supported by the underlying DBMS |
---|
public function executeResetSequence($table, $value = null)
{
$this->db->createCommand()->resetSequence($table, $value)->execute();
}
Defined in: yii\db\QueryBuilder::extractAlias()
Extracts table alias if there is one or returns false
protected boolean|array extractAlias ( $table ) | ||
$table |
protected function extractAlias($table)
{
if (preg_match('/^(.*?)(?i:\s+as|)\s+([^ ]+)$/', $table, $matches)) {
return $matches;
}
return false;
}
Defined in: yii\db\QueryBuilder::getColumnType()
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.
public string getColumnType ( $type ) | ||
$type | string|yii\db\ColumnSchemaBuilder |
Abstract column type |
return | string |
Physical column type. |
---|
public function getColumnType($type)
{
if ($type instanceof ColumnSchemaBuilder) {
$type = $type->__toString();
}
if (isset($this->typeMap[$type])) {
return $this->typeMap[$type];
} elseif (preg_match('/^(\w+)\((.+?)\)(.*)$/', $type, $matches)) {
if (isset($this->typeMap[$matches[1]])) {
return preg_replace('/\(.+\)/', '(' . $matches[2] . ')', $this->typeMap[$matches[1]]) . $matches[3];
}
} elseif (preg_match('/^(\w+)\s+/', $type, $matches)) {
if (isset($this->typeMap[$matches[1]])) {
return preg_replace('/^\w+/', $this->typeMap[$matches[1]], $type);
}
}
return $type;
}
Defined in: yii\db\QueryBuilder::getExpressionBuilder()
Gets object of yii\db\ExpressionBuilderInterface that is suitable for $expression.
Uses $expressionBuilders array to find a suitable builder class.
See also $expressionBuilders.
public yii\db\ExpressionBuilderInterface getExpressionBuilder ( yii\db\ExpressionInterface $expression ) | ||
$expression | yii\db\ExpressionInterface | |
throws | yii\base\InvalidArgumentException |
when $expression building is not supported by this QueryBuilder. |
---|
public function getExpressionBuilder(ExpressionInterface $expression)
{
$className = get_class($expression);
if (!isset($this->expressionBuilders[$className])) {
foreach (array_reverse($this->expressionBuilders) as $expressionClass => $builderClass) {
if (is_subclass_of($expression, $expressionClass)) {
$this->expressionBuilders[$className] = $builderClass;
break;
}
}
if (!isset($this->expressionBuilders[$className])) {
throw new InvalidArgumentException('Expression of class ' . $className . ' can not be built in ' . get_class($this));
}
}
if ($this->expressionBuilders[$className] === __CLASS__) {
return $this;
}
if (!is_object($this->expressionBuilders[$className])) {
$this->expressionBuilders[$className] = new $this->expressionBuilders[$className]($this);
}
return $this->expressionBuilders[$className];
}
Defined in: yii\db\QueryBuilder::hasLimit()
Checks to see if the given limit is effective.
protected boolean hasLimit ( $limit ) | ||
$limit | mixed |
The given limit |
return | boolean |
Whether the limit is effective |
---|
protected function hasLimit($limit)
{
return ($limit instanceof ExpressionInterface) || ctype_digit((string)$limit);
}
Defined in: yii\base\BaseObject::hasMethod()
Returns a value indicating whether a method is defined.
The default implementation is a call to php function method_exists()
.
You may override this method when you implemented the php magic method __call()
.
public boolean hasMethod ( $name ) | ||
$name | string |
The method name |
return | boolean |
Whether the method is defined |
---|
public function hasMethod($name)
{
return method_exists($this, $name);
}
Defined in: yii\db\QueryBuilder::hasOffset()
Checks to see if the given offset is effective.
protected boolean hasOffset ( $offset ) | ||
$offset | mixed |
The given offset |
return | boolean |
Whether the offset is effective |
---|
protected function hasOffset($offset)
{
return ($offset instanceof ExpressionInterface) || ctype_digit((string)$offset) && (string)$offset !== '0';
}
Defined in: yii\base\BaseObject::hasProperty()
Returns a value indicating whether a property is defined.
A property is defined if:
- the class has a getter or setter method associated with the specified name (in this case, property name is case-insensitive);
- the class has a member variable with the specified name (when
$checkVars
is true);
See also:
public boolean hasProperty ( $name, $checkVars = true ) | ||
$name | string |
The property name |
$checkVars | boolean |
Whether to treat member variables as properties |
return | boolean |
Whether the property is defined |
---|
public function hasProperty($name, $checkVars = true)
{
return $this->canGetProperty($name, $checkVars) || $this->canSetProperty($name, false);
}
Defined in: yii\db\QueryBuilder::init()
Initializes the object.
This method is invoked at the end of the constructor after the object is initialized with the given configuration.
public void init ( ) |
public function init()
{
parent::init();
$this->expressionBuilders = array_merge($this->defaultExpressionBuilders(), $this->expressionBuilders);
$this->conditionClasses = array_merge($this->defaultConditionClasses(), $this->conditionClasses);
}
Creates an INSERT SQL statement.
For example,
`
php
$sql = $queryBuilder->insert('user', [
'name' => 'Sam',
'age' => 30,
], $params);
`
The method will properly escape the table and column names.
public string insert ( $table, $columns, &$params ) | ||
$table | string |
The table that new rows will be inserted into. |
$columns | array|yii\db\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. |
return | string |
The INSERT SQL |
---|
public function insert($table, $columns, &$params)
{
return parent::insert($table, $this->normalizeTableRowData($table, $columns), $params);
}
Defined in: yii\db\QueryBuilder::prepareInsertSelectSubQuery()
Prepare select-subquery and field names for INSERT INTO ... SELECT SQL statement.
protected array prepareInsertSelectSubQuery ( $columns, $schema, $params = [] ) | ||
$columns | yii\db\Query |
Object, which represents select query. |
$schema | yii\db\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. |
return | array |
Array of column names, values and params. |
---|---|---|
throws | yii\base\InvalidArgumentException |
if query's select does not contain named parameters only. |
protected function prepareInsertSelectSubQuery($columns, $schema, $params = [])
{
if (!is_array($columns->select) || empty($columns->select) || in_array('*', $columns->select)) {
throw new InvalidArgumentException('Expected select query object with enumerated (named) parameters');
}
list($values, $params) = $this->build($columns, $params);
$names = [];
$values = ' ' . $values;
foreach ($columns->select as $title => $field) {
if (is_string($title)) {
$names[] = $schema->quoteColumnName($title);
} elseif (preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $field, $matches)) {
$names[] = $schema->quoteColumnName($matches[2]);
} else {
$names[] = $schema->quoteColumnName($field);
}
}
return [$names, $values, $params];
}
Defined in: yii\db\QueryBuilder::prepareInsertValues()
Prepares a VALUES
part for an INSERT
SQL statement.
protected array prepareInsertValues ( $table, $columns, $params = [] ) | ||
$table | string |
The table that new rows will be inserted into. |
$columns | array|yii\db\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. |
return | array |
Array of column names, placeholders, values and params. |
---|
protected function prepareInsertValues($table, $columns, $params = [])
{
$schema = $this->db->getSchema();
$tableSchema = $schema->getTableSchema($table);
$columnSchemas = $tableSchema !== null ? $tableSchema->columns : [];
$names = [];
$placeholders = [];
$values = ' DEFAULT VALUES';
if ($columns instanceof Query) {
list($names, $values, $params) = $this->prepareInsertSelectSubQuery($columns, $schema, $params);
} else {
foreach ($columns as $name => $value) {
$names[] = $schema->quoteColumnName($name);
$value = isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
if ($value instanceof ExpressionInterface) {
$placeholders[] = $this->buildExpression($value, $params);
} elseif ($value instanceof \yii\db\Query) {
list($sql, $params) = $this->build($value, $params);
$placeholders[] = "($sql)";
} else {
$placeholders[] = $this->bindParam($value, $params);
}
}
}
return [$names, $placeholders, $values, $params];
}
Defined in: yii\db\QueryBuilder::prepareUpdateSets()
Prepares a SET
parts for an UPDATE
SQL statement.
protected array prepareUpdateSets ( $table, $columns, $params = [] ) | ||
$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. |
return | array |
An array |
---|
protected function prepareUpdateSets($table, $columns, $params = [])
{
$tableSchema = $this->db->getTableSchema($table);
$columnSchemas = $tableSchema !== null ? $tableSchema->columns : [];
$sets = [];
foreach ($columns as $name => $value) {
$value = isset($columnSchemas[$name]) ? $columnSchemas[$name]->dbTypecast($value) : $value;
if ($value instanceof ExpressionInterface) {
$placeholder = $this->buildExpression($value, $params);
} else {
$placeholder = $this->bindParam($value, $params);
}
$sets[] = $this->db->quoteColumnName($name) . '=' . $placeholder;
}
return [$sets, $params];
}
Defined in: yii\db\QueryBuilder::prepareUpsertColumns()
protected array prepareUpsertColumns ( $table, $insertColumns, $updateColumns, &$constraints = [] ) | ||
$table | string | |
$insertColumns | array|yii\db\Query | |
$updateColumns | array|boolean | |
$constraints | yii\db\Constraint[] |
This parameter recieves a matched constraint list. The constraints will be unique by their column names. |
protected function prepareUpsertColumns($table, $insertColumns, $updateColumns, &$constraints = [])
{
if ($insertColumns instanceof Query) {
list($insertNames) = $this->prepareInsertSelectSubQuery($insertColumns, $this->db->getSchema());
} else {
$insertNames = array_map([$this->db, 'quoteColumnName'], array_keys($insertColumns));
}
$uniqueNames = $this->getTableUniqueColumnNames($table, $insertNames, $constraints);
$uniqueNames = array_map([$this->db, 'quoteColumnName'], $uniqueNames);
if ($updateColumns !== true) {
return [$uniqueNames, $insertNames, null];
}
return [$uniqueNames, $insertNames, array_diff($insertNames, $uniqueNames)];
}
Defined in: yii\db\QueryBuilder::renameColumn()
Builds a SQL statement for renaming a column.
public string renameColumn ( $table, $oldName, $newName ) | ||
$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. |
return | string |
The SQL statement for renaming a DB column. |
---|
public function renameColumn($table, $oldName, $newName)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($table)
. ' RENAME COLUMN ' . $this->db->quoteColumnName($oldName)
. ' TO ' . $this->db->quoteColumnName($newName);
}
Builds a SQL statement for renaming a DB table.
public string renameTable ( $oldName, $newName ) | ||
$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. |
return | string |
The SQL statement for renaming a DB table. |
---|
public function renameTable($oldName, $newName)
{
return 'ALTER TABLE ' . $this->db->quoteTableName($oldName) . ' RENAME TO ' . $this->db->quoteTableName($newName);
}
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 1.
public string resetSequence ( $tableName, $value = null ) | ||
$tableName | string |
The name of the table whose primary key sequence will be reset |
$value | mixed |
The 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 a value 1. |
return | string |
The SQL statement for resetting sequence |
---|---|---|
throws | yii\base\InvalidArgumentException |
if the table does not exist or there is no sequence associated with the table. |
public function resetSequence($tableName, $value = null)
{
$table = $this->db->getTableSchema($tableName);
if ($table !== null && $table->sequenceName !== null) {
// c.f. https://www.postgresql.org/docs/8.1/functions-sequence.html
$sequence = $this->db->quoteTableName($table->sequenceName);
$tableName = $this->db->quoteTableName($tableName);
if ($value === null) {
$key = $this->db->quoteColumnName(reset($table->primaryKey));
$value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1";
} else {
$value = (int) $value;
}
return "SELECT SETVAL('$sequence',$value,false)";
} elseif ($table === null) {
throw new InvalidArgumentException("Table not found: $tableName");
}
throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
}
Defined in: yii\db\QueryBuilder::selectExists()
Creates a SELECT EXISTS() SQL statement.
public string selectExists ( $rawSql ) | ||
$rawSql | string |
The subquery in a raw form to select from. |
return | string |
The SELECT EXISTS() SQL statement. |
---|
public function selectExists($rawSql)
{
return 'SELECT EXISTS(' . $rawSql . ')';
}
Defined in: yii\db\QueryBuilder::setConditionClasses()
Setter for $conditionClasses property.
See also $conditionClasses.
public void setConditionClasses ( $classes ) | ||
$classes | string[] |
Map of condition aliases to condition classes. For example:
|
public function setConditionClasses($classes)
{
$this->conditionClasses = array_merge($this->conditionClasses, $classes);
}
Defined in: yii\db\QueryBuilder::setExpressionBuilders()
Setter for $expressionBuilders property.
See also $expressionBuilders.
public void setExpressionBuilders ( $builders ) | ||
$builders | string[] |
Array of builders that should be merged with the pre-defined ones in $expressionBuilders property. |
public function setExpressionBuilders($builders)
{
$this->expressionBuilders = array_merge($this->expressionBuilders, $builders);
}
Builds a SQL statement for truncating a DB table.
Explicitly restarts identity for PGSQL to be consistent with other databases which all do this by default.
public string truncateTable ( $table ) | ||
$table | string |
The table to be truncated. The name will be properly quoted by the method. |
return | string |
The SQL statement for truncating a DB table. |
---|
public function truncateTable($table)
{
return 'TRUNCATE TABLE ' . $this->db->quoteTableName($table) . ' RESTART IDENTITY';
}
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.
public string update ( $table, $columns, $condition, &$params ) | ||
$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 yii\db\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. |
return | string |
The UPDATE SQL |
---|
public function update($table, $columns, $condition, &$params)
{
return parent::update($table, $this->normalizeTableRowData($table, $columns), $condition, $params);
}
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.
See also:
public string upsert ( $table, $insertColumns, $updateColumns, &$params ) | ||
$table | string |
The table that new rows will be inserted into/updated in. |
$insertColumns | array|yii\db\Query |
The column data (name => value) to be inserted into the table or instance
of yii\db\Query to perform |
$updateColumns | array|boolean |
The column data (name => value) to be updated if they already exist.
If |
$params | array |
The binding parameters that will be generated by this method. They should be bound to the DB command later. |
return | string |
The resulting SQL. |
---|---|---|
throws | yii\base\NotSupportedException |
if this is not supported by the underlying DBMS. |
public function upsert($table, $insertColumns, $updateColumns, &$params)
{
$insertColumns = $this->normalizeTableRowData($table, $insertColumns);
if (!is_bool($updateColumns)) {
$updateColumns = $this->normalizeTableRowData($table, $updateColumns);
}
if (version_compare($this->db->getServerVersion(), '9.5', '<')) {
return $this->oldUpsert($table, $insertColumns, $updateColumns, $params);
}
return $this->newUpsert($table, $insertColumns, $updateColumns, $params);
}
Signup or Login in order to comment.