QueryInterface

API reference › yii › db › QueryInterface

interface	yii\db\QueryInterface
Since	2.0
Source	framework/db/QueryInterface.php

Implemented by 2 types

• Query
• ActiveQueryInterface

The QueryInterface defines the minimum set of methods to be implemented by a database query.

The default implementation of this interface is provided by QueryTrait.

It has support for getting one() instance or all(). Allows pagination via limit() and offset(). Sorting is supported via orderBy() and items can be limited to match some conditions using where().

Methods

Method	Description	Defined by
addOrderBy() abstract	Adds additional ORDER BY columns to the query.	QueryInterface
all() abstract	Executes the query and returns all results as an array.	QueryInterface
andFilterWhere() abstract	Adds an additional WHERE condition to the existing one ignoring empty parameters.	QueryInterface
andWhere() abstract	Adds an additional WHERE condition to the existing one.	QueryInterface
count() abstract	Returns the number of records.	QueryInterface
emulateExecution() abstract	Sets whether to emulate query execution, preventing any interaction with data storage.	QueryInterface
exists() abstract	Returns a value indicating whether the query result contains any row of data.	QueryInterface
filterWhere() abstract	Sets the WHERE part of the query ignoring empty parameters.	QueryInterface
indexBy() abstract	Sets the indexBy() property.	QueryInterface
limit() abstract	Sets the LIMIT part of the query.	QueryInterface
offset() abstract	Sets the OFFSET part of the query.	QueryInterface
one() abstract	Executes the query and returns a single row of result.	QueryInterface
orderBy() abstract	Sets the ORDER BY part of the query.	QueryInterface
orFilterWhere() abstract	Adds an additional WHERE condition to the existing one ignoring empty parameters.	QueryInterface
orWhere() abstract	Adds an additional WHERE condition to the existing one.	QueryInterface
where() abstract	Sets the WHERE part of the query.	QueryInterface

Method details

addOrderBy()

abstract public function addOrderBy(string|array $columns): $this

Adds additional ORDER BY columns to the query.

Parameters

Parameter	Type	Description
$columns	string|array	The columns (and the directions) to be ordered by. Columns can be specified in either a string (e.g. “id ASC, name DESC”) or an array (e.g. ['id' => SORT_ASC, 'name' => SORT_DESC]). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression).

Returns

$this: The query object itself

See also

• orderBy()

all()

abstract public function all(yii\db\Connection|null $db = null): array

Executes the query and returns all results as an array.

Parameters

Parameter	Type	Description
$db	Connection|null	The database connection used to execute the query. If this parameter is not given, the db application component will be used.

Returns

array: The query results. If the query results in nothing, an empty array will be returned.

andFilterWhere()

abstract public function andFilterWhere(array $condition): $this

Adds an additional WHERE condition to the existing one ignoring empty parameters.

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

Parameters

Parameter	Type	Description
$condition	array	The new WHERE condition. Please refer to where() on how to specify this parameter.

Returns

$this: The query object itself

See also

• filterWhere()
• orFilterWhere()

andWhere()

abstract public function andWhere(array $condition): $this

Adds an additional WHERE condition to the existing one.

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

Parameters

Parameter	Type	Description
$condition	array	The new WHERE condition. Please refer to where() on how to specify this parameter.

Returns

$this: The query object itself

See also

• where()
• orWhere()

count()

abstract public function count(string $q = '*', yii\db\Connection|null $db = null): int|string|null

Returns the number of records.

Parameters

Parameter	Type	Description
$q	string	The COUNT expression. Defaults to ’*’.
$db	Connection|null	The database connection used to execute the query. If this parameter is not given, the db application component will be used.

Returns

int|string|null: Number of records.

emulateExecution()

abstract public function emulateExecution(bool $value = true): $this

Sets whether to emulate query execution, preventing any interaction with data storage.

After this mode is enabled, methods, returning query results like one(), all(), exists() and so on, will return empty or false values. You should use this method in case your program logic indicates query should not return any results, like in case you set false where condition like 0=1.

Parameters

Parameter	Type	Description
$value	bool	Whether to prevent query execution.

Returns

$this: The query object itself.

Since

2.0.11

exists()

abstract public function exists(yii\db\Connection|null $db = null): bool

Returns a value indicating whether the query result contains any row of data.

Parameters

Parameter	Type	Description
$db	Connection|null	The database connection used to execute the query. If this parameter is not given, the db application component will be used.

Returns

bool: Whether the query result contains any row of data.

filterWhere()

abstract public function filterWhere(array $condition): $this

Sets the WHERE part of the query ignoring empty parameters.

Parameters

Parameter	Type	Description
$condition	array	The conditions that should be put in the WHERE part. Please refer to where() on how to specify this parameter.

Returns

$this: The query object itself

See also

• andFilterWhere()
• orFilterWhere()

indexBy()

abstract public function indexBy(string|callable $column): $this

Sets the indexBy() property.

Parameters

Parameter	Type	Description
$column	string|callable	The name of the column by which the query results should be indexed by. This can also be a callable (e.g. anonymous function) that returns the index value based on the given row data. The signature of the callable should be: function ($row) { // return the index value corresponding to $row }

Returns

$this: The query object itself

limit()

abstract public function limit(int|null $limit): $this

Sets the LIMIT part of the query.

Parameters

Parameter	Type	Description
$limit	int|null	The limit. Use null or negative value to disable limit.

Returns

$this: The query object itself

offset()

abstract public function offset(int|null $offset): $this

Sets the OFFSET part of the query.

Parameters

Parameter	Type	Description
$offset	int|null	The offset. Use null or negative value to disable offset.

Returns

$this: The query object itself

one()

abstract public function one(yii\db\Connection|null $db = null): array|bool

Executes the query and returns a single row of result.

Parameters

Parameter	Type	Description
$db	Connection|null	The database connection used to execute the query. If this parameter is not given, the db application component will be used.

Returns

array|bool: The first row (in terms of an array) of the query result. False is returned if the query results in nothing.

orderBy()

abstract public function orderBy(string|array $columns): $this

Sets the ORDER BY part of the query.

Parameters

Parameter	Type	Description
$columns	string|array	The columns (and the directions) to be ordered by. Columns can be specified in either a string (e.g. “id ASC, name DESC”) or an array (e.g. ['id' => SORT_ASC, 'name' => SORT_DESC]). The method will automatically quote the column names unless a column contains some parenthesis (which means the column contains a DB expression).

Returns

$this: The query object itself

See also

• addOrderBy()

orFilterWhere()

abstract public function orFilterWhere(array $condition): $this

Adds an additional WHERE condition to the existing one ignoring empty parameters.

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

Parameters

Parameter	Type	Description
$condition	array	The new WHERE condition. Please refer to where() on how to specify this parameter.

Returns

$this: The query object itself

See also

• filterWhere()
• andFilterWhere()

orWhere()

abstract public function orWhere(array $condition): $this

Adds an additional WHERE condition to the existing one.

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

Parameters

Parameter	Type	Description
$condition	array	The new WHERE condition. Please refer to where() on how to specify this parameter.

Returns

$this: The query object itself

See also

• where()
• andWhere()

where()

abstract public function where(array $condition): $this

Sets the WHERE part of the query.

The $condition specified as an array can be in one of the following two formats:

• hash format: ['column1' => value1, 'column2' => value2, ...]
• operator format: [operator, operand1, operand2, ...]

A condition in hash format represents the following SQL expression in general: column1=value1 AND column2=value2 AND .... In case when a value is an array or sub-query, an IN expression will be generated. And if a value is null, IS NULL will be used in the generated expression. Below are some examples:

• ['type' => 1, 'status' => 2] generates (type = 1) AND (status = 2).
• ['id' => [1, 2, 3], 'status' => 2] generates (id IN (1, 2, 3)) AND (status = 2).
• ['status' => null] generates status IS NULL.

A condition in operator format generates the SQL expression according to the specified operator, which can be one of the following:

• and: the operands should be concatenated together using AND. For example, ['and', 'id=1', 'id=2'] will generate id=1 AND id=2. If an operand is an array, it will be converted into a string using the rules described here. For example, ['and', 'type=1', ['or', 'id=1', 'id=2']] will generate type=1 AND (id=1 OR id=2). The method will not do any quoting or escaping.

• or: similar to the and operator except that the operands are concatenated using OR. For example, ['or', ['type' => [7, 8, 9]], ['id' => [1, 2, 3]]] will generate (type IN (7, 8, 9) OR (id IN (1, 2, 3))).

• not: this will take only one operand and build the negation of it by prefixing the query string with NOT. For example ['not', ['attribute' => null]] will result in the condition NOT (attribute IS NULL).

• between: operand 1 should be the column name, and operand 2 and 3 should be the starting and ending values of the range that the column is in. For example, ['between', 'id', 1, 10] will generate id BETWEEN 1 AND 10.

• not between: similar to between except the BETWEEN is replaced with NOT BETWEEN in the generated condition.

• in: operand 1 should be a column or DB expression, and operand 2 be an array representing the range of the values that the column or DB expression should be in. For example, ['in', 'id', [1, 2, 3]] will generate id IN (1, 2, 3). The method will properly quote the column name and escape values in the range.

  To create a composite IN condition you can use and array for the column name and value, where the values are indexed by the column name: ['in', ['id', 'name'], [['id' => 1, 'name' => 'foo'], ['id' => 2, 'name' => 'bar']] ].

  You may also specify a sub-query that is used to get the values for the IN-condition: ['in', 'user_id', (new Query())->select('id')->from('users')->where(['active' => 1])]

• not in: similar to the in operator except that IN is replaced with NOT IN in the generated condition.

• like: operand 1 should be a column or DB expression, and operand 2 be a string or an array representing the values that the column or DB expression should be like. For example, ['like', 'name', 'tester'] will generate name LIKE '%tester%'. When the value range is given as an array, multiple LIKE predicates will be generated and concatenated using AND. For example, ['like', 'name', ['test', 'sample']] will generate name LIKE '%test%' AND name LIKE '%sample%'. The method will properly quote the column name and escape special characters in the values. Sometimes, you may want to add the percentage characters to the matching value by yourself, you may supply a third operand false to do so. For example, ['like', 'name', '%tester', false] will generate name LIKE '%tester'.

• or like: similar to the like operator except that OR is used to concatenate the LIKE predicates when operand 2 is an array.

• not like: similar to the like operator except that LIKE is replaced with NOT LIKE in the generated condition.

• or not like: similar to the not like operator except that OR is used to concatenate the NOT LIKE predicates.

• exists: operand 1 is a query object that used to build an EXISTS condition. For example ['exists', (new Query())->select('id')->from('users')->where(['active' => 1])] will result in the following SQL expression: EXISTS (SELECT "id" FROM "users" WHERE "active"=1).

• not exists: similar to the exists operator except that EXISTS is replaced with NOT EXISTS in the generated condition.

• Additionally you can specify arbitrary operators as follows: A condition of ['>=', 'id', 10] will result in the following SQL expression: id >= 10.

Note that this method will override any existing WHERE condition. You might want to use andWhere() or orWhere() instead.

Parameters

Parameter	Type	Description
$condition	array	The conditions that should be put in the WHERE part.

Returns

$this: The query object itself

See also

• andWhere()
• orWhere()