# QueryInterface

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

[API reference](/docs/api/index.md) › [yii](/docs/api/yii/index.md) › [db](/docs/api/yii/db/index.md) › QueryInterface

| interface | `yii\db\QueryInterface` |
| --- | --- |
| Since | 2.0 |
| Source | [`framework/db/QueryInterface.php`](https://github.com/yiisoft/yii2/blob/22.0/framework/db/QueryInterface.php#L24) |

Implemented by 2 types:

- [`Query`](/docs/api/yii/db/query/index.md)
- [`ActiveQueryInterface`](/docs/api/yii/db/activequeryinterface/index.md)

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`](/docs/api/yii/db/querytrait/index.md).

It has support for getting [`one()`](/docs/api/yii/db/queryinterface/index.md#method-one) instance or [`all()`](/docs/api/yii/db/queryinterface/index.md#method-all).
Allows pagination via [`limit()`](/docs/api/yii/db/queryinterface/index.md#method-limit) and [`offset()`](/docs/api/yii/db/queryinterface/index.md#method-offset).
Sorting is supported via [`orderBy()`](/docs/api/yii/db/queryinterface/index.md#method-orderBy) and items can be limited to match some conditions using [`where()`](/docs/api/yii/db/queryinterface/index.md#method-where).

## Methods

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

## Method details

### addOrderBy() <span id="method-addOrderBy"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-orderBy)

### all() <span id="method-all"></span>

```php
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`](/docs/api/yii/db/connection/index.md)`\|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() <span id="method-andFilterWhere"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-where) on how to specify this parameter. |

**Returns**

`$this`: The query object itself

**See also**

- [`filterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-filterWhere)
- [`orFilterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-orFilterWhere)

### andWhere() <span id="method-andWhere"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-where) on how to specify this parameter. |

**Returns**

`$this`: The query object itself

**See also**

- [`where()`](/docs/api/yii/db/queryinterface/index.md#method-where)
- [`orWhere()`](/docs/api/yii/db/queryinterface/index.md#method-orWhere)

### count() <span id="method-count"></span>

```php
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`](/docs/api/yii/db/connection/index.md)`\|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() <span id="method-emulateExecution"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-one), [`all()`](/docs/api/yii/db/queryinterface/index.md#method-all), [`exists()`](/docs/api/yii/db/queryinterface/index.md#method-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() <span id="method-exists"></span>

```php
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`](/docs/api/yii/db/connection/index.md)`\|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() <span id="method-filterWhere"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-where) on how to specify this parameter. |

**Returns**

`$this`: The query object itself

**See also**

- [`andFilterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-andFilterWhere)
- [`orFilterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-orFilterWhere)

### indexBy() <span id="method-indexBy"></span>

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

Sets the [`indexBy()`](/docs/api/yii/db/queryinterface/index.md#method-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() <span id="method-limit"></span>

```php
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() <span id="method-offset"></span>

```php
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() <span id="method-one"></span>

```php
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`](/docs/api/yii/db/connection/index.md)`\|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() <span id="method-orderBy"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-addOrderBy)

### orFilterWhere() <span id="method-orFilterWhere"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-where) on how to specify this parameter. |

**Returns**

`$this`: The query object itself

**See also**

- [`filterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-filterWhere)
- [`andFilterWhere()`](/docs/api/yii/db/queryinterface/index.md#method-andFilterWhere)

### orWhere() <span id="method-orWhere"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-where) on how to specify this parameter. |

**Returns**

`$this`: The query object itself

**See also**

- [`where()`](/docs/api/yii/db/queryinterface/index.md#method-where)
- [`andWhere()`](/docs/api/yii/db/queryinterface/index.md#method-andWhere)

### where() <span id="method-where"></span>

```php
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()`](/docs/api/yii/db/queryinterface/index.md#method-andWhere) or [`orWhere()`](/docs/api/yii/db/queryinterface/index.md#method-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()`](/docs/api/yii/db/queryinterface/index.md#method-andWhere)
- [`orWhere()`](/docs/api/yii/db/queryinterface/index.md#method-orWhere)