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

Mostrar archivo Open project: yiisoft/yii2 Class Usage Examples

Public Properties

Property	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

Public Methods

Method	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.

Protected Methods

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

Method Details

addGroupBy() public method

Adds additional group-by columns to the existing ones.

addParams() public method

Adds additional parameters to be bound to the query.

addSelect() public method

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();

all() public method

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.
return	array	the query results. If the query results in nothing, an empty array will be returned.

andFilterCompare() public method

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 method

The new condition and the existing one will be joined using the 'AND' operator.

andWhere() public method

The new condition and the existing one will be joined using the 'AND' operator.

average() public method

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.
return	mixed	the average of the specified column values.

batch() public method

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.
return	BatchQueryResult	the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches.

column() public method

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.
return	array	the first column of the query result. An empty array is returned if the query results in nothing.

count() public method

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.
return	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 method

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
return	Query	the new Query object

createCommand() public method

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.
return	Command	the created DB command instance.

distinct() public method

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 method

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.
return	BatchQueryResult	the batch query result. It implements the [[\Iterator]] interface and can be traversed to retrieve the data in batches.

exists() public method

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.
return	boolean	whether the query result contains any row of data.

from() public method

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 method

Sets the GROUP BY part of the query.

having() public method

Sets the HAVING part of the query.

innerJoin() public method

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 method

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 method

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 method

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.
return	mixed	the maximum of the specified column values.

min() public method

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.
return	mixed	the minimum of the specified column values.

one() public method

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.
return	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 method

The new condition and the existing one will be joined using the 'OR' operator.

orWhere() public method

The new condition and the existing one will be joined using the 'OR' operator.

params() public method

Sets the parameters to be bound to the query.

populate() public method

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
return	array	the converted query result

prepare() public method

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 method

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
return	boolean \| string

rightJoin() public method

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 method

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.
return	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 method

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 method

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.
return	mixed	the sum of the specified column values.

union() public method

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 method

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.

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.

$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.

$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

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.

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.

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.

protected queryScalar ( string \| yii\db\Expression $selectExpression, Connection \| null $db ) : boolean \| string
$selectExpression	string \| yii\db\Expression
$db	Connection \| null
return	boolean \| string

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

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.

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.

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.

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.

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.

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

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.