QueryInterface.php 10.4 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db;

/**
 * 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]].
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @author Carsten Brandt <mail@cebe.cc>
 * @since 2.0
 */
interface QueryInterface
{
25 26
    /**
     * Executes the query and returns all results as an array.
27 28 29
     * @param Connection $db the database connection used to execute the query.
     * 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.
30 31
     */
    public function all($db = null);
32

33 34
    /**
     * Executes the query and returns a single row of result.
35 36
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
37
     * @return array|boolean the first row (in terms of an array) of the query result. False is returned if the query
38
     * results in nothing.
39 40
     */
    public function one($db = null);
41

42 43
    /**
     * Returns the number of records.
44 45 46 47
     * @param string $q the COUNT expression. Defaults to '*'.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return integer number of records
48 49
     */
    public function count($q = '*', $db = null);
50

51 52
    /**
     * Returns a value indicating whether the query result contains any row of data.
53 54 55
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return boolean whether the query result contains any row of data.
56 57
     */
    public function exists($db = null);
58

59 60 61
    /**
     * Sets the [[indexBy]] property.
     * @param string|callable $column the name of the column by which the query results should be indexed by.
62 63
     * 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:
64 65 66 67 68 69 70 71 72 73 74
     *
     * ~~~
     * function ($row)
     * {
     *     // return the index value corresponding to $row
     * }
     * ~~~
     *
     * @return static the query object itself
     */
    public function indexBy($column);
75

76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138
    /**
     * Sets the WHERE part of the query.
     *
     * The method requires a $condition parameter.
     *
     * The $condition parameter should be an array 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,
     * 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 followings:
     *
     * - `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`.
     *
     * - `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.
     *
     * - `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.
     *
Qiang Xue committed
139
     * @param string|array $condition the conditions that should be put in the WHERE part.
140 141 142 143 144
     * @return static the query object itself
     * @see andWhere()
     * @see orWhere()
     */
    public function where($condition);
145

Alexander Makarov committed
146
    /**
Qiang Xue committed
147 148 149
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'AND' operator.
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
Alexander Makarov committed
150 151
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
152 153
     * @see where()
     * @see orWhere()
Alexander Makarov committed
154
     */
Qiang Xue committed
155
    public function andWhere($condition);
Alexander Makarov committed
156

157 158
    /**
     * Adds an additional WHERE condition to the existing one.
Qiang Xue committed
159
     * The new condition and the existing one will be joined using the 'OR' operator.
160 161 162
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return static the query object itself
163
     * @see where()
Qiang Xue committed
164
     * @see andWhere()
165
     */
Qiang Xue committed
166
    public function orWhere($condition);
167

Alexander Makarov committed
168
    /**
Qiang Xue committed
169 170 171
     * Sets the WHERE part of the query ignoring empty parameters.
     *
     * @param array $condition the conditions that should be put in the WHERE part. Please refer to [[where()]]
Alexander Makarov committed
172 173
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
174
     * @see andFilterWhere()
175
     * @see orFilterWhere()
Alexander Makarov committed
176
     */
Qiang Xue committed
177
    public function filterWhere(array $condition);
Alexander Makarov committed
178

179
    /**
Qiang Xue committed
180 181 182
     * 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.
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
183 184
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
185 186
     * @see filterWhere()
     * @see orFilterWhere()
187
     */
Qiang Xue committed
188
    public function andFilterWhere(array $condition);
189

Alexander Makarov committed
190 191 192
    /**
     * 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.
Qiang Xue committed
193
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
Alexander Makarov committed
194 195
     * on how to specify this parameter.
     * @return static the query object itself
196 197
     * @see filterWhere()
     * @see andFilterWhere()
Alexander Makarov committed
198
     */
Qiang Xue committed
199
    public function orFilterWhere(array $condition);
Alexander Makarov committed
200

201 202
    /**
     * Sets the ORDER BY part of the query.
203 204 205 206 207 208
     * @param string|array $columns 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).
     * @return static the query object itself
209 210 211
     * @see addOrderBy()
     */
    public function orderBy($columns);
212

213 214
    /**
     * Adds additional ORDER BY columns to the query.
215 216 217 218 219 220
     * @param string|array $columns 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).
     * @return static the query object itself
221 222 223
     * @see orderBy()
     */
    public function addOrderBy($columns);
224

225 226
    /**
     * Sets the LIMIT part of the query.
227 228
     * @param integer $limit the limit. Use null or negative value to disable limit.
     * @return static the query object itself
229 230
     */
    public function limit($limit);
231

232 233
    /**
     * Sets the OFFSET part of the query.
234 235
     * @param integer $offset the offset. Use null or negative value to disable offset.
     * @return static the query object itself
236 237
     */
    public function offset($offset);
238
}