QueryInterface.php 10.8 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
    /**
     * Sets the WHERE part of the query.
     *
79
     * The `$condition` specified as an array can be in one of the following two formats:
80 81 82 83 84 85
     *
     * - 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,
86
     * an `IN` expression will be generated. And if a value is `null`, `IS NULL` will be used
87 88 89 90 91 92 93 94 95
     * 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:
     *
96
     * - **and**: the operands should be concatenated together using `AND`. For example,
97 98 99
     *   `['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)`.
100 101 102
     *   The method will *not* do any quoting or escaping.
     *
     * - **or**: similar to the `and` operator except that the operands are concatenated using `OR`.
103
     *
104
     * - **not**: this will take only one operator and build the negation of it by prefixing the query string with `NOT`.
Carsten Brandt committed
105
     *   For example `['not', ['attribute' => null]]` will result in the condition `NOT (attribute IS NULL)`.
106
     *
107
     * - **between**: operand 1 should be the column name, and operand 2 and 3 should be the
108 109
     *   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`.
110
     *
111
     * - **not between**: similar to `between` except the `BETWEEN` is replaced with `NOT BETWEEN`
112
     *   in the generated condition.
113
     *
114
     * - **in**: operand 1 should be a column or DB expression, and operand 2 be an array representing
115 116 117
     *   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.
118
     *
119
     * - **not in**: similar to the `in` operator except that `IN` is replaced with `NOT IN` in the generated condition.
120
     *
121
     * - **like**: operand 1 should be a column or DB expression, and operand 2 be a string or an array representing
122 123 124 125 126 127 128 129
     *   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'`.
130
     *
131
     * - **or like**: similar to the `like` operator except that `OR` is used to concatenate the `LIKE`
132
     *   predicates when operand 2 is an array.
133
     *
134
     * - **not like**: similar to the `like` operator except that `LIKE` is replaced with `NOT LIKE`
135
     *   in the generated condition.
136
     *
137
     * - **or not like**: similar to the `not like` operator except that `OR` is used to concatenate
138
     *   the `NOT LIKE` predicates.
139
     *
Carsten Brandt committed
140 141 142
     * - Additionally you can specify arbitrary operators as follows: A condition of `['>=', 'id', 10]` will result in the
     *   following SQL expression: `id >= 10`.
     *
Qiang Xue committed
143
     * @param string|array $condition the conditions that should be put in the WHERE part.
144 145 146 147 148
     * @return static the query object itself
     * @see andWhere()
     * @see orWhere()
     */
    public function where($condition);
149

Alexander Makarov committed
150
    /**
Qiang Xue committed
151 152 153
     * 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
154 155
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
156 157
     * @see where()
     * @see orWhere()
Alexander Makarov committed
158
     */
Qiang Xue committed
159
    public function andWhere($condition);
Alexander Makarov committed
160

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

Alexander Makarov committed
172
    /**
Qiang Xue committed
173 174 175
     * 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
176 177
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
178
     * @see andFilterWhere()
179
     * @see orFilterWhere()
Alexander Makarov committed
180
     */
Qiang Xue committed
181
    public function filterWhere(array $condition);
Alexander Makarov committed
182

183
    /**
Qiang Xue committed
184 185 186
     * 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()]]
187 188
     * on how to specify this parameter.
     * @return static the query object itself
Qiang Xue committed
189 190
     * @see filterWhere()
     * @see orFilterWhere()
191
     */
Qiang Xue committed
192
    public function andFilterWhere(array $condition);
193

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

205 206
    /**
     * Sets the ORDER BY part of the query.
207 208 209 210 211 212
     * @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
213 214 215
     * @see addOrderBy()
     */
    public function orderBy($columns);
216

217 218
    /**
     * Adds additional ORDER BY columns to the query.
219 220 221 222 223 224
     * @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
225 226 227
     * @see orderBy()
     */
    public function addOrderBy($columns);
228

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

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