Craft 5 Class Reference

Appearance

Db ​

Type
Class
Namespace
craft\helpers
Inherits
craft\helpers\Db
Since
3.0.0

Class Db

View source

Public Methods ​

MethodDescription
areColumnTypesCompatible()Returns whether two column type definitions are relatively compatible with each other.
batch()Starts a batch query, similar to yii\db\Query::batch().
batchInsert()Creates and executes a batch INSERT SQL statement.
defaultCollation()Returns the default collation that should be used, based on the current MySQL version.
delete()Creates and executes a DELETE SQL statement.
deleteIfExists()Creates and executes a DELETE SQL statement, but only if there are any rows to delete, avoiding deadlock issues when deleting data from large tables.
dropAllForeignKeysToTable()Drops all the foreign keys that reference a table.
dropForeignKeyIfExists()Creates and executes a SQL statement for dropping a foreign key if it exists.
dropIndexIfExists()Creates and executes a SQL statement for dropping an index if it exists.
each()Starts a batch query and retrieves data row by row.
escapeCommas()Escapes commas in a string so the value doesn’t get interpreted as an array by parseParam().
escapeForLike()Escapes underscores within a value for a LIKE condition.
escapeParam()Escapes commas, asterisks, and colons in a string, so they are not treated as special characters in parseParam().
findForeignKey()Looks for a foreign key on the given table with the given columns, and returns its name, or null if no match is found.
findIndex()Looks for an index on the given table with the given columns and unique property, and returns its name, or null if no match is found.
getMaxAllowedValueForNumericColumn()Returns the maximum number allowed for a given column type.
getMinAllowedValueForNumericColumn()Returns the minimum number allowed for a given column type.
getNumericalColumnType()Returns a number column type, taking the min, max, and number of decimal points into account.
getSimplifiedColumnType()Returns a simplified version of a given column type.
getTextualColumnStorageCapacity()Returns the maximum number of bytes a given textual column type can hold for a given database.
getTextualColumnTypeByContentLength()Given a length of a piece of content, returns the underlying database column type to use for saving.
idByUid()Returns the id of a row in the given table by its uid.
idsByUids()Returns an array uid:id pairs from a given table, by their uids.
insert()Creates and executes an INSERT SQL statement.
isNumericColumnType()Returns whether the given column type is numeric.
isTextualColumnType()Returns whether the given column type is textual.
isTypeSupported()Returns whether a given DB connection’s schema supports a column type.
normalizeParam()Normalizes a param value with a provided resolver function, unless the resolver function ever returns an empty value.
parseBooleanParam()Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where()-compatible condition.
parseColumnLength()Parses a column type definition and returns just the column length/size.
parseColumnPrecisionAndScale()Parses a decimal column type definition and returns just the column precision and scale.
parseColumnType()Parses a column type definition and returns just the column type, if it can be determined.
parseDateParam()Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where()-compatible condition.
parseDsn()Parses a DSN string and returns an array with the driver and any driver params, or just a single key.
parseMoneyParam()Parses a query param value for a money column, and returns a yii\db\QueryInterface::where()-compatible condition.
parseNumericParam()Parses a query param value for a numeric column and returns a yii\db\QueryInterface::where()-compatible condition.
parseParam()Parses a query param value and returns a yii\db\QueryInterface::where()-compatible condition.
parseTimestampParam()Parses a query param value for a timestamp column and returns a yii\db\QueryInterface::where()-compatible condition.
prepareDateForDb()Prepares a date to be sent to the database.
prepareForJsonColumn()Prepares a value to be stored in a JSON column
prepareMoneyForDb()Prepares a money object to be sent to the database.
prepareValueForDb()Prepares a value to be sent to the database.
prepareValuesForDb()Prepares an array or object’s values to be sent to the database.
rawTableShortName()Returns a table name with curly brackets and percent sign removed.
renameTable()Renames a table and its corresponding sequence (if PostgreSQL).
replace()Creates and executes an SQL statement for replacing some text with other text in a given table column.
reset()Resets the memoized database connection.
supportsTimeZones()Returns whether the database supports time zone conversions.
truncateTable()Creates and executes a TRUNCATE TABLE SQL statement.
uidById()Returns the uid of a row in the given table by its id.
uidsByIds()Returns an array id:uid pairs from a given table, by their ids.
unescapeParam()Unescapes commas, asterisks, and colons added to a string via escapeParam().
update()Creates and executes an UPDATE SQL statement.
upsert()Creates and executes a command to insert rows into a database table if they do not already exist (matching unique constraints), or update them if they do.
url2config()Generates a DB config from a database connection URL.

areColumnTypesCompatible() ​

Returns whether two column type definitions are relatively compatible with each other.

View source

Arguments ​

Returns ​

boolean

batch() ​

Since
3.7.0

Starts a batch query, similar to yii\db\Query::batch().

Each iteration will be a batch of rows.

php
foreach (Db::batch($query) as $batch) {
    foreach ($batch as $row) {
        // ...
    }
}

If using MySQL and $db is null, a new unbuffered DB connection will be created for the query so that the data can actually be retrieved in batches, to work around limitations with query batching in MySQL. Therefore keep in mind that the data retrieved by the batch query won’t reflect any changes that have been made over the main DB connection, if a transaction is currently active.

View source

Arguments ​

Returns ​

yii\db\BatchQueryResult – The batched query to be iterated on

batchInsert() ​

Since
3.5.0

Creates and executes a batch INSERT SQL statement.

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

If the table contains dateCreated, dateUpdated, and/or uid columns, those values will be included automatically, if not already set.

View source

Arguments ​

  • $table (string) – The table that new rows will be inserted into
  • $columns (array) – The column names
  • $rows (array) – The rows to be batch inserted into the table
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

defaultCollation() ​

Since
5.0.0

Returns the default collation that should be used, based on the current MySQL version.

  • If MySQL 8.0+, utf8mb4_0900_ai_ci will be returned.
  • Otherwise, utf8mb4_unicode_ci will be returned.

View source

Arguments ​

delete() ​

Since
3.5.0

Creates and executes a DELETE SQL statement.

View source

Arguments ​

  • $table (string) – The table where the data will be deleted from
  • $condition (string, array) – The conditions that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify conditions.
  • $params (array) – The parameters to be bound to the query.
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

deleteIfExists() ​

Since
3.0.12

Creates and executes a DELETE SQL statement, but only if there are any rows to delete, avoiding deadlock issues when deleting data from large tables.

View source

Arguments ​

  • $table (string) – The table where the data will be deleted from
  • $condition (string, array) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition.
  • $params (array) – The parameters to be bound to the command
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – Number of rows affected by the execution

Throws ​

dropAllForeignKeysToTable() ​

Since
4.0.0

Drops all the foreign keys that reference a table.

View source

Arguments ​

dropForeignKeyIfExists() ​

Since
4.0.0

Creates and executes a SQL statement for dropping a foreign key if it exists.

View source

Arguments ​

  • $table (string) – The table that the foreign key was created for.
  • $columns (string, string[]) – The column(s) that are included in the foreign key. If there are multiple columns, separate them by commas or use an array.
  • $db (craft\db\Connection, null) – The database connection.

dropIndexIfExists() ​

Since
4.0.0

Creates and executes a SQL statement for dropping an index if it exists.

View source

Arguments ​

  • $table (string) – The table that the index was created for.
  • $columns (string, string[]) – The column(s) that are included in the index. If there are multiple columns, separate them by commas or use an array.
  • $unique (boolean) – Whether the index has a UNIQUE constraint.
  • $db (craft\db\Connection, null) – The database connection.

each() ​

Since
3.7.0

Starts a batch query and retrieves data row by row.

This method is similar to batch() except that in each iteration of the result, only one row of data is returned.

php
foreach (Db::each($query) as $row) {
    // ...
}

If using MySQL and $db is null, a new unbuffered DB connection will be created for the query so that the data can actually be retrieved in batches, to work around limitations with query batching in MySQL. Therefore keep in mind that the data retrieved by the batch query won’t reflect any changes that have been made over the main DB connection, if a transaction is currently active.

View source

Arguments ​

Returns ​

yii\db\BatchQueryResult – The batched query to be iterated on

escapeCommas() ​

Since
4.0.0

Escapes commas in a string so the value doesn’t get interpreted as an array by parseParam().

View source

Arguments ​

  • $value (string) – The param value.

Returns ​

string – The escaped param value.

escapeForLike() ​

Since
4.3.7

Escapes underscores within a value for a LIKE condition.

View source

Arguments ​

Returns ​

string – The escaped value

escapeParam() ​

Escapes commas, asterisks, and colons in a string, so they are not treated as special characters in parseParam().

View source

Arguments ​

  • $value (string) – The param value.

Returns ​

string – The escaped param value.

findForeignKey() ​

Since
4.0.0

Looks for a foreign key on the given table with the given columns, and returns its name, or null if no match is found.

View source

Arguments ​

  • $tableName (string) – The table that the foreign key is on
  • $columns (string, string[]) – The column(s) that are included in the foreign key. If there are multiple columns, separate them by commas or use an array.
  • $db (craft\db\Connection, null) – The database connection.

Returns ​

string, null – The foreign key name, or null if there isn’t a matching one.

findIndex() ​

Since
4.0.0

Looks for an index on the given table with the given columns and unique property, and returns its name, or null if no match is found.

View source

Arguments ​

  • $tableName (string) – The table that the index was created for.
  • $columns (string, string[]) – The column(s) that are included in the index. If there are multiple columns, separate them by commas or use an array.
  • $unique (boolean) – Whether the index has a UNIQUE constraint.
  • $db (craft\db\Connection, null) – The database connection.

Returns ​

string, null – The index name, or null if there isn’t a matching one.

getMaxAllowedValueForNumericColumn() ​

Returns the maximum number allowed for a given column type.

View source

Arguments ​

Returns ​

integer, false – The max allowed number, or false if it can't be determined

getMinAllowedValueForNumericColumn() ​

Returns the minimum number allowed for a given column type.

View source

Arguments ​

Returns ​

integer, false – The min allowed number, or false if it can't be determined

getNumericalColumnType() ​

Returns a number column type, taking the min, max, and number of decimal points into account.

View source

Arguments ​

Returns ​

string

Throws ​

getSimplifiedColumnType() ​

Returns a simplified version of a given column type.

View source

Arguments ​

Returns ​

string

getTextualColumnStorageCapacity() ​

Returns the maximum number of bytes a given textual column type can hold for a given database.

View source

Arguments ​

Returns ​

integer, null, false – The storage capacity of the column type in bytes, null if unlimited, or false if it can't be determined.

getTextualColumnTypeByContentLength() ​

Given a length of a piece of content, returns the underlying database column type to use for saving.

View source

Arguments ​

Returns ​

string

Throws ​

idByUid() ​

Since
3.1.0

Returns the id of a row in the given table by its uid.

View source

Arguments ​

Returns ​

integer, null

idsByUids() ​

Since
3.1.0

Returns an array uid:id pairs from a given table, by their uids.

View source

Arguments ​

Returns ​

integer[]

insert() ​

Since
3.5.0

Creates and executes an INSERT SQL statement.

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

If the table contains dateCreated, dateUpdated, and/or uid columns, those values will be included automatically, if not already set.

View source

Arguments ​

  • $table (string) – The table that new rows will be inserted into
  • $columns (array) – The column data (name=>value) to be inserted into the table
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

isNumericColumnType() ​

Returns whether the given column type is numeric.

View source

Arguments ​

Returns ​

boolean

isTextualColumnType() ​

Returns whether the given column type is textual.

View source

Arguments ​

Returns ​

boolean

isTypeSupported() ​

Returns whether a given DB connection’s schema supports a column type.

View source

Arguments ​

Returns ​

boolean

Throws ​

normalizeParam() ​

Since
3.7.40

Normalizes a param value with a provided resolver function, unless the resolver function ever returns an empty value.

If the original param value began with and, or, or not, that will be preserved.

View source

Arguments ​

  • $value (mixed) – The param value to be normalized
  • $resolver (callable) – Method to resolve non-model values to models

Returns ​

boolean – Whether the value was normalized

parseBooleanParam() ​

Since
3.4.14

Parses a query param value for a boolean column and returns a yii\db\QueryInterface::where()-compatible condition.

The follow values are supported:

  • true or false
  • :empty: or :notempty: (normalizes to false and true)
  • 'not x' or '!= x' (normalizes to the opposite of the boolean value of x)
  • Anything else (normalizes to the boolean value of itself)

If $defaultValue is set, and it matches the normalized $value, then the resulting condition will match any null values as well.

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $value (string, boolean) – The param value
  • $defaultValue (boolean, null) – How null values should be treated
  • $columnType (string) – The database column type the param is targeting

Returns ​

array

parseColumnLength() ​

Parses a column type definition and returns just the column length/size.

View source

Arguments ​

Returns ​

integer, null

parseColumnPrecisionAndScale() ​

Since
5.2.2

Parses a decimal column type definition and returns just the column precision and scale.

View source

Arguments ​

Returns ​

array{0: int, 1: int}, null

parseColumnType() ​

Parses a column type definition and returns just the column type, if it can be determined.

View source

Arguments ​

Returns ​

string, null

parseDateParam() ​

Parses a query param value for a date/time column, and returns a yii\db\QueryInterface::where()-compatible condition.

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $value (string, array, DateTimeInterface) – The param value
  • $defaultOperator (string) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)

Returns ​

array, null

parseDsn() ​

Since
3.4.0

Parses a DSN string and returns an array with the driver and any driver params, or just a single key.

View source

Arguments ​

  • $dsn (string)
  • $key (string, null) – The key that is needed from the DSN, if only one param is needed.

Returns ​

array, string, false – The full array, or the specific key value, or false if $key is a param that doesn’t exist in the DSN string.

Throws ​

parseMoneyParam() ​

Since
4.0.0

Parses a query param value for a money column, and returns a yii\db\QueryInterface::where()-compatible condition.

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $currency (string) – The currency code to use for the money object.
  • $value (string, array, \Money\Money) – The param value
  • $defaultOperator (string) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)

Returns ​

array, null

parseNumericParam() ​

Since
4.0.0

Parses a query param value for a numeric column and returns a yii\db\QueryInterface::where()-compatible condition.

The follow values are supported:

  • A number
  • :empty: or :notempty:
  • 'not x' or '!= x'
  • '> x', '>= x', '< x', or '<= x', or a combination of those

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $value (string, string[]) – The param value
  • $defaultOperator (string) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)
  • $columnType (string, null) – The database column type the param is targeting

Returns ​

array, null

Throws ​

parseParam() ​

Parses a query param value and returns a yii\db\QueryInterface::where()-compatible condition.

If the $value is a string, it will automatically be converted to an array, split on any commas within the string (via craft\helpers\ArrayHelper::toArray()). If that is not desired behavior, you can escape the comma with a backslash before it.

The first value can be set to either and, or, or not to define whether all, any, or none of the values must match. (or will be assumed by default.)

Values can begin with the operators 'not ', '!=', '<=', '>=', '<', '>', or '='. If they don’t, '=' will be assumed.

Values can also be set to either ':empty:' or ':notempty:' if you want to search for empty or non-empty database values. (An “empty” value is either NULL or an empty string of text).

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $value (string, integer, array) – The param value(s).
  • $defaultOperator (string) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)
  • $caseInsensitive (boolean) – Whether the resulting condition should be case-insensitive
  • $columnType (string, null) – The database column type the param is targeting

Returns ​

array, null

Throws ​

parseTimestampParam() ​

Since
5.1.0

Parses a query param value for a timestamp column and returns a yii\db\QueryInterface::where()-compatible condition.

The follow values are supported:

  • A number
  • :empty: or :notempty:
  • 'not x' or '!= x'
  • '> x', '>= x', '< x', or '<= x', or a combination of those

View source

Arguments ​

  • $column (string) – The database column that the param is targeting.
  • $value (string, string[]) – The param value
  • $defaultOperator (string) – The default operator to apply to the values (can be not, !=, <=, >=, <, >, or =)

Returns ​

array, null

Throws ​

prepareDateForDb() ​

Prepares a date to be sent to the database.

View source

Arguments ​

  • $date (mixed) – The date to be prepared

Returns ​

string, null – The prepped date, or null if it could not be prepared

prepareForJsonColumn() ​

DEPRECATED

Deprecated in 5.2.3

Since
5.0.0

Prepares a value to be stored in a JSON column

View source

Arguments ​

Returns ​

array, string

prepareMoneyForDb() ​

Since
4.0.0

Prepares a money object to be sent to the database.

View source

Arguments ​

  • $money (mixed) – The money to be prepared

Returns ​

string, null – The prepped date, or null if it could not be prepared

prepareValueForDb() ​

Prepares a value to be sent to the database.

View source

Arguments ​

  • $value (mixed) – The value to be prepared
  • $columnType (string, null) – The type of column the value will be stored in

Returns ​

mixed – The prepped value

prepareValuesForDb() ​

Prepares an array or object’s values to be sent to the database.

View source

Arguments ​

  • $values (mixed) – The values to be prepared

Returns ​

array – The prepared values

rawTableShortName() ​

Since
4.4.0

Returns a table name with curly brackets and percent sign removed.

View source

Arguments ​

Returns ​

string

renameTable() ​

Since
4.0.0

Renames a table and its corresponding sequence (if PostgreSQL).

View source

Arguments ​

replace() ​

Since
3.5.0

Creates and executes an SQL statement for replacing some text with other text in a given table column.

View source

Arguments ​

  • $table (string) – The table to be updated
  • $column (string) – The column to be searched
  • $find (string) – The text to be searched for
  • $replace (string) – The replacement text
  • $condition (string, array) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition.
  • $params (array) – The parameters to be bound to the command
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

reset() ​

Since
3.5.12.1

Resets the memoized database connection.

View source

supportsTimeZones() ​

Since
3.6.16

Returns whether the database supports time zone conversions.

This could return false if MySQL’s time zone tables haven’t been populated yet. See https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html for more info.

View source

Arguments ​

Returns ​

boolean

truncateTable() ​

Since
3.6.8

Creates and executes a TRUNCATE TABLE SQL statement.

View source

Arguments ​

Throws ​

uidById() ​

Since
3.1.0

Returns the uid of a row in the given table by its id.

View source

Arguments ​

Returns ​

string, null

uidsByIds() ​

Since
3.1.0

Returns an array id:uid pairs from a given table, by their ids.

View source

Arguments ​

Returns ​

string[]

unescapeParam() ​

Since
4.4.0

Unescapes commas, asterisks, and colons added to a string via escapeParam().

View source

Arguments ​

  • $value (string) – The param value.

Returns ​

string – The escaped param value.

update() ​

Since
3.5.0

Creates and executes an UPDATE SQL statement.

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

View source

Arguments ​

  • $table (string) – The table to be updated
  • $columns (array) – The column data (name => value) to be updated
  • $condition (string, array) – The condition that will be put in the WHERE part. Please refer to craft\db\Query::where() on how to specify condition
  • $params (array) – The parameters to be bound to the command
  • $updateTimestamp (boolean) – Whether the dateUpdated column should be updated, if the table has one.
  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

upsert() ​

Since
3.5.0

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

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

If the table contains dateCreated, dateUpdated, and/or uid columns, those values will be included for new rows automatically, if not already set.

View source

Arguments ​

  • $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 craft\db\Query to perform INSERT INTO ... SELECT SQL statement

  • $updateColumns (array, boolean) – The column data (name => value) to be updated if they already exist

  • If true is passed, the column data will be updated to match the insert column data.

  • If false is passed, no update will be performed if the column data already exists.

  • $params (array) – The parameters to be bound to the command

  • $updateTimestamp (boolean) – Whether the dateUpdated column should be updated for existing rows, if the table has one.

  • $db (craft\db\Connection, null) – The database connection to use

Returns ​

integer – The number of rows affected by the execution

Throws ​

url2config() ​

Since
3.4.0

Generates a DB config from a database connection URL.

This can be used from config/db.php:

View source

Arguments ​

Returns ​

array

Example ​

php
$url = craft\helpers\App::env('CRAFT_DB_URL');
return craft\helpers\Db::url2config($url);

Constants ​

ConstantDescription
SIMPLE_TYPE_NUMERIC
SIMPLE_TYPE_TEXTUAL