PHP Class yii\db\Query

Query provides a set of methods to facilitate the specification of different clauses in a SELECT statement. These methods can be chained together. By calling Query::createCommand, we can get a Command instance which can be further used to perform/execute the DB query against a database. For example, ~~~ $query = new Query; compose the query $query->select('id, name') ->from('tbl_user') ->limit(10); build and execute the query $rows = $query->all(); alternatively, you can create DB command and execute it $command = $query->createCommand(); $command->sql returns the actual SQL $rows = $command->queryAll(); ~~~
Since: 2.0
Author: Qiang Xue ([email protected])
Author: Carsten Brandt ([email protected])
Inheritance: extends yii\base\Component, implements yii\db\QueryInterface, use trait QueryTrait
Afficher le fichier Open project: yiisoft/yii2 Class Usage Examples

Méthodes publiques

Свойство Type Description
$distinct whether to select distinct rows of data only. If this is set true, the SELECT clause would be changed to SELECT DISTINCT.
$from the table(s) to be selected from. For example, ['user', 'post']. This is used to construct the FROM clause in a SQL statement.
$groupBy how to group the query results. For example, ['company', 'department']. This is used to construct the GROUP BY clause in a SQL statement.
$having the condition to be applied in the GROUP BY clause. It can be either a string or an array. Please refer to Query::where on how to specify the condition.
$join how to join with other tables. Each array element represents the specification of one join which has the following structure: php [$joinType, $tableName, $joinCondition] For example, php [ ['INNER JOIN', 'user', 'user.id = author_id'], ['LEFT JOIN', 'team', 'team.id = team_id'], ]
$params list of query parameter values indexed by parameter placeholders. For example, [':name' => 'Dan', ':age' => 31].
$select the columns being selected. For example, ['id', 'name']. This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns.
$selectOption additional option that should be appended to the 'SELECT' keyword. For example, in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used.
$union this is used to construct the UNION clause(s) in a SQL statement. Each array element is an array of the following structure: - query: either a string or a Query object representing a query - all: boolean, whether it should be UNION ALL or UNION

Méthodes publiques

Méthode Description
addGroupBy ( string | array $columns ) Adds additional group-by columns to the existing ones.
addParams ( array $params ) Adds additional parameters to be bound to the query.
addSelect ( string | array | yii\db\Expression $columns ) Add more columns to the SELECT part of the query.
all ( Connection $db = null ) : array Executes the query and returns all results as an array.
andFilterCompare ( string $name, string $value, string $defaultOperator = '=' ) Adds a filtering condition for a specific column and allow the user to choose a filter operator.
andHaving ( string | array | yii\db\Expression $condition, array $params = [] ) Adds an additional HAVING condition to the existing one.
andWhere ( string | array | yii\db\Expression $condition, array $params = [] ) Adds an additional WHERE condition to the existing one.
average ( string $q, Connection $db = null ) : mixed Returns the average of the specified column values.
batch ( integer $batchSize = 100, Connection $db = null ) : BatchQueryResult Starts a batch query.
column ( Connection $db = null ) : array Executes the query and returns the first column of the result.
count ( string $q = '*', Connection $db = null ) : integer | string Returns the number of records.
create ( Query $from ) : Query Creates a new Query object and copies its property values from an existing one.
createCommand ( Connection $db = null ) : Command Creates a DB command that can be used to execute this query.
distinct ( boolean $value = true ) Sets the value indicating whether to SELECT DISTINCT or not.
each ( integer $batchSize = 100, Connection $db = null ) : BatchQueryResult Starts a batch query and retrieves data row by row.
exists ( Connection $db = null ) : boolean Returns a value indicating whether the query result contains any row of data.
from ( string | array $tables ) Sets the FROM part of the query.
groupBy ( string | array | yii\db\Expression $columns ) Sets the GROUP BY part of the query.
having ( string | array | yii\db\Expression $condition, array $params = [] ) Sets the HAVING part of the query.
innerJoin ( string | array $table, string | array $on = '', array $params = [] ) Appends an INNER JOIN part to the query.
join ( string $type, string | array $table, string | array $on = '', array $params = [] ) Appends a JOIN part to the query.
leftJoin ( string | array $table, string | array $on = '', array $params = [] ) Appends a LEFT OUTER JOIN part to the query.
max ( string $q, Connection $db = null ) : mixed Returns the maximum of the specified column values.
min ( string $q, Connection $db = null ) : mixed Returns the minimum of the specified column values.
one ( Connection $db = null ) : array | boolean Executes the query and returns a single row of result.
orHaving ( string | array | yii\db\Expression $condition, array $params = [] ) Adds an additional HAVING condition to the existing one.
orWhere ( string | array | yii\db\Expression $condition, array $params = [] ) Adds an additional WHERE condition to the existing one.
params ( array $params ) Sets the parameters to be bound to the query.
populate ( array $rows ) : array Converts the raw query results into the format as specified by this query.
prepare ( QueryBuilder $builder ) Prepares for building SQL.
rightJoin ( string | array $table, string | array $on = '', array $params = [] ) Appends a RIGHT OUTER JOIN part to the query.
scalar ( Connection $db = null ) : string | null | false Returns the query result as a scalar value.
select ( string | array | yii\db\Expression $columns, string $option = null ) Sets the SELECT part of the query.
sum ( string $q, Connection $db = null ) : mixed Returns the sum of the specified column values.
union ( string | Query $sql, boolean $all = false ) Appends a SQL statement using UNION operator.
where ( string | array | yii\db\Expression $condition, array $params = [] ) Sets the WHERE part of the query.

Méthodes protégées

Méthode Description
queryScalar ( string | yii\db\Expression $selectExpression, Connection | null $db ) : boolean | string Queries a scalar value by setting [[select]] first.

Method Details

addGroupBy() public méthode

Adds additional group-by columns to the existing ones.
See also: groupBy()
public addGroupBy ( string | array $columns )
$columns string | array additional columns to be grouped by. Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression). Note that if your group-by is an expression containing commas, you should always use an array to represent the group-by information. Otherwise, the method will not be able to correctly determine the group-by columns. Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL.

addParams() public méthode

Adds additional parameters to be bound to the query.
See also: params()
public addParams ( array $params )
$params array list of query parameter values indexed by parameter placeholders. For example, `[':name' => 'Dan', ':age' => 31]`.

addSelect() public méthode

Note, that if [[select]] has not been specified before, you should include * explicitly if you want to select all remaining columns too: php $query->addSelect(["*", "CONCAT(first_name, ' ', last_name) AS full_name"])->one();
See also: select()
public addSelect ( string | array | yii\db\Expression $columns )
$columns string | array | yii\db\Expression the columns to add to the select. See [[select()]] for more details about the format of this parameter.

all() public méthode

Executes the query and returns all results as an array.
public all ( Connection $db = null ) : array
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat array the query results. If the query results in nothing, an empty array will be returned.

andFilterCompare() public méthode

It adds an additional WHERE condition for the given field and determines the comparison operator based on the first few characters of the given value. The condition is added in the same way as in [[andFilterWhere]] so [[isEmpty()|empty values]] are ignored. The new condition and the existing one will be joined using the 'AND' operator. The comparison operator is intelligently determined based on the first few characters in the given value. In particular, it recognizes the following operators if they appear as the leading characters in the given value: - <: the column must be less than the given value. - >: the column must be greater than the given value. - <=: the column must be less than or equal to the given value. - >=: the column must be greater than or equal to the given value. - <>: the column must not be the same as the given value. - =: the column must be equal to the given value. - If none of the above operators is detected, the $defaultOperator will be used.
Since: 2.0.8
public andFilterCompare ( string $name, string $value, string $defaultOperator = '=' )
$name string the column name.
$value string the column value optionally prepended with the comparison operator.
$defaultOperator string The operator to use, when no operator is given in `$value`. Defaults to `=`, performing an exact match.

andHaving() public méthode

The new condition and the existing one will be joined using the 'AND' operator.
See also: having()
See also: orHaving()
public andHaving ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the new HAVING condition. Please refer to [[where()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

andWhere() public méthode

The new condition and the existing one will be joined using the 'AND' operator.
See also: where()
See also: orWhere()
public andWhere ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the new WHERE condition. Please refer to [[where()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

average() public méthode

Returns the average of the specified column values.
public average ( string $q, Connection $db = null ) : mixed
$q string the column name or expression. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression.
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat mixed the average of the specified column values.

batch() public méthode

A batch query supports fetching data in batches, which can keep the memory usage under a limit. This method will return a BatchQueryResult object which implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches. For example, php $query = (new Query)->from('user'); foreach ($query->batch() as $rows) { $rows is an array of 100 or fewer rows from user table }
public batch ( integer $batchSize = 100, Connection $db = null ) : BatchQueryResult
$batchSize integer the number of records to be fetched in each batch.
$db Connection the database connection. If not set, the "db" application component will be used.
Résultat BatchQueryResult the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches.

column() public méthode

Executes the query and returns the first column of the result.
public column ( Connection $db = null ) : array
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat array the first column of the query result. An empty array is returned if the query results in nothing.

count() public méthode

Returns the number of records.
public count ( string $q = '*', Connection $db = null ) : integer | string
$q string the COUNT expression. Defaults to '*'. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression.
$db Connection the database connection used to generate the SQL statement. If this parameter is not given (or null), the `db` application component will be used.
Résultat integer | string number of records. The result may be a string depending on the underlying database engine and to support integer values higher than a 32bit PHP integer can handle.

create() public static méthode

The properties being copies are the ones to be used by query builders.
public static create ( Query $from ) : Query
$from Query the source query object
Résultat Query the new Query object

createCommand() public méthode

Creates a DB command that can be used to execute this query.
public createCommand ( Connection $db = null ) : Command
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat Command the created DB command instance.

distinct() public méthode

Sets the value indicating whether to SELECT DISTINCT or not.
public distinct ( boolean $value = true )
$value boolean whether to SELECT DISTINCT or not.

each() public méthode

This method is similar to Query::batch except that in each iteration of the result, only one row of data is returned. For example, php $query = (new Query)->from('user'); foreach ($query->each() as $row) { }
public each ( integer $batchSize = 100, Connection $db = null ) : BatchQueryResult
$batchSize integer the number of records to be fetched in each batch.
$db Connection the database connection. If not set, the "db" application component will be used.
Résultat BatchQueryResult the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches.

exists() public méthode

Returns a value indicating whether the query result contains any row of data.
public exists ( Connection $db = null ) : boolean
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat boolean whether the query result contains any row of data.

from() public méthode

Sets the FROM part of the query.
public from ( string | array $tables )
$tables string | array the table(s) to be selected from. This can be either a string (e.g. `'user'`) or an array (e.g. `['user', 'profile']`) specifying one or several table names. Table names can contain schema prefixes (e.g. `'public.user'`) and/or table aliases (e.g. `'user u'`). The method will automatically quote the table names unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). When the tables are specified as an array, you may also use the array keys as the table aliases (if a table does not need alias, do not use a string key). Use a Query object to represent a sub-query. In this case, the corresponding array key will be used as the alias for the sub-query. Here are some examples: ```php // SELECT * FROM `user` `u`, `profile`; $query = (new \yii\db\Query)->from(['u' => 'user', 'profile']); // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; $subquery = (new \yii\db\Query)->from('user')->where(['active' => true]) $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); // subquery can also be a string with plain SQL wrapped in parenthesis // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; $subquery = "(SELECT * FROM `user` WHERE `active` = 1)"; $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); ```

groupBy() public méthode

Sets the GROUP BY part of the query.
See also: addGroupBy()
public groupBy ( string | array | yii\db\Expression $columns )
$columns string | array | yii\db\Expression the columns to be grouped by. Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression). Note that if your group-by is an expression containing commas, you should always use an array to represent the group-by information. Otherwise, the method will not be able to correctly determine the group-by columns. Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL.

having() public méthode

Sets the HAVING part of the query.
See also: andHaving()
See also: orHaving()
public having ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the conditions to be put after HAVING. Please refer to [[where()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

innerJoin() public méthode

Appends an INNER JOIN part to the query.
public innerJoin ( string | array $table, string | array $on = '', array $params = [] )
$table string | array the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query.
$on string | array the join condition that should appear in the ON part. Please refer to [[join()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

join() public méthode

The first parameter specifies what type of join it is.
public join ( string $type, string | array $table, string | array $on = '', array $params = [] )
$type string the type of join, such as INNER JOIN, LEFT JOIN.
$table string | array the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query.
$on string | array the join condition that should appear in the ON part. Please refer to [[where()]] on how to specify this parameter. Note that the array format of [[where()]] is designed to match columns to values instead of columns to columns, so the following would **not** work as expected: `['post.author_id' => 'user.id']`, it would match the `post.author_id` column value against the string `'user.id'`. It is recommended to use the string syntax here which is more suited for a join: ```php 'post.author_id = user.id' ```
$params array the parameters (name => value) to be bound to the query.

leftJoin() public méthode

Appends a LEFT OUTER JOIN part to the query.
public leftJoin ( string | array $table, string | array $on = '', array $params = [] )
$table string | array the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query.
$on string | array the join condition that should appear in the ON part. Please refer to [[join()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query

max() public méthode

Returns the maximum of the specified column values.
public max ( string $q, Connection $db = null ) : mixed
$q string the column name or expression. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression.
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat mixed the maximum of the specified column values.

min() public méthode

Returns the minimum of the specified column values.
public min ( string $q, Connection $db = null ) : mixed
$q string the column name or expression. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression.
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat mixed the minimum of the specified column values.

one() public méthode

Executes the query and returns a single row of result.
public one ( Connection $db = null ) : array | boolean
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat array | boolean the first row (in terms of an array) of the query result. False is returned if the query results in nothing.

orHaving() public méthode

The new condition and the existing one will be joined using the 'OR' operator.
See also: having()
See also: andHaving()
public orHaving ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the new HAVING condition. Please refer to [[where()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

orWhere() public méthode

The new condition and the existing one will be joined using the 'OR' operator.
See also: where()
See also: andWhere()
public orWhere ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the new WHERE condition. Please refer to [[where()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query.

params() public méthode

Sets the parameters to be bound to the query.
See also: addParams()
public params ( array $params )
$params array list of query parameter values indexed by parameter placeholders. For example, `[':name' => 'Dan', ':age' => 31]`.

populate() public méthode

This method is internally used to convert the data fetched from database into the format as required by this query.
public populate ( array $rows ) : array
$rows array the raw query result from database
Résultat array the converted query result

prepare() public méthode

This method is called by QueryBuilder when it starts to build SQL from a query object. You may override this method to do some final preparation work when converting a query into a SQL statement.
public prepare ( QueryBuilder $builder )
$builder QueryBuilder

queryScalar() protected méthode

Restores the value of select to make this query reusable.
protected queryScalar ( string | yii\db\Expression $selectExpression, Connection | null $db ) : boolean | string
$selectExpression string | yii\db\Expression
$db Connection | null
Résultat boolean | string

rightJoin() public méthode

Appends a RIGHT OUTER JOIN part to the query.
public rightJoin ( string | array $table, string | array $on = '', array $params = [] )
$table string | array the table to be joined. Use a string to represent the name of the table to be joined. The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). The method will automatically quote the table name unless it contains some parenthesis (which means the table is given as a sub-query or DB expression). Use an array to represent joining with a sub-query. The array must contain only one element. The value must be a [[Query]] object representing the sub-query while the corresponding key represents the alias for the sub-query.
$on string | array the join condition that should appear in the ON part. Please refer to [[join()]] on how to specify this parameter.
$params array the parameters (name => value) to be bound to the query

scalar() public méthode

The value returned will be the first column in the first row of the query results.
public scalar ( Connection $db = null ) : string | null | false
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat string | null | false the value of the first column in the first row of the query result. False is returned if the query result is empty.

select() public méthode

Sets the SELECT part of the query.
public select ( string | array | yii\db\Expression $columns, string $option = null )
$columns string | array | yii\db\Expression the columns to be selected. Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). Columns can be prefixed with table names (e.g. "user.id") and/or contain column aliases (e.g. "user.id AS user_id"). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression). A DB expression may also be passed in form of an [[Expression]] object. Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts. When the columns are specified as an array, you may also use array keys as the column aliases (if a column does not need alias, do not use a string key). Starting from version 2.0.1, you may also select sub-queries as columns by specifying each such column as a `Query` instance representing the sub-query.
$option string additional option that should be appended to the 'SELECT' keyword. For example, in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used.

sum() public méthode

Returns the sum of the specified column values.
public sum ( string $q, Connection $db = null ) : mixed
$q string the column name or expression. Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression.
$db Connection the database connection used to generate the SQL statement. If this parameter is not given, the `db` application component will be used.
Résultat mixed the sum of the specified column values.

union() public méthode

Appends a SQL statement using UNION operator.
public union ( string | Query $sql, boolean $all = false )
$sql string | Query the SQL statement to be appended using UNION
$all boolean TRUE if using UNION ALL and FALSE if using UNION

where() public méthode

The method requires a $condition parameter, and optionally a $params parameter specifying the values to be bound to the query. The $condition parameter should be either a string (e.g. 'id=1') or an array.
See also: andWhere()
See also: orWhere()
See also: QueryInterface::where()
public where ( string | array | yii\db\Expression $condition, array $params = [] )
$condition string | array | yii\db\Expression the conditions that should be put in the WHERE part.
$params array the parameters (name => value) to be bound to the query.

Property Details

$distinct public_oe property

whether to select distinct rows of data only. If this is set true, the SELECT clause would be changed to SELECT DISTINCT.
public $distinct

$from public_oe property

the table(s) to be selected from. For example, ['user', 'post']. This is used to construct the FROM clause in a SQL statement.
See also: from()
public $from

$groupBy public_oe property

how to group the query results. For example, ['company', 'department']. This is used to construct the GROUP BY clause in a SQL statement.
public $groupBy

$having public_oe property

the condition to be applied in the GROUP BY clause. It can be either a string or an array. Please refer to Query::where on how to specify the condition.
public $having

$join public_oe property

how to join with other tables. Each array element represents the specification of one join which has the following structure: php [$joinType, $tableName, $joinCondition] For example, php [ ['INNER JOIN', 'user', 'user.id = author_id'], ['LEFT JOIN', 'team', 'team.id = team_id'], ]
public $join

$params public_oe property

list of query parameter values indexed by parameter placeholders. For example, [':name' => 'Dan', ':age' => 31].
public $params

$select public_oe property

the columns being selected. For example, ['id', 'name']. This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns.
See also: select()
public $select

$selectOption public_oe property

additional option that should be appended to the 'SELECT' keyword. For example, in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used.
public $selectOption

$union public_oe property

this is used to construct the UNION clause(s) in a SQL statement. Each array element is an array of the following structure: - query: either a string or a Query object representing a query - all: boolean, whether it should be UNION ALL or UNION
public $union