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