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 25 26 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
    /**
     * @var string|array query condition. This refers to the WHERE clause in a SQL statement.
     * For example, `age > 31 AND team = 1`.
     * @see where()
     */
    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
}