QueryTrait.php 12.9 KB
Newer Older
1 2 3 4 5 6 7 8 9
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db;

10
use yii\base\NotSupportedException;
11 12 13 14

/**
 * The BaseQuery trait represents the minimum method set of a database Query.
 *
15
 * It is supposed to be used in a class that implements the [[QueryInterface]].
16 17 18 19 20
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @author Carsten Brandt <mail@cebe.cc>
 * @since 2.0
 */
21
trait QueryTrait
22
{
23
    /**
24
     * @var string|array query condition. This refers to the WHERE clause in a SQL statement.
25 26
     * For example, `['age' => 31, 'team' => 1]`.
     * @see where() for valid syntax on specifying this value.
27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
     */
    public $where;
    /**
     * @var integer maximum number of records to be returned. If not set or less than 0, it means no limit.
     */
    public $limit;
    /**
     * @var integer zero-based offset from where the records are to be returned. If not set or
     * less than 0, it means starting from the beginning.
     */
    public $offset;
    /**
     * @var array how to sort the query results. This is used to construct the ORDER BY clause in a SQL statement.
     * The array keys are the columns to be sorted by, and the array values are the corresponding sort directions which
     * can be either [SORT_ASC](http://php.net/manual/en/array.constants.php#constant.sort-asc)
     * or [SORT_DESC](http://php.net/manual/en/array.constants.php#constant.sort-desc).
     * The array may also contain [[Expression]] objects. If that is the case, the expressions
     * will be converted into strings without any change.
     */
    public $orderBy;
    /**
     * @var string|callable $column 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. For more details, see [[indexBy()]]. This property is only used by [[QueryInterface::all()|all()]].
     */
    public $indexBy;

54

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

    /**
     * Sets the WHERE part of the query.
     *
     * See [[QueryInterface::where()]] for detailed documentation.
     *
Qiang Xue committed
81
     * @param string|array $condition the conditions that should be put in the WHERE part.
Carsten Brandt committed
82
     * @return static the query object itself.
83 84 85 86 87 88 89 90 91 92 93 94
     * @see andWhere()
     * @see orWhere()
     */
    public function where($condition)
    {
        $this->where = $condition;
        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'AND' operator.
95 96
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
Carsten Brandt committed
97
     * @return static the query object itself.
98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
     * @see where()
     * @see orWhere()
     */
    public function andWhere($condition)
    {
        if ($this->where === null) {
            $this->where = $condition;
        } else {
            $this->where = ['and', $this->where, $condition];
        }
        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'OR' operator.
114 115
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
Carsten Brandt committed
116
     * @return static the query object itself.
117 118 119 120 121 122 123 124 125 126 127 128 129
     * @see where()
     * @see andWhere()
     */
    public function orWhere($condition)
    {
        if ($this->where === null) {
            $this->where = $condition;
        } else {
            $this->where = ['or', $this->where, $condition];
        }
        return $this;
    }

Alexander Makarov committed
130
    /**
Qiang Xue committed
131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146
     * Sets the WHERE part of the query but ignores [[isEmpty()|empty operands]].
     *
     * This method is similar to [[where()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * The following code shows the difference between this method and [[where()]]:
     *
     * ```php
     * // WHERE `age`=:age
     * $query->filterWhere(['name' => null, 'age' => 20]);
     * // WHERE `age`=:age
     * $query->where(['age' => 20]);
     * // WHERE `name` IS NULL AND `age`=:age
     * $query->where(['name' => null, 'age' => 20]);
     * ```
Alexander Makarov committed
147
     *
Qiang Xue committed
148
     * Note that unlike [[where()]], you cannot pass binding parameters to this method.
Alexander Makarov committed
149 150
     *
     * @param array $condition the conditions that should be put in the WHERE part.
151
     * See [[where()]] on how to specify this parameter.
Carsten Brandt committed
152
     * @return static the query object itself.
153 154 155
     * @see where()
     * @see andFilterWhere()
     * @see orFilterWhere()
Alexander Makarov committed
156
     */
Qiang Xue committed
157
    public function filterWhere(array $condition)
Alexander Makarov committed
158 159 160 161 162 163 164 165
    {
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
            $this->where($condition);
        }
        return $this;
    }

166
    /**
Qiang Xue committed
167
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
168
     * The new condition and the existing one will be joined using the 'AND' operator.
Qiang Xue committed
169 170 171 172 173 174
     *
     * This method is similar to [[andWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
175
     * on how to specify this parameter.
Carsten Brandt committed
176
     * @return static the query object itself.
177 178
     * @see filterWhere()
     * @see orFilterWhere()
179
     */
Qiang Xue committed
180
    public function andFilterWhere(array $condition)
181
    {
182 183 184
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
            $this->andWhere($condition);
185 186 187 188
        }
        return $this;
    }

Alexander Makarov committed
189
    /**
Qiang Xue committed
190
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
191
     * The new condition and the existing one will be joined using the 'OR' operator.
Qiang Xue committed
192 193 194 195 196 197
     *
     * This method is similar to [[orWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
Alexander Makarov committed
198
     * on how to specify this parameter.
Carsten Brandt committed
199
     * @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
    {
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
207
            $this->orWhere($condition);
Alexander Makarov committed
208 209 210 211
        }
        return $this;
    }

212
    /**
Qiang Xue committed
213
     * Removes [[isEmpty()|empty operands]] from the given query condition.
214
     *
Qiang Xue committed
215 216
     * @param array $condition the original condition
     * @return array the condition with [[isEmpty()|empty operands]] removed.
Qiang Xue committed
217
     * @throws NotSupportedException if the condition operator is not supported
218
     */
219
    protected function filterCondition($condition)
220
    {
Qiang Xue committed
221 222 223
        if (!is_array($condition)) {
            return $condition;
        }
224

Qiang Xue committed
225 226 227 228 229 230 231 232 233 234 235
        if (!isset($condition[0])) {
            // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
            foreach ($condition as $name => $value) {
                if ($this->isEmpty($value)) {
                    unset($condition[$name]);
                }
            }
            return $condition;
        }

        // operator format: operator, operand 1, operand 2, ...
236

Qiang Xue committed
237 238 239 240 241 242 243 244 245 246
        $operator = array_shift($condition);

        switch (strtoupper($operator)) {
            case 'NOT':
            case 'AND':
            case 'OR':
                foreach ($condition as $i => $operand) {
                    $subCondition = $this->filterCondition($operand);
                    if ($this->isEmpty($subCondition)) {
                        unset($condition[$i]);
247
                    } else {
Qiang Xue committed
248
                        $condition[$i] = $subCondition;
249
                    }
Qiang Xue committed
250 251 252 253 254
                }

                if (empty($condition)) {
                    return [];
                }
255
                break;
256 257 258 259 260 261 262 263 264
            case 'BETWEEN':
            case 'NOT BETWEEN':
                if (array_key_exists(1, $condition) && array_key_exists(2, $condition)) {
                    if ($this->isEmpty($condition[1]) || $this->isEmpty($condition[2])) {
                        return [];
                    }
                }
                break;
            default:
Qiang Xue committed
265 266 267
                if (array_key_exists(1, $condition) && $this->isEmpty($condition[1])) {
                    return [];
                }
268
        }
Qiang Xue committed
269 270 271

        array_unshift($condition, $operator);

272
        return $condition;
273 274
    }

Alexander Makarov committed
275
    /**
Qiang Xue committed
276
     * Returns a value indicating whether the give value is "empty".
Alexander Makarov committed
277
     *
Qiang Xue committed
278
     * The value is considered "empty", if one of the following conditions is satisfied:
279 280 281 282 283 284
     *
     * - it is `null`,
     * - an empty string (`''`),
     * - a string containing only whitespace characters,
     * - or an empty array.
     *
Qiang Xue committed
285 286
     * @param mixed $value
     * @return boolean if the value is empty
Alexander Makarov committed
287
     */
Qiang Xue committed
288
    protected function isEmpty($value)
Alexander Makarov committed
289
    {
Qiang Xue committed
290
        return $value === '' || $value === [] || $value === null || is_string($value) && trim($value) === '';
291
    }
Alexander Makarov committed
292

293 294
    /**
     * Sets the ORDER BY part of the query.
295
     * @param string|array $columns the columns (and the directions) to be ordered by.
Carsten Brandt committed
296
     * Columns can be specified in either a string (e.g. `"id ASC, name DESC"`) or an array
297 298 299 300 301 302
     * (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).
     * Note that if your order-by is an expression containing commas, you should always use an array
     * to represent the order-by information. Otherwise, the method will not be able to correctly determine
     * the order-by columns.
Carsten Brandt committed
303
     * @return static the query object itself.
304 305 306 307 308 309 310 311 312 313
     * @see addOrderBy()
     */
    public function orderBy($columns)
    {
        $this->orderBy = $this->normalizeOrderBy($columns);
        return $this;
    }

    /**
     * Adds additional ORDER BY columns to the query.
314 315 316 317 318
     * @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).
Carsten Brandt committed
319
     * @return static the query object itself.
320 321 322 323 324 325 326 327 328 329 330 331 332
     * @see orderBy()
     */
    public function addOrderBy($columns)
    {
        $columns = $this->normalizeOrderBy($columns);
        if ($this->orderBy === null) {
            $this->orderBy = $columns;
        } else {
            $this->orderBy = array_merge($this->orderBy, $columns);
        }
        return $this;
    }

333 334 335 336 337 338
    /**
     * Normalizes format of ORDER BY data
     *
     * @param array|string $columns
     * @return array
     */
339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358
    protected function normalizeOrderBy($columns)
    {
        if (is_array($columns)) {
            return $columns;
        } else {
            $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
            $result = [];
            foreach ($columns as $column) {
                if (preg_match('/^(.*?)\s+(asc|desc)$/i', $column, $matches)) {
                    $result[$matches[1]] = strcasecmp($matches[2], 'desc') ? SORT_ASC : SORT_DESC;
                } else {
                    $result[$column] = SORT_ASC;
                }
            }
            return $result;
        }
    }

    /**
     * Sets the LIMIT part of the query.
359
     * @param integer $limit the limit. Use null or negative value to disable limit.
Carsten Brandt committed
360
     * @return static the query object itself.
361 362 363 364 365 366 367 368 369
     */
    public function limit($limit)
    {
        $this->limit = $limit;
        return $this;
    }

    /**
     * Sets the OFFSET part of the query.
370
     * @param integer $offset the offset. Use null or negative value to disable offset.
Carsten Brandt committed
371
     * @return static the query object itself.
372 373 374 375 376 377
     */
    public function offset($offset)
    {
        $this->offset = $offset;
        return $this;
    }
Qiang Xue committed
378
}