1 | <?php |
||
2 | /** |
||
3 | * @link http://www.yiiframework.com/ |
||
4 | * @copyright Copyright (c) 2008 Yii Software LLC |
||
5 | * @license http://www.yiiframework.com/license/ |
||
6 | */ |
||
7 | |||
8 | namespace yii\db; |
||
9 | |||
10 | use Yii; |
||
11 | use yii\base\Component; |
||
12 | use yii\base\InvalidArgumentException; |
||
13 | use yii\helpers\ArrayHelper; |
||
14 | use yii\base\InvalidConfigException; |
||
15 | |||
16 | /** |
||
17 | * Query represents a SELECT SQL statement in a way that is independent of DBMS. |
||
18 | * |
||
19 | * Query provides a set of methods to facilitate the specification of different clauses |
||
20 | * in a SELECT statement. These methods can be chained together. |
||
21 | * |
||
22 | * By calling [[createCommand()]], we can get a [[Command]] instance which can be further |
||
23 | * used to perform/execute the DB query against a database. |
||
24 | * |
||
25 | * For example, |
||
26 | * |
||
27 | * ```php |
||
28 | * $query = new Query; |
||
29 | * // compose the query |
||
30 | * $query->select('id, name') |
||
31 | * ->from('user') |
||
32 | * ->limit(10); |
||
33 | * // build and execute the query |
||
34 | * $rows = $query->all(); |
||
35 | * // alternatively, you can create DB command and execute it |
||
36 | * $command = $query->createCommand(); |
||
37 | * // $command->sql returns the actual SQL |
||
38 | * $rows = $command->queryAll(); |
||
39 | * ``` |
||
40 | * |
||
41 | * Query internally uses the [[QueryBuilder]] class to generate the SQL statement. |
||
42 | * |
||
43 | * A more detailed usage guide on how to work with Query can be found in the [guide article on Query Builder](guide:db-query-builder). |
||
44 | * |
||
45 | * @property string[] $tablesUsedInFrom Table names indexed by aliases. This property is read-only. |
||
46 | * |
||
47 | * @author Qiang Xue <[email protected]> |
||
48 | * @author Carsten Brandt <[email protected]> |
||
49 | * @since 2.0 |
||
50 | */ |
||
51 | class Query extends Component implements QueryInterface, ExpressionInterface |
||
52 | { |
||
53 | use QueryTrait; |
||
54 | |||
55 | /** |
||
56 | * @var array the columns being selected. For example, `['id', 'name']`. |
||
57 | * This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns. |
||
58 | * @see select() |
||
59 | */ |
||
60 | public $select; |
||
61 | /** |
||
62 | * @var string additional option that should be appended to the 'SELECT' keyword. For example, |
||
63 | * in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used. |
||
64 | */ |
||
65 | public $selectOption; |
||
66 | /** |
||
67 | * @var bool whether to select distinct rows of data only. If this is set true, |
||
68 | * the SELECT clause would be changed to SELECT DISTINCT. |
||
69 | */ |
||
70 | public $distinct; |
||
71 | /** |
||
72 | * @var array the table(s) to be selected from. For example, `['user', 'post']`. |
||
73 | * This is used to construct the FROM clause in a SQL statement. |
||
74 | * @see from() |
||
75 | */ |
||
76 | public $from; |
||
77 | /** |
||
78 | * @var array how to group the query results. For example, `['company', 'department']`. |
||
79 | * This is used to construct the GROUP BY clause in a SQL statement. |
||
80 | */ |
||
81 | public $groupBy; |
||
82 | /** |
||
83 | * @var array how to join with other tables. Each array element represents the specification |
||
84 | * of one join which has the following structure: |
||
85 | * |
||
86 | * ```php |
||
87 | * [$joinType, $tableName, $joinCondition] |
||
88 | * ``` |
||
89 | * |
||
90 | * For example, |
||
91 | * |
||
92 | * ```php |
||
93 | * [ |
||
94 | * ['INNER JOIN', 'user', 'user.id = author_id'], |
||
95 | * ['LEFT JOIN', 'team', 'team.id = team_id'], |
||
96 | * ] |
||
97 | * ``` |
||
98 | */ |
||
99 | public $join; |
||
100 | /** |
||
101 | * @var string|array|ExpressionInterface the condition to be applied in the GROUP BY clause. |
||
102 | * It can be either a string or an array. Please refer to [[where()]] on how to specify the condition. |
||
103 | */ |
||
104 | public $having; |
||
105 | /** |
||
106 | * @var array this is used to construct the UNION clause(s) in a SQL statement. |
||
107 | * Each array element is an array of the following structure: |
||
108 | * |
||
109 | * - `query`: either a string or a [[Query]] object representing a query |
||
110 | * - `all`: boolean, whether it should be `UNION ALL` or `UNION` |
||
111 | */ |
||
112 | public $union; |
||
113 | /** |
||
114 | * @var array list of query parameter values indexed by parameter placeholders. |
||
115 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
116 | */ |
||
117 | public $params = []; |
||
118 | /** |
||
119 | * @var int|true the default number of seconds that query results can remain valid in cache. |
||
120 | * Use 0 to indicate that the cached data will never expire. |
||
121 | * Use a negative number to indicate that query cache should not be used. |
||
122 | * Use boolean `true` to indicate that [[Connection::queryCacheDuration]] should be used. |
||
123 | * @see cache() |
||
124 | * @since 2.0.14 |
||
125 | */ |
||
126 | public $queryCacheDuration; |
||
127 | /** |
||
128 | * @var \yii\caching\Dependency the dependency to be associated with the cached query result for this query |
||
129 | * @see cache() |
||
130 | * @since 2.0.14 |
||
131 | */ |
||
132 | public $queryCacheDependency; |
||
133 | |||
134 | |||
135 | /** |
||
136 | * Creates a DB command that can be used to execute this query. |
||
137 | * @param Connection $db the database connection used to generate the SQL statement. |
||
138 | * If this parameter is not given, the `db` application component will be used. |
||
139 | * @return Command the created DB command instance. |
||
140 | */ |
||
141 | 399 | public function createCommand($db = null) |
|
142 | { |
||
143 | 399 | if ($db === null) { |
|
144 | 45 | $db = Yii::$app->getDb(); |
|
145 | } |
||
146 | 399 | list($sql, $params) = $db->getQueryBuilder()->build($this); |
|
147 | |||
148 | 399 | $command = $db->createCommand($sql, $params); |
|
149 | 399 | $this->setCommandCache($command); |
|
150 | |||
151 | 399 | return $command; |
|
152 | } |
||
153 | |||
154 | /** |
||
155 | * Prepares for building SQL. |
||
156 | * This method is called by [[QueryBuilder]] when it starts to build SQL from a query object. |
||
157 | * You may override this method to do some final preparation work when converting a query into a SQL statement. |
||
158 | * @param QueryBuilder $builder |
||
159 | * @return $this a prepared query instance which will be used by [[QueryBuilder]] to build the SQL |
||
160 | */ |
||
161 | 883 | public function prepare($builder) |
|
162 | { |
||
163 | 883 | return $this; |
|
164 | } |
||
165 | |||
166 | /** |
||
167 | * Starts a batch query. |
||
168 | * |
||
169 | * A batch query supports fetching data in batches, which can keep the memory usage under a limit. |
||
170 | * This method will return a [[BatchQueryResult]] object which implements the [[\Iterator]] interface |
||
171 | * and can be traversed to retrieve the data in batches. |
||
172 | * |
||
173 | * For example, |
||
174 | * |
||
175 | * ```php |
||
176 | * $query = (new Query)->from('user'); |
||
177 | * foreach ($query->batch() as $rows) { |
||
178 | * // $rows is an array of 100 or fewer rows from user table |
||
179 | * } |
||
180 | * ``` |
||
181 | * |
||
182 | * @param int $batchSize the number of records to be fetched in each batch. |
||
183 | * @param Connection $db the database connection. If not set, the "db" application component will be used. |
||
184 | * @return BatchQueryResult the batch query result. It implements the [[\Iterator]] interface |
||
185 | * and can be traversed to retrieve the data in batches. |
||
186 | */ |
||
187 | 6 | public function batch($batchSize = 100, $db = null) |
|
188 | { |
||
189 | 6 | return Yii::createObject([ |
|
190 | 6 | 'class' => BatchQueryResult::className(), |
|
191 | 6 | 'query' => $this, |
|
192 | 6 | 'batchSize' => $batchSize, |
|
193 | 6 | 'db' => $db, |
|
194 | 'each' => false, |
||
195 | ]); |
||
196 | } |
||
197 | |||
198 | /** |
||
199 | * Starts a batch query and retrieves data row by row. |
||
200 | * |
||
201 | * This method is similar to [[batch()]] except that in each iteration of the result, |
||
202 | * only one row of data is returned. For example, |
||
203 | * |
||
204 | * ```php |
||
205 | * $query = (new Query)->from('user'); |
||
206 | * foreach ($query->each() as $row) { |
||
207 | * } |
||
208 | * ``` |
||
209 | * |
||
210 | * @param int $batchSize the number of records to be fetched in each batch. |
||
211 | * @param Connection $db the database connection. If not set, the "db" application component will be used. |
||
212 | * @return BatchQueryResult the batch query result. It implements the [[\Iterator]] interface |
||
213 | * and can be traversed to retrieve the data in batches. |
||
214 | */ |
||
215 | 3 | public function each($batchSize = 100, $db = null) |
|
216 | { |
||
217 | 3 | return Yii::createObject([ |
|
218 | 3 | 'class' => BatchQueryResult::className(), |
|
219 | 3 | 'query' => $this, |
|
220 | 3 | 'batchSize' => $batchSize, |
|
221 | 3 | 'db' => $db, |
|
222 | 'each' => true, |
||
223 | ]); |
||
224 | } |
||
225 | |||
226 | /** |
||
227 | * Executes the query and returns all results as an array. |
||
228 | * @param Connection $db the database connection used to generate the SQL statement. |
||
229 | * If this parameter is not given, the `db` application component will be used. |
||
230 | * @return array the query results. If the query results in nothing, an empty array will be returned. |
||
231 | */ |
||
232 | 520 | public function all($db = null) |
|
233 | { |
||
234 | 520 | if ($this->emulateExecution) { |
|
235 | 12 | return []; |
|
236 | } |
||
237 | 512 | $rows = $this->createCommand($db)->queryAll(); |
|
238 | 512 | return $this->populate($rows); |
|
239 | } |
||
240 | |||
241 | /** |
||
242 | * Converts the raw query results into the format as specified by this query. |
||
243 | * This method is internally used to convert the data fetched from database |
||
244 | * into the format as required by this query. |
||
245 | * @param array $rows the raw query result from database |
||
246 | * @return array the converted query result |
||
247 | */ |
||
248 | 681 | public function populate($rows) |
|
249 | { |
||
250 | 681 | if ($this->indexBy === null) { |
|
251 | 673 | return $rows; |
|
252 | } |
||
253 | 31 | $result = []; |
|
254 | 31 | foreach ($rows as $row) { |
|
255 | 31 | $result[ArrayHelper::getValue($row, $this->indexBy)] = $row; |
|
256 | } |
||
257 | |||
258 | 31 | return $result; |
|
259 | } |
||
260 | |||
261 | /** |
||
262 | * Executes the query and returns a single row of result. |
||
263 | * @param Connection $db the database connection used to generate the SQL statement. |
||
264 | * If this parameter is not given, the `db` application component will be used. |
||
265 | * @return array|bool the first row (in terms of an array) of the query result. False is returned if the query |
||
266 | * results in nothing. |
||
267 | */ |
||
268 | 556 | public function one($db = null) |
|
269 | { |
||
270 | 556 | if ($this->emulateExecution) { |
|
271 | 8 | return false; |
|
272 | } |
||
273 | |||
274 | 548 | return $this->createCommand($db)->queryOne(); |
|
275 | } |
||
276 | |||
277 | /** |
||
278 | * Returns the query result as a scalar value. |
||
279 | * The value returned will be the first column in the first row of the query results. |
||
280 | * @param Connection $db the database connection used to generate the SQL statement. |
||
281 | * If this parameter is not given, the `db` application component will be used. |
||
282 | * @return string|null|false the value of the first column in the first row of the query result. |
||
283 | * False is returned if the query result is empty. |
||
284 | */ |
||
285 | 44 | public function scalar($db = null) |
|
286 | { |
||
287 | 44 | if ($this->emulateExecution) { |
|
288 | 8 | return null; |
|
289 | } |
||
290 | |||
291 | 36 | return $this->createCommand($db)->queryScalar(); |
|
292 | } |
||
293 | |||
294 | /** |
||
295 | * Executes the query and returns the first column of the result. |
||
296 | * @param Connection $db the database connection used to generate the SQL statement. |
||
297 | * If this parameter is not given, the `db` application component will be used. |
||
298 | * @return array the first column of the query result. An empty array is returned if the query results in nothing. |
||
299 | */ |
||
300 | 89 | public function column($db = null) |
|
301 | { |
||
302 | 89 | if ($this->emulateExecution) { |
|
303 | 8 | return []; |
|
304 | } |
||
305 | |||
306 | 81 | if ($this->indexBy === null) { |
|
307 | 75 | return $this->createCommand($db)->queryColumn(); |
|
308 | } |
||
309 | |||
310 | 10 | if (is_string($this->indexBy) && is_array($this->select) && count($this->select) === 1) { |
|
311 | 10 | if (strpos($this->indexBy, '.') === false && count($tables = $this->getTablesUsedInFrom()) > 0) { |
|
312 | 10 | $this->select[] = key($tables) . '.' . $this->indexBy; |
|
313 | } else { |
||
314 | $this->select[] = $this->indexBy; |
||
315 | } |
||
316 | } |
||
317 | 10 | $rows = $this->createCommand($db)->queryAll(); |
|
318 | 10 | $results = []; |
|
319 | 10 | foreach ($rows as $row) { |
|
320 | 10 | $value = reset($row); |
|
321 | |||
322 | 10 | if ($this->indexBy instanceof \Closure) { |
|
323 | 4 | $results[call_user_func($this->indexBy, $row)] = $value; |
|
324 | } else { |
||
325 | 10 | $results[$row[$this->indexBy]] = $value; |
|
326 | } |
||
327 | } |
||
328 | |||
329 | 10 | return $results; |
|
330 | } |
||
331 | |||
332 | /** |
||
333 | * Returns the number of records. |
||
334 | * @param string $q the COUNT expression. Defaults to '*'. |
||
335 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
336 | * @param Connection $db the database connection used to generate the SQL statement. |
||
337 | * If this parameter is not given (or null), the `db` application component will be used. |
||
338 | * @return int|string number of records. The result may be a string depending on the |
||
339 | * underlying database engine and to support integer values higher than a 32bit PHP integer can handle. |
||
340 | */ |
||
341 | 111 | public function count($q = '*', $db = null) |
|
342 | { |
||
343 | 111 | if ($this->emulateExecution) { |
|
344 | 8 | return 0; |
|
345 | } |
||
346 | |||
347 | 111 | return $this->queryScalar("COUNT($q)", $db); |
|
348 | } |
||
349 | |||
350 | /** |
||
351 | * Returns the sum of the specified column values. |
||
352 | * @param string $q the column name or expression. |
||
353 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
354 | * @param Connection $db the database connection used to generate the SQL statement. |
||
355 | * If this parameter is not given, the `db` application component will be used. |
||
356 | * @return mixed the sum of the specified column values. |
||
357 | */ |
||
358 | 12 | public function sum($q, $db = null) |
|
359 | { |
||
360 | 12 | if ($this->emulateExecution) { |
|
361 | 8 | return 0; |
|
362 | } |
||
363 | |||
364 | 4 | return $this->queryScalar("SUM($q)", $db); |
|
365 | } |
||
366 | |||
367 | /** |
||
368 | * Returns the average of the specified column values. |
||
369 | * @param string $q the column name or expression. |
||
370 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
371 | * @param Connection $db the database connection used to generate the SQL statement. |
||
372 | * If this parameter is not given, the `db` application component will be used. |
||
373 | * @return mixed the average of the specified column values. |
||
374 | */ |
||
375 | 12 | public function average($q, $db = null) |
|
376 | { |
||
377 | 12 | if ($this->emulateExecution) { |
|
378 | 8 | return 0; |
|
379 | } |
||
380 | |||
381 | 4 | return $this->queryScalar("AVG($q)", $db); |
|
382 | } |
||
383 | |||
384 | /** |
||
385 | * Returns the minimum of the specified column values. |
||
386 | * @param string $q the column name or expression. |
||
387 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
388 | * @param Connection $db the database connection used to generate the SQL statement. |
||
389 | * If this parameter is not given, the `db` application component will be used. |
||
390 | * @return mixed the minimum of the specified column values. |
||
391 | */ |
||
392 | 12 | public function min($q, $db = null) |
|
393 | { |
||
394 | 12 | return $this->queryScalar("MIN($q)", $db); |
|
395 | } |
||
396 | |||
397 | /** |
||
398 | * Returns the maximum of the specified column values. |
||
399 | * @param string $q the column name or expression. |
||
400 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
401 | * @param Connection $db the database connection used to generate the SQL statement. |
||
402 | * If this parameter is not given, the `db` application component will be used. |
||
403 | * @return mixed the maximum of the specified column values. |
||
404 | */ |
||
405 | 12 | public function max($q, $db = null) |
|
406 | { |
||
407 | 12 | return $this->queryScalar("MAX($q)", $db); |
|
408 | } |
||
409 | |||
410 | /** |
||
411 | * Returns a value indicating whether the query result contains any row of data. |
||
412 | * @param Connection $db the database connection used to generate the SQL statement. |
||
413 | * If this parameter is not given, the `db` application component will be used. |
||
414 | * @return bool whether the query result contains any row of data. |
||
415 | */ |
||
416 | 88 | public function exists($db = null) |
|
417 | { |
||
418 | 88 | if ($this->emulateExecution) { |
|
419 | 8 | return false; |
|
420 | } |
||
421 | 80 | $command = $this->createCommand($db); |
|
422 | 80 | $params = $command->params; |
|
423 | 80 | $command->setSql($command->db->getQueryBuilder()->selectExists($command->getSql())); |
|
424 | 80 | $command->bindValues($params); |
|
425 | 80 | return (bool) $command->queryScalar(); |
|
426 | } |
||
427 | |||
428 | /** |
||
429 | * Queries a scalar value by setting [[select]] first. |
||
430 | * Restores the value of select to make this query reusable. |
||
431 | * @param string|ExpressionInterface $selectExpression |
||
432 | * @param Connection|null $db |
||
433 | * @return bool|string |
||
434 | */ |
||
435 | 111 | protected function queryScalar($selectExpression, $db) |
|
436 | { |
||
437 | 111 | if ($this->emulateExecution) { |
|
438 | 8 | return null; |
|
439 | } |
||
440 | |||
441 | if ( |
||
442 | 111 | !$this->distinct |
|
443 | 111 | && empty($this->groupBy) |
|
444 | 111 | && empty($this->having) |
|
445 | 111 | && empty($this->union) |
|
446 | ) { |
||
447 | 110 | $select = $this->select; |
|
448 | 110 | $order = $this->orderBy; |
|
449 | 110 | $limit = $this->limit; |
|
450 | 110 | $offset = $this->offset; |
|
451 | |||
452 | 110 | $this->select = [$selectExpression]; |
|
453 | 110 | $this->orderBy = null; |
|
454 | 110 | $this->limit = null; |
|
455 | 110 | $this->offset = null; |
|
456 | 110 | $command = $this->createCommand($db); |
|
457 | |||
458 | 110 | $this->select = $select; |
|
459 | 110 | $this->orderBy = $order; |
|
460 | 110 | $this->limit = $limit; |
|
461 | 110 | $this->offset = $offset; |
|
462 | |||
463 | 110 | return $command->queryScalar(); |
|
464 | } |
||
465 | |||
466 | 8 | $command = (new self()) |
|
467 | 8 | ->select([$selectExpression]) |
|
468 | 8 | ->from(['c' => $this]) |
|
469 | 8 | ->createCommand($db); |
|
470 | 8 | $this->setCommandCache($command); |
|
471 | |||
472 | 8 | return $command->queryScalar(); |
|
473 | } |
||
474 | |||
475 | /** |
||
476 | * Returns table names used in [[from]] indexed by aliases. |
||
477 | * Both aliases and names are enclosed into {{ and }}. |
||
478 | * @return string[] table names indexed by aliases |
||
479 | * @throws \yii\base\InvalidConfigException |
||
480 | * @since 2.0.12 |
||
481 | */ |
||
482 | 159 | public function getTablesUsedInFrom() |
|
483 | { |
||
484 | 159 | if (empty($this->from)) { |
|
485 | return []; |
||
486 | } |
||
487 | |||
488 | 159 | if (is_array($this->from)) { |
|
489 | 111 | $tableNames = $this->from; |
|
490 | 48 | } elseif (is_string($this->from)) { |
|
491 | 32 | $tableNames = preg_split('/\s*,\s*/', trim($this->from), -1, PREG_SPLIT_NO_EMPTY); |
|
492 | 16 | } elseif ($this->from instanceof Expression) { |
|
493 | 8 | $tableNames = [$this->from]; |
|
494 | } else { |
||
495 | 8 | throw new InvalidConfigException(gettype($this->from) . ' in $from is not supported.'); |
|
496 | } |
||
497 | |||
498 | 151 | return $this->cleanUpTableNames($tableNames); |
|
499 | } |
||
500 | |||
501 | /** |
||
502 | * Clean up table names and aliases |
||
503 | * Both aliases and names are enclosed into {{ and }}. |
||
504 | * @param array $tableNames non-empty array |
||
505 | * @return string[] table names indexed by aliases |
||
506 | * @since 2.0.14 |
||
507 | */ |
||
508 | 348 | protected function cleanUpTableNames($tableNames) |
|
509 | { |
||
510 | 348 | $cleanedUpTableNames = []; |
|
511 | 348 | foreach ($tableNames as $alias => $tableName) { |
|
512 | 348 | if (is_string($tableName) && !is_string($alias)) { |
|
513 | $pattern = <<<PATTERN |
||
514 | 268 | ~ |
|
515 | ^ |
||
516 | \s* |
||
517 | ( |
||
518 | (?:['"`\[]|{{) |
||
519 | .*? |
||
520 | (?:['"`\]]|}}) |
||
521 | | |
||
522 | \(.*?\) |
||
523 | | |
||
524 | .*? |
||
525 | ) |
||
526 | (?: |
||
527 | (?: |
||
528 | \s+ |
||
529 | (?:as)? |
||
530 | \s* |
||
531 | ) |
||
532 | ( |
||
533 | (?:['"`\[]|{{) |
||
534 | .*? |
||
535 | (?:['"`\]]|}}) |
||
536 | | |
||
537 | .*? |
||
538 | ) |
||
539 | )? |
||
540 | \s* |
||
541 | $ |
||
542 | ~iux |
||
543 | PATTERN; |
||
544 | 268 | if (preg_match($pattern, $tableName, $matches)) { |
|
545 | 268 | if (isset($matches[2])) { |
|
546 | 24 | list(, $tableName, $alias) = $matches; |
|
547 | } else { |
||
548 | 260 | $tableName = $alias = $matches[1]; |
|
549 | } |
||
550 | } |
||
551 | } |
||
552 | |||
553 | |||
554 | 348 | if ($tableName instanceof Expression) { |
|
555 | 16 | if (!is_string($alias)) { |
|
556 | 8 | throw new InvalidArgumentException('To use Expression in from() method, pass it in array format with alias.'); |
|
557 | } |
||
558 | 8 | $cleanedUpTableNames[$this->ensureNameQuoted($alias)] = $tableName; |
|
559 | 332 | } elseif ($tableName instanceof self) { |
|
560 | 8 | $cleanedUpTableNames[$this->ensureNameQuoted($alias)] = $tableName; |
|
561 | } else { |
||
562 | 340 | $cleanedUpTableNames[$this->ensureNameQuoted($alias)] = $this->ensureNameQuoted($tableName); |
|
563 | } |
||
564 | } |
||
565 | |||
566 | 340 | return $cleanedUpTableNames; |
|
567 | } |
||
568 | |||
569 | /** |
||
570 | * Ensures name is wrapped with {{ and }} |
||
571 | * @param string $name |
||
572 | * @return string |
||
573 | */ |
||
574 | 340 | private function ensureNameQuoted($name) |
|
575 | { |
||
576 | 340 | $name = str_replace(["'", '"', '`', '[', ']'], '', $name); |
|
577 | 340 | if ($name && !preg_match('/^{{.*}}$/', $name)) { |
|
578 | 325 | return '{{' . $name . '}}'; |
|
579 | } |
||
580 | |||
581 | 39 | return $name; |
|
582 | } |
||
583 | |||
584 | /** |
||
585 | * Sets the SELECT part of the query. |
||
586 | * @param string|array|ExpressionInterface $columns the columns to be selected. |
||
587 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
588 | * Columns can be prefixed with table names (e.g. "user.id") and/or contain column aliases (e.g. "user.id AS user_id"). |
||
589 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
590 | * (which means the column contains a DB expression). A DB expression may also be passed in form of |
||
591 | * an [[ExpressionInterface]] object. |
||
592 | * |
||
593 | * Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should |
||
594 | * use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts. |
||
595 | * |
||
596 | * When the columns are specified as an array, you may also use array keys as the column aliases (if a column |
||
597 | * does not need alias, do not use a string key). |
||
598 | * |
||
599 | * Starting from version 2.0.1, you may also select sub-queries as columns by specifying each such column |
||
600 | * as a `Query` instance representing the sub-query. |
||
601 | * |
||
602 | * @param string $option additional option that should be appended to the 'SELECT' keyword. For example, |
||
603 | * in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used. |
||
604 | * @return $this the query object itself |
||
605 | */ |
||
606 | 463 | public function select($columns, $option = null) |
|
607 | { |
||
608 | 463 | $this->select = $this->normalizeSelect($columns); |
|
609 | 463 | $this->selectOption = $option; |
|
610 | 463 | return $this; |
|
611 | } |
||
612 | |||
613 | /** |
||
614 | * Add more columns to the SELECT part of the query. |
||
615 | * |
||
616 | * Note, that if [[select]] has not been specified before, you should include `*` explicitly |
||
617 | * if you want to select all remaining columns too: |
||
618 | * |
||
619 | * ```php |
||
620 | * $query->addSelect(["*", "CONCAT(first_name, ' ', last_name) AS full_name"])->one(); |
||
621 | * ``` |
||
622 | * |
||
623 | * @param string|array|ExpressionInterface $columns the columns to add to the select. See [[select()]] for more |
||
624 | * details about the format of this parameter. |
||
625 | * @return $this the query object itself |
||
626 | * @see select() |
||
627 | */ |
||
628 | 12 | public function addSelect($columns) |
|
629 | { |
||
630 | 12 | if ($this->select === null) { |
|
631 | 4 | return $this->select($columns); |
|
632 | } |
||
633 | 12 | if (!is_array($this->select)) { |
|
0 ignored issues
–
show
introduced
by
Loading history...
|
|||
634 | $this->select = $this->normalizeSelect($this->select); |
||
635 | } |
||
636 | 12 | $this->select = array_merge($this->select, $this->normalizeSelect($columns)); |
|
637 | |||
638 | 12 | return $this; |
|
639 | } |
||
640 | |||
641 | /** |
||
642 | * Normalizes the SELECT columns passed to [[select()]] or [[addSelect()]]. |
||
643 | * |
||
644 | * @param string|array|ExpressionInterface $columns |
||
645 | * @return array |
||
646 | * @since 2.0.21 |
||
647 | */ |
||
648 | 463 | protected function normalizeSelect($columns) |
|
649 | { |
||
650 | 463 | if ($columns instanceof ExpressionInterface) { |
|
651 | 4 | $columns = [$columns]; |
|
652 | 463 | } elseif (!is_array($columns)) { |
|
653 | 127 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
654 | } |
||
655 | 463 | $select = []; |
|
656 | 463 | foreach ($columns as $columnAlias => $columnDefinition) { |
|
657 | 459 | if (is_string($columnAlias)) { |
|
658 | // Already in the normalized format, good for them |
||
659 | 63 | $select[$columnAlias] = $columnDefinition; |
|
660 | 63 | continue; |
|
661 | } |
||
662 | 453 | if (is_string($columnDefinition)) { |
|
663 | if ( |
||
664 | 449 | preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $columnDefinition, $matches) && |
|
665 | 449 | !preg_match('/^\d+$/', $matches[2]) && |
|
666 | 449 | strpos($matches[2], '.') === false |
|
667 | ) { |
||
668 | // Using "columnName as alias" or "columnName alias" syntax |
||
669 | 18 | $select[$matches[2]] = $matches[1]; |
|
670 | 18 | continue; |
|
671 | } |
||
672 | 443 | if (strpos($columnDefinition, '(') === false) { |
|
673 | // Normal column name, just alias it to itself to ensure it's not selected twice |
||
674 | 430 | $select[$columnDefinition] = $columnDefinition; |
|
675 | 430 | continue; |
|
676 | } |
||
677 | } |
||
678 | // Either a string calling a function, DB expression, or sub-query |
||
679 | 45 | $select[] = $columnDefinition; |
|
680 | } |
||
681 | 463 | return $select; |
|
682 | } |
||
683 | |||
684 | /** |
||
685 | * Returns unique column names excluding duplicates. |
||
686 | * Columns to be removed: |
||
687 | * - if column definition already present in SELECT part with same alias |
||
688 | * - if column definition without alias already present in SELECT part without alias too |
||
689 | * @param array $columns the columns to be merged to the select. |
||
690 | * @since 2.0.14 |
||
691 | * @deprecated in 2.0.21 |
||
692 | */ |
||
693 | protected function getUniqueColumns($columns) |
||
694 | { |
||
695 | $unaliasedColumns = $this->getUnaliasedColumnsFromSelect(); |
||
696 | |||
697 | $result = []; |
||
698 | foreach ($columns as $columnAlias => $columnDefinition) { |
||
699 | if (!$columnDefinition instanceof Query) { |
||
700 | if (is_string($columnAlias)) { |
||
701 | $existsInSelect = isset($this->select[$columnAlias]) && $this->select[$columnAlias] === $columnDefinition; |
||
702 | if ($existsInSelect) { |
||
703 | continue; |
||
704 | } |
||
705 | } elseif (is_int($columnAlias)) { |
||
706 | $existsInSelect = in_array($columnDefinition, $unaliasedColumns, true); |
||
707 | $existsInResultSet = in_array($columnDefinition, $result, true); |
||
708 | if ($existsInSelect || $existsInResultSet) { |
||
709 | continue; |
||
710 | } |
||
711 | } |
||
712 | } |
||
713 | |||
714 | $result[$columnAlias] = $columnDefinition; |
||
715 | } |
||
716 | return $result; |
||
717 | } |
||
718 | |||
719 | /** |
||
720 | * @return array List of columns without aliases from SELECT statement. |
||
721 | * @since 2.0.14 |
||
722 | * @deprecated in 2.0.21 |
||
723 | */ |
||
724 | protected function getUnaliasedColumnsFromSelect() |
||
725 | { |
||
726 | $result = []; |
||
727 | if (is_array($this->select)) { |
||
728 | foreach ($this->select as $name => $value) { |
||
729 | if (is_int($name)) { |
||
730 | $result[] = $value; |
||
731 | } |
||
732 | } |
||
733 | } |
||
734 | return array_unique($result); |
||
735 | } |
||
736 | |||
737 | /** |
||
738 | * Sets the value indicating whether to SELECT DISTINCT or not. |
||
739 | * @param bool $value whether to SELECT DISTINCT or not. |
||
740 | * @return $this the query object itself |
||
741 | */ |
||
742 | 8 | public function distinct($value = true) |
|
743 | { |
||
744 | 8 | $this->distinct = $value; |
|
745 | 8 | return $this; |
|
746 | } |
||
747 | |||
748 | /** |
||
749 | * Sets the FROM part of the query. |
||
750 | * @param string|array|ExpressionInterface $tables the table(s) to be selected from. This can be either a string (e.g. `'user'`) |
||
751 | * or an array (e.g. `['user', 'profile']`) specifying one or several table names. |
||
752 | * Table names can contain schema prefixes (e.g. `'public.user'`) and/or table aliases (e.g. `'user u'`). |
||
753 | * The method will automatically quote the table names unless it contains some parenthesis |
||
754 | * (which means the table is given as a sub-query or DB expression). |
||
755 | * |
||
756 | * When the tables are specified as an array, you may also use the array keys as the table aliases |
||
757 | * (if a table does not need alias, do not use a string key). |
||
758 | * |
||
759 | * Use a Query object to represent a sub-query. In this case, the corresponding array key will be used |
||
760 | * as the alias for the sub-query. |
||
761 | * |
||
762 | * To specify the `FROM` part in plain SQL, you may pass an instance of [[ExpressionInterface]]. |
||
763 | * |
||
764 | * Here are some examples: |
||
765 | * |
||
766 | * ```php |
||
767 | * // SELECT * FROM `user` `u`, `profile`; |
||
768 | * $query = (new \yii\db\Query)->from(['u' => 'user', 'profile']); |
||
769 | * |
||
770 | * // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; |
||
771 | * $subquery = (new \yii\db\Query)->from('user')->where(['active' => true]) |
||
772 | * $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); |
||
773 | * |
||
774 | * // subquery can also be a string with plain SQL wrapped in parenthesis |
||
775 | * // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; |
||
776 | * $subquery = "(SELECT * FROM `user` WHERE `active` = 1)"; |
||
777 | * $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); |
||
778 | * ``` |
||
779 | * |
||
780 | * @return $this the query object itself |
||
781 | */ |
||
782 | 526 | public function from($tables) |
|
783 | { |
||
784 | 526 | if ($tables instanceof ExpressionInterface) { |
|
785 | 8 | $tables = [$tables]; |
|
786 | } |
||
787 | 526 | if (is_string($tables)) { |
|
788 | 464 | $tables = preg_split('/\s*,\s*/', trim($tables), -1, PREG_SPLIT_NO_EMPTY); |
|
789 | } |
||
790 | 526 | $this->from = $tables; |
|
791 | 526 | return $this; |
|
792 | } |
||
793 | |||
794 | /** |
||
795 | * Sets the WHERE part of the query. |
||
796 | * |
||
797 | * The method requires a `$condition` parameter, and optionally a `$params` parameter |
||
798 | * specifying the values to be bound to the query. |
||
799 | * |
||
800 | * The `$condition` parameter should be either a string (e.g. `'id=1'`) or an array. |
||
801 | * |
||
802 | * {@inheritdoc} |
||
803 | * |
||
804 | * @param string|array|ExpressionInterface $condition the conditions that should be put in the WHERE part. |
||
805 | * @param array $params the parameters (name => value) to be bound to the query. |
||
806 | * @return $this the query object itself |
||
807 | * @see andWhere() |
||
808 | * @see orWhere() |
||
809 | * @see QueryInterface::where() |
||
810 | */ |
||
811 | 855 | public function where($condition, $params = []) |
|
812 | { |
||
813 | 855 | $this->where = $condition; |
|
814 | 855 | $this->addParams($params); |
|
815 | 855 | return $this; |
|
816 | } |
||
817 | |||
818 | /** |
||
819 | * Adds an additional WHERE condition to the existing one. |
||
820 | * The new condition and the existing one will be joined using the `AND` operator. |
||
821 | * @param string|array|ExpressionInterface $condition the new WHERE condition. Please refer to [[where()]] |
||
822 | * on how to specify this parameter. |
||
823 | * @param array $params the parameters (name => value) to be bound to the query. |
||
824 | * @return $this the query object itself |
||
825 | * @see where() |
||
826 | * @see orWhere() |
||
827 | */ |
||
828 | 498 | public function andWhere($condition, $params = []) |
|
829 | { |
||
830 | 498 | if ($this->where === null) { |
|
831 | 439 | $this->where = $condition; |
|
832 | 129 | } elseif (is_array($this->where) && isset($this->where[0]) && strcasecmp($this->where[0], 'and') === 0) { |
|
833 | 39 | $this->where[] = $condition; |
|
834 | } else { |
||
835 | 129 | $this->where = ['and', $this->where, $condition]; |
|
836 | } |
||
837 | 498 | $this->addParams($params); |
|
838 | 498 | return $this; |
|
839 | } |
||
840 | |||
841 | /** |
||
842 | * Adds an additional WHERE condition to the existing one. |
||
843 | * The new condition and the existing one will be joined using the `OR` operator. |
||
844 | * @param string|array|ExpressionInterface $condition the new WHERE condition. Please refer to [[where()]] |
||
845 | * on how to specify this parameter. |
||
846 | * @param array $params the parameters (name => value) to be bound to the query. |
||
847 | * @return $this the query object itself |
||
848 | * @see where() |
||
849 | * @see andWhere() |
||
850 | */ |
||
851 | 9 | public function orWhere($condition, $params = []) |
|
852 | { |
||
853 | 9 | if ($this->where === null) { |
|
854 | $this->where = $condition; |
||
855 | } else { |
||
856 | 9 | $this->where = ['or', $this->where, $condition]; |
|
857 | } |
||
858 | 9 | $this->addParams($params); |
|
859 | 9 | return $this; |
|
860 | } |
||
861 | |||
862 | /** |
||
863 | * Adds a filtering condition for a specific column and allow the user to choose a filter operator. |
||
864 | * |
||
865 | * It adds an additional WHERE condition for the given field and determines the comparison operator |
||
866 | * based on the first few characters of the given value. |
||
867 | * The condition is added in the same way as in [[andFilterWhere]] so [[isEmpty()|empty values]] are ignored. |
||
868 | * The new condition and the existing one will be joined using the `AND` operator. |
||
869 | * |
||
870 | * The comparison operator is intelligently determined based on the first few characters in the given value. |
||
871 | * In particular, it recognizes the following operators if they appear as the leading characters in the given value: |
||
872 | * |
||
873 | * - `<`: the column must be less than the given value. |
||
874 | * - `>`: the column must be greater than the given value. |
||
875 | * - `<=`: the column must be less than or equal to the given value. |
||
876 | * - `>=`: the column must be greater than or equal to the given value. |
||
877 | * - `<>`: the column must not be the same as the given value. |
||
878 | * - `=`: the column must be equal to the given value. |
||
879 | * - If none of the above operators is detected, the `$defaultOperator` will be used. |
||
880 | * |
||
881 | * @param string $name the column name. |
||
882 | * @param string $value the column value optionally prepended with the comparison operator. |
||
883 | * @param string $defaultOperator The operator to use, when no operator is given in `$value`. |
||
884 | * Defaults to `=`, performing an exact match. |
||
885 | * @return $this The query object itself |
||
886 | * @since 2.0.8 |
||
887 | */ |
||
888 | 4 | public function andFilterCompare($name, $value, $defaultOperator = '=') |
|
889 | { |
||
890 | 4 | if (preg_match('/^(<>|>=|>|<=|<|=)/', $value, $matches)) { |
|
891 | 4 | $operator = $matches[1]; |
|
892 | 4 | $value = substr($value, strlen($operator)); |
|
893 | } else { |
||
894 | 4 | $operator = $defaultOperator; |
|
895 | } |
||
896 | |||
897 | 4 | return $this->andFilterWhere([$operator, $name, $value]); |
|
898 | } |
||
899 | |||
900 | /** |
||
901 | * Appends a JOIN part to the query. |
||
902 | * The first parameter specifies what type of join it is. |
||
903 | * @param string $type the type of join, such as INNER JOIN, LEFT JOIN. |
||
904 | * @param string|array $table the table to be joined. |
||
905 | * |
||
906 | * Use a string to represent the name of the table to be joined. |
||
907 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
908 | * The method will automatically quote the table name unless it contains some parenthesis |
||
909 | * (which means the table is given as a sub-query or DB expression). |
||
910 | * |
||
911 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
912 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
913 | * represents the alias for the sub-query. |
||
914 | * |
||
915 | * @param string|array $on the join condition that should appear in the ON part. |
||
916 | * Please refer to [[where()]] on how to specify this parameter. |
||
917 | * |
||
918 | * Note that the array format of [[where()]] is designed to match columns to values instead of columns to columns, so |
||
919 | * the following would **not** work as expected: `['post.author_id' => 'user.id']`, it would |
||
920 | * match the `post.author_id` column value against the string `'user.id'`. |
||
921 | * It is recommended to use the string syntax here which is more suited for a join: |
||
922 | * |
||
923 | * ```php |
||
924 | * 'post.author_id = user.id' |
||
925 | * ``` |
||
926 | * |
||
927 | * @param array $params the parameters (name => value) to be bound to the query. |
||
928 | * @return $this the query object itself |
||
929 | */ |
||
930 | 69 | public function join($type, $table, $on = '', $params = []) |
|
931 | { |
||
932 | 69 | $this->join[] = [$type, $table, $on]; |
|
933 | 69 | return $this->addParams($params); |
|
934 | } |
||
935 | |||
936 | /** |
||
937 | * Appends an INNER JOIN part to the query. |
||
938 | * @param string|array $table the table to be joined. |
||
939 | * |
||
940 | * Use a string to represent the name of the table to be joined. |
||
941 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
942 | * The method will automatically quote the table name unless it contains some parenthesis |
||
943 | * (which means the table is given as a sub-query or DB expression). |
||
944 | * |
||
945 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
946 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
947 | * represents the alias for the sub-query. |
||
948 | * |
||
949 | * @param string|array $on the join condition that should appear in the ON part. |
||
950 | * Please refer to [[join()]] on how to specify this parameter. |
||
951 | * @param array $params the parameters (name => value) to be bound to the query. |
||
952 | * @return $this the query object itself |
||
953 | */ |
||
954 | 3 | public function innerJoin($table, $on = '', $params = []) |
|
955 | { |
||
956 | 3 | $this->join[] = ['INNER JOIN', $table, $on]; |
|
957 | 3 | return $this->addParams($params); |
|
958 | } |
||
959 | |||
960 | /** |
||
961 | * Appends a LEFT OUTER JOIN part to the query. |
||
962 | * @param string|array $table the table to be joined. |
||
963 | * |
||
964 | * Use a string to represent the name of the table to be joined. |
||
965 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
966 | * The method will automatically quote the table name unless it contains some parenthesis |
||
967 | * (which means the table is given as a sub-query or DB expression). |
||
968 | * |
||
969 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
970 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
971 | * represents the alias for the sub-query. |
||
972 | * |
||
973 | * @param string|array $on the join condition that should appear in the ON part. |
||
974 | * Please refer to [[join()]] on how to specify this parameter. |
||
975 | * @param array $params the parameters (name => value) to be bound to the query |
||
976 | * @return $this the query object itself |
||
977 | */ |
||
978 | 4 | public function leftJoin($table, $on = '', $params = []) |
|
979 | { |
||
980 | 4 | $this->join[] = ['LEFT JOIN', $table, $on]; |
|
981 | 4 | return $this->addParams($params); |
|
982 | } |
||
983 | |||
984 | /** |
||
985 | * Appends a RIGHT OUTER JOIN part to the query. |
||
986 | * @param string|array $table the table to be joined. |
||
987 | * |
||
988 | * Use a string to represent the name of the table to be joined. |
||
989 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
990 | * The method will automatically quote the table name unless it contains some parenthesis |
||
991 | * (which means the table is given as a sub-query or DB expression). |
||
992 | * |
||
993 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
994 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
995 | * represents the alias for the sub-query. |
||
996 | * |
||
997 | * @param string|array $on the join condition that should appear in the ON part. |
||
998 | * Please refer to [[join()]] on how to specify this parameter. |
||
999 | * @param array $params the parameters (name => value) to be bound to the query |
||
1000 | * @return $this the query object itself |
||
1001 | */ |
||
1002 | public function rightJoin($table, $on = '', $params = []) |
||
1003 | { |
||
1004 | $this->join[] = ['RIGHT JOIN', $table, $on]; |
||
1005 | return $this->addParams($params); |
||
1006 | } |
||
1007 | |||
1008 | /** |
||
1009 | * Sets the GROUP BY part of the query. |
||
1010 | * @param string|array|ExpressionInterface $columns the columns to be grouped by. |
||
1011 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
1012 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
1013 | * (which means the column contains a DB expression). |
||
1014 | * |
||
1015 | * Note that if your group-by is an expression containing commas, you should always use an array |
||
1016 | * to represent the group-by information. Otherwise, the method will not be able to correctly determine |
||
1017 | * the group-by columns. |
||
1018 | * |
||
1019 | * Since version 2.0.7, an [[ExpressionInterface]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
||
1020 | * Since version 2.0.14, an [[ExpressionInterface]] object can be passed as well. |
||
1021 | * @return $this the query object itself |
||
1022 | * @see addGroupBy() |
||
1023 | */ |
||
1024 | 29 | public function groupBy($columns) |
|
1025 | { |
||
1026 | 29 | if ($columns instanceof ExpressionInterface) { |
|
1027 | 4 | $columns = [$columns]; |
|
1028 | 29 | } elseif (!is_array($columns)) { |
|
1029 | 29 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
1030 | } |
||
1031 | 29 | $this->groupBy = $columns; |
|
1032 | 29 | return $this; |
|
1033 | } |
||
1034 | |||
1035 | /** |
||
1036 | * Adds additional group-by columns to the existing ones. |
||
1037 | * @param string|array|ExpressionInterface $columns additional columns to be grouped by. |
||
1038 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
1039 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
1040 | * (which means the column contains a DB expression). |
||
1041 | * |
||
1042 | * Note that if your group-by is an expression containing commas, you should always use an array |
||
1043 | * to represent the group-by information. Otherwise, the method will not be able to correctly determine |
||
1044 | * the group-by columns. |
||
1045 | * |
||
1046 | * Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
||
1047 | * Since version 2.0.14, an [[ExpressionInterface]] object can be passed as well. |
||
1048 | * @return $this the query object itself |
||
1049 | * @see groupBy() |
||
1050 | */ |
||
1051 | 4 | public function addGroupBy($columns) |
|
1052 | { |
||
1053 | 4 | if ($columns instanceof ExpressionInterface) { |
|
1054 | $columns = [$columns]; |
||
1055 | 4 | } elseif (!is_array($columns)) { |
|
1056 | 4 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
1057 | } |
||
1058 | 4 | if ($this->groupBy === null) { |
|
1059 | $this->groupBy = $columns; |
||
1060 | } else { |
||
1061 | 4 | $this->groupBy = array_merge($this->groupBy, $columns); |
|
1062 | } |
||
1063 | |||
1064 | 4 | return $this; |
|
1065 | } |
||
1066 | |||
1067 | /** |
||
1068 | * Sets the HAVING part of the query. |
||
1069 | * @param string|array|ExpressionInterface $condition the conditions to be put after HAVING. |
||
1070 | * Please refer to [[where()]] on how to specify this parameter. |
||
1071 | * @param array $params the parameters (name => value) to be bound to the query. |
||
1072 | * @return $this the query object itself |
||
1073 | * @see andHaving() |
||
1074 | * @see orHaving() |
||
1075 | */ |
||
1076 | 13 | public function having($condition, $params = []) |
|
1077 | { |
||
1078 | 13 | $this->having = $condition; |
|
1079 | 13 | $this->addParams($params); |
|
1080 | 13 | return $this; |
|
1081 | } |
||
1082 | |||
1083 | /** |
||
1084 | * Adds an additional HAVING condition to the existing one. |
||
1085 | * The new condition and the existing one will be joined using the `AND` operator. |
||
1086 | * @param string|array|ExpressionInterface $condition the new HAVING condition. Please refer to [[where()]] |
||
1087 | * on how to specify this parameter. |
||
1088 | * @param array $params the parameters (name => value) to be bound to the query. |
||
1089 | * @return $this the query object itself |
||
1090 | * @see having() |
||
1091 | * @see orHaving() |
||
1092 | */ |
||
1093 | 4 | public function andHaving($condition, $params = []) |
|
1094 | { |
||
1095 | 4 | if ($this->having === null) { |
|
1096 | $this->having = $condition; |
||
1097 | } else { |
||
1098 | 4 | $this->having = ['and', $this->having, $condition]; |
|
1099 | } |
||
1100 | 4 | $this->addParams($params); |
|
1101 | 4 | return $this; |
|
1102 | } |
||
1103 | |||
1104 | /** |
||
1105 | * Adds an additional HAVING condition to the existing one. |
||
1106 | * The new condition and the existing one will be joined using the `OR` operator. |
||
1107 | * @param string|array|ExpressionInterface $condition the new HAVING condition. Please refer to [[where()]] |
||
1108 | * on how to specify this parameter. |
||
1109 | * @param array $params the parameters (name => value) to be bound to the query. |
||
1110 | * @return $this the query object itself |
||
1111 | * @see having() |
||
1112 | * @see andHaving() |
||
1113 | */ |
||
1114 | 4 | public function orHaving($condition, $params = []) |
|
1115 | { |
||
1116 | 4 | if ($this->having === null) { |
|
1117 | $this->having = $condition; |
||
1118 | } else { |
||
1119 | 4 | $this->having = ['or', $this->having, $condition]; |
|
1120 | } |
||
1121 | 4 | $this->addParams($params); |
|
1122 | 4 | return $this; |
|
1123 | } |
||
1124 | |||
1125 | /** |
||
1126 | * Sets the HAVING part of the query but ignores [[isEmpty()|empty operands]]. |
||
1127 | * |
||
1128 | * This method is similar to [[having()]]. The main difference is that this method will |
||
1129 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
1130 | * for building query conditions based on filter values entered by users. |
||
1131 | * |
||
1132 | * The following code shows the difference between this method and [[having()]]: |
||
1133 | * |
||
1134 | * ```php |
||
1135 | * // HAVING `age`=:age |
||
1136 | * $query->filterHaving(['name' => null, 'age' => 20]); |
||
1137 | * // HAVING `age`=:age |
||
1138 | * $query->having(['age' => 20]); |
||
1139 | * // HAVING `name` IS NULL AND `age`=:age |
||
1140 | * $query->having(['name' => null, 'age' => 20]); |
||
1141 | * ``` |
||
1142 | * |
||
1143 | * Note that unlike [[having()]], you cannot pass binding parameters to this method. |
||
1144 | * |
||
1145 | * @param array $condition the conditions that should be put in the HAVING part. |
||
1146 | * See [[having()]] on how to specify this parameter. |
||
1147 | * @return $this the query object itself |
||
1148 | * @see having() |
||
1149 | * @see andFilterHaving() |
||
1150 | * @see orFilterHaving() |
||
1151 | * @since 2.0.11 |
||
1152 | */ |
||
1153 | 8 | public function filterHaving(array $condition) |
|
1154 | { |
||
1155 | 8 | $condition = $this->filterCondition($condition); |
|
1156 | 8 | if ($condition !== []) { |
|
1157 | 8 | $this->having($condition); |
|
1158 | } |
||
1159 | |||
1160 | 8 | return $this; |
|
1161 | } |
||
1162 | |||
1163 | /** |
||
1164 | * Adds an additional HAVING condition to the existing one but ignores [[isEmpty()|empty operands]]. |
||
1165 | * The new condition and the existing one will be joined using the `AND` operator. |
||
1166 | * |
||
1167 | * This method is similar to [[andHaving()]]. The main difference is that this method will |
||
1168 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
1169 | * for building query conditions based on filter values entered by users. |
||
1170 | * |
||
1171 | * @param array $condition the new HAVING condition. Please refer to [[having()]] |
||
1172 | * on how to specify this parameter. |
||
1173 | * @return $this the query object itself |
||
1174 | * @see filterHaving() |
||
1175 | * @see orFilterHaving() |
||
1176 | * @since 2.0.11 |
||
1177 | */ |
||
1178 | 8 | public function andFilterHaving(array $condition) |
|
1179 | { |
||
1180 | 8 | $condition = $this->filterCondition($condition); |
|
1181 | 8 | if ($condition !== []) { |
|
1182 | $this->andHaving($condition); |
||
1183 | } |
||
1184 | |||
1185 | 8 | return $this; |
|
1186 | } |
||
1187 | |||
1188 | /** |
||
1189 | * Adds an additional HAVING condition to the existing one but ignores [[isEmpty()|empty operands]]. |
||
1190 | * The new condition and the existing one will be joined using the `OR` operator. |
||
1191 | * |
||
1192 | * This method is similar to [[orHaving()]]. The main difference is that this method will |
||
1193 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
1194 | * for building query conditions based on filter values entered by users. |
||
1195 | * |
||
1196 | * @param array $condition the new HAVING condition. Please refer to [[having()]] |
||
1197 | * on how to specify this parameter. |
||
1198 | * @return $this the query object itself |
||
1199 | * @see filterHaving() |
||
1200 | * @see andFilterHaving() |
||
1201 | * @since 2.0.11 |
||
1202 | */ |
||
1203 | 8 | public function orFilterHaving(array $condition) |
|
1204 | { |
||
1205 | 8 | $condition = $this->filterCondition($condition); |
|
1206 | 8 | if ($condition !== []) { |
|
1207 | $this->orHaving($condition); |
||
1208 | } |
||
1209 | |||
1210 | 8 | return $this; |
|
1211 | } |
||
1212 | |||
1213 | /** |
||
1214 | * Appends a SQL statement using UNION operator. |
||
1215 | * @param string|Query $sql the SQL statement to be appended using UNION |
||
1216 | * @param bool $all TRUE if using UNION ALL and FALSE if using UNION |
||
1217 | * @return $this the query object itself |
||
1218 | */ |
||
1219 | 11 | public function union($sql, $all = false) |
|
1220 | { |
||
1221 | 11 | $this->union[] = ['query' => $sql, 'all' => $all]; |
|
1222 | 11 | return $this; |
|
1223 | } |
||
1224 | |||
1225 | /** |
||
1226 | * Sets the parameters to be bound to the query. |
||
1227 | * @param array $params list of query parameter values indexed by parameter placeholders. |
||
1228 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
1229 | * @return $this the query object itself |
||
1230 | * @see addParams() |
||
1231 | */ |
||
1232 | 8 | public function params($params) |
|
1233 | { |
||
1234 | 8 | $this->params = $params; |
|
1235 | 8 | return $this; |
|
1236 | } |
||
1237 | |||
1238 | /** |
||
1239 | * Adds additional parameters to be bound to the query. |
||
1240 | * @param array $params list of query parameter values indexed by parameter placeholders. |
||
1241 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
1242 | * @return $this the query object itself |
||
1243 | * @see params() |
||
1244 | */ |
||
1245 | 1249 | public function addParams($params) |
|
1246 | { |
||
1247 | 1249 | if (!empty($params)) { |
|
1248 | 98 | if (empty($this->params)) { |
|
1249 | 98 | $this->params = $params; |
|
1250 | } else { |
||
1251 | 8 | foreach ($params as $name => $value) { |
|
1252 | 8 | if (is_int($name)) { |
|
1253 | $this->params[] = $value; |
||
1254 | } else { |
||
1255 | 8 | $this->params[$name] = $value; |
|
1256 | } |
||
1257 | } |
||
1258 | } |
||
1259 | } |
||
1260 | |||
1261 | 1249 | return $this; |
|
1262 | } |
||
1263 | |||
1264 | /** |
||
1265 | * Enables query cache for this Query. |
||
1266 | * @param int|true $duration the number of seconds that query results can remain valid in cache. |
||
1267 | * Use 0 to indicate that the cached data will never expire. |
||
1268 | * Use a negative number to indicate that query cache should not be used. |
||
1269 | * Use boolean `true` to indicate that [[Connection::queryCacheDuration]] should be used. |
||
1270 | * Defaults to `true`. |
||
1271 | * @param \yii\caching\Dependency $dependency the cache dependency associated with the cached result. |
||
1272 | * @return $this the Query object itself |
||
1273 | * @since 2.0.14 |
||
1274 | */ |
||
1275 | 4 | public function cache($duration = true, $dependency = null) |
|
1276 | { |
||
1277 | 4 | $this->queryCacheDuration = $duration; |
|
1278 | 4 | $this->queryCacheDependency = $dependency; |
|
1279 | 4 | return $this; |
|
1280 | } |
||
1281 | |||
1282 | /** |
||
1283 | * Disables query cache for this Query. |
||
1284 | * @return $this the Query object itself |
||
1285 | * @since 2.0.14 |
||
1286 | */ |
||
1287 | 4 | public function noCache() |
|
1288 | { |
||
1289 | 4 | $this->queryCacheDuration = -1; |
|
1290 | 4 | return $this; |
|
1291 | } |
||
1292 | |||
1293 | /** |
||
1294 | * Sets $command cache, if this query has enabled caching. |
||
1295 | * |
||
1296 | * @param Command $command |
||
1297 | * @return Command |
||
1298 | * @since 2.0.14 |
||
1299 | */ |
||
1300 | 914 | protected function setCommandCache($command) |
|
1301 | { |
||
1302 | 914 | if ($this->queryCacheDuration !== null || $this->queryCacheDependency !== null) { |
|
1303 | 4 | $duration = $this->queryCacheDuration === true ? null : $this->queryCacheDuration; |
|
1304 | 4 | $command->cache($duration, $this->queryCacheDependency); |
|
1305 | } |
||
1306 | |||
1307 | 914 | return $command; |
|
1308 | } |
||
1309 | |||
1310 | /** |
||
1311 | * Creates a new Query object and copies its property values from an existing one. |
||
1312 | * The properties being copies are the ones to be used by query builders. |
||
1313 | * @param Query $from the source query object |
||
1314 | * @return Query the new Query object |
||
1315 | */ |
||
1316 | 567 | public static function create($from) |
|
1317 | { |
||
1318 | 567 | return new self([ |
|
1319 | 567 | 'where' => $from->where, |
|
1320 | 567 | 'limit' => $from->limit, |
|
1321 | 567 | 'offset' => $from->offset, |
|
1322 | 567 | 'orderBy' => $from->orderBy, |
|
1323 | 567 | 'indexBy' => $from->indexBy, |
|
1324 | 567 | 'select' => $from->select, |
|
1325 | 567 | 'selectOption' => $from->selectOption, |
|
1326 | 567 | 'distinct' => $from->distinct, |
|
1327 | 567 | 'from' => $from->from, |
|
1328 | 567 | 'groupBy' => $from->groupBy, |
|
1329 | 567 | 'join' => $from->join, |
|
1330 | 567 | 'having' => $from->having, |
|
1331 | 567 | 'union' => $from->union, |
|
1332 | 567 | 'params' => $from->params, |
|
1333 | ]); |
||
1334 | } |
||
1335 | |||
1336 | /** |
||
1337 | * Returns the SQL representation of Query |
||
1338 | * @return string |
||
1339 | */ |
||
1340 | public function __toString() |
||
1341 | { |
||
1342 | return serialize($this); |
||
1343 | } |
||
1344 | } |
||
1345 |