Complex classes like Query often do a lot of different things. To break such a class down, we need to identify a cohesive component within that class. A common approach to find such a component is to look for fields/methods that share the same prefixes, or suffixes. You can also have a look at the cohesion graph to spot any un-connected, or weakly-connected components.
Once you have determined the fields that belong together, you can apply the Extract Class refactoring. If the component makes sense as a sub-class, Extract Subclass is also a candidate, and is often faster.
While breaking up the class, it is a good idea to analyze how other classes use Query, and based on these observations, apply Extract Interface, too.
1 | <?php |
||
46 | class Query extends Component implements QueryInterface |
||
47 | { |
||
48 | use QueryTrait; |
||
49 | |||
50 | /** |
||
51 | * @var array the columns being selected. For example, `['id', 'name']`. |
||
52 | * This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns. |
||
53 | * @see select() |
||
54 | */ |
||
55 | public $select; |
||
56 | /** |
||
57 | * @var string additional option that should be appended to the 'SELECT' keyword. For example, |
||
58 | * in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used. |
||
59 | */ |
||
60 | public $selectOption; |
||
61 | /** |
||
62 | * @var bool whether to select distinct rows of data only. If this is set true, |
||
63 | * the SELECT clause would be changed to SELECT DISTINCT. |
||
64 | */ |
||
65 | public $distinct; |
||
66 | /** |
||
67 | * @var array the table(s) to be selected from. For example, `['user', 'post']`. |
||
68 | * This is used to construct the FROM clause in a SQL statement. |
||
69 | * @see from() |
||
70 | */ |
||
71 | public $from; |
||
72 | /** |
||
73 | * @var array how to group the query results. For example, `['company', 'department']`. |
||
74 | * This is used to construct the GROUP BY clause in a SQL statement. |
||
75 | */ |
||
76 | public $groupBy; |
||
77 | /** |
||
78 | * @var array how to join with other tables. Each array element represents the specification |
||
79 | * of one join which has the following structure: |
||
80 | * |
||
81 | * ```php |
||
82 | * [$joinType, $tableName, $joinCondition] |
||
83 | * ``` |
||
84 | * |
||
85 | * For example, |
||
86 | * |
||
87 | * ```php |
||
88 | * [ |
||
89 | * ['INNER JOIN', 'user', 'user.id = author_id'], |
||
90 | * ['LEFT JOIN', 'team', 'team.id = team_id'], |
||
91 | * ] |
||
92 | * ``` |
||
93 | */ |
||
94 | public $join; |
||
95 | /** |
||
96 | * @var string|array|Expression the condition to be applied in the GROUP BY clause. |
||
97 | * It can be either a string or an array. Please refer to [[where()]] on how to specify the condition. |
||
98 | */ |
||
99 | public $having; |
||
100 | /** |
||
101 | * @var array this is used to construct the UNION clause(s) in a SQL statement. |
||
102 | * Each array element is an array of the following structure: |
||
103 | * |
||
104 | * - `query`: either a string or a [[Query]] object representing a query |
||
105 | * - `all`: boolean, whether it should be `UNION ALL` or `UNION` |
||
106 | */ |
||
107 | public $union; |
||
108 | /** |
||
109 | * @var array list of query parameter values indexed by parameter placeholders. |
||
110 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
111 | */ |
||
112 | public $params = []; |
||
113 | |||
114 | |||
115 | /** |
||
116 | * Creates a DB command that can be used to execute this query. |
||
117 | * @param Connection $db the database connection used to generate the SQL statement. |
||
118 | * If this parameter is not given, the `db` application component will be used. |
||
119 | * @return Command the created DB command instance. |
||
120 | */ |
||
121 | 190 | public function createCommand($db = null) |
|
122 | { |
||
123 | 190 | if ($db === null) { |
|
124 | 13 | $db = Yii::$app->getDb(); |
|
125 | } |
||
126 | 190 | list ($sql, $params) = $db->getQueryBuilder()->build($this); |
|
127 | |||
128 | 190 | return $db->createCommand($sql, $params); |
|
129 | } |
||
130 | |||
131 | /** |
||
132 | * Prepares for building SQL. |
||
133 | * This method is called by [[QueryBuilder]] when it starts to build SQL from a query object. |
||
134 | * You may override this method to do some final preparation work when converting a query into a SQL statement. |
||
135 | * @param QueryBuilder $builder |
||
136 | * @return $this a prepared query instance which will be used by [[QueryBuilder]] to build the SQL |
||
137 | */ |
||
138 | 503 | public function prepare($builder) |
|
142 | |||
143 | /** |
||
144 | * Starts a batch query. |
||
145 | * |
||
146 | * A batch query supports fetching data in batches, which can keep the memory usage under a limit. |
||
147 | * This method will return a [[BatchQueryResult]] object which implements the [[\Iterator]] interface |
||
148 | * and can be traversed to retrieve the data in batches. |
||
149 | * |
||
150 | * For example, |
||
151 | * |
||
152 | * ```php |
||
153 | * $query = (new Query)->from('user'); |
||
154 | * foreach ($query->batch() as $rows) { |
||
155 | * // $rows is an array of 100 or fewer rows from user table |
||
156 | * } |
||
157 | * ``` |
||
158 | * |
||
159 | * @param int $batchSize the number of records to be fetched in each batch. |
||
160 | * @param Connection $db the database connection. If not set, the "db" application component will be used. |
||
161 | * @return BatchQueryResult the batch query result. It implements the [[\Iterator]] interface |
||
162 | * and can be traversed to retrieve the data in batches. |
||
163 | */ |
||
164 | 6 | public function batch($batchSize = 100, $db = null) |
|
165 | { |
||
166 | 6 | return Yii::createObject([ |
|
167 | 6 | 'class' => BatchQueryResult::className(), |
|
168 | 6 | 'query' => $this, |
|
169 | 6 | 'batchSize' => $batchSize, |
|
170 | 6 | 'db' => $db, |
|
171 | 'each' => false, |
||
172 | ]); |
||
173 | } |
||
174 | |||
175 | /** |
||
176 | * Starts a batch query and retrieves data row by row. |
||
177 | * This method is similar to [[batch()]] except that in each iteration of the result, |
||
178 | * only one row of data is returned. For example, |
||
179 | * |
||
180 | * ```php |
||
181 | * $query = (new Query)->from('user'); |
||
182 | * foreach ($query->each() as $row) { |
||
183 | * } |
||
184 | * ``` |
||
185 | * |
||
186 | * @param int $batchSize the number of records to be fetched in each batch. |
||
187 | * @param Connection $db the database connection. If not set, the "db" application component will be used. |
||
188 | * @return BatchQueryResult the batch query result. It implements the [[\Iterator]] interface |
||
189 | * and can be traversed to retrieve the data in batches. |
||
190 | */ |
||
191 | 3 | public function each($batchSize = 100, $db = null) |
|
192 | { |
||
193 | 3 | return Yii::createObject([ |
|
194 | 3 | 'class' => BatchQueryResult::className(), |
|
195 | 3 | 'query' => $this, |
|
196 | 3 | 'batchSize' => $batchSize, |
|
197 | 3 | 'db' => $db, |
|
198 | 'each' => true, |
||
199 | ]); |
||
200 | } |
||
201 | |||
202 | /** |
||
203 | * Executes the query and returns all results as an array. |
||
204 | * @param Connection $db the database connection used to generate the SQL statement. |
||
205 | * If this parameter is not given, the `db` application component will be used. |
||
206 | * @return array the query results. If the query results in nothing, an empty array will be returned. |
||
207 | */ |
||
208 | 298 | public function all($db = null) |
|
216 | |||
217 | /** |
||
218 | * Converts the raw query results into the format as specified by this query. |
||
219 | * This method is internally used to convert the data fetched from database |
||
220 | * into the format as required by this query. |
||
221 | * @param array $rows the raw query result from database |
||
222 | * @return array the converted query result |
||
223 | */ |
||
224 | 122 | public function populate($rows) |
|
225 | { |
||
226 | 122 | if ($this->indexBy === null) { |
|
227 | 122 | return $rows; |
|
228 | } |
||
229 | 3 | $result = []; |
|
230 | 3 | foreach ($rows as $row) { |
|
231 | 3 | if (is_string($this->indexBy)) { |
|
232 | 3 | $key = $row[$this->indexBy]; |
|
233 | } else { |
||
234 | $key = call_user_func($this->indexBy, $row); |
||
235 | } |
||
236 | 3 | $result[$key] = $row; |
|
237 | } |
||
238 | 3 | return $result; |
|
239 | } |
||
240 | |||
241 | /** |
||
242 | * Executes the query and returns a single row of result. |
||
243 | * @param Connection $db the database connection used to generate the SQL statement. |
||
244 | * If this parameter is not given, the `db` application component will be used. |
||
245 | * @return array|bool the first row (in terms of an array) of the query result. False is returned if the query |
||
246 | * results in nothing. |
||
247 | */ |
||
248 | 309 | public function one($db = null) |
|
255 | |||
256 | /** |
||
257 | * Returns the query result as a scalar value. |
||
258 | * The value returned will be the first column in the first row of the query results. |
||
259 | * @param Connection $db the database connection used to generate the SQL statement. |
||
260 | * If this parameter is not given, the `db` application component will be used. |
||
261 | * @return string|null|false the value of the first column in the first row of the query result. |
||
262 | * False is returned if the query result is empty. |
||
263 | */ |
||
264 | 14 | public function scalar($db = null) |
|
271 | |||
272 | /** |
||
273 | * Executes the query and returns the first column of the result. |
||
274 | * @param Connection $db the database connection used to generate the SQL statement. |
||
275 | * If this parameter is not given, the `db` application component will be used. |
||
276 | * @return array the first column of the query result. An empty array is returned if the query results in nothing. |
||
277 | */ |
||
278 | 27 | public function column($db = null) |
|
279 | { |
||
280 | 27 | if ($this->emulateExecution) { |
|
281 | 6 | return []; |
|
282 | } |
||
283 | |||
284 | 21 | if ($this->indexBy === null) { |
|
285 | 21 | return $this->createCommand($db)->queryColumn(); |
|
286 | } |
||
287 | |||
288 | 3 | if (is_string($this->indexBy) && is_array($this->select) && count($this->select) === 1) { |
|
289 | 3 | $this->select[] = $this->indexBy; |
|
290 | } |
||
291 | 3 | $rows = $this->createCommand($db)->queryAll(); |
|
292 | 3 | $results = []; |
|
293 | 3 | foreach ($rows as $row) { |
|
294 | 3 | $value = reset($row); |
|
295 | |||
296 | 3 | if ($this->indexBy instanceof \Closure) { |
|
297 | 3 | $results[call_user_func($this->indexBy, $row)] = $value; |
|
298 | } else { |
||
299 | 3 | $results[$row[$this->indexBy]] = $value; |
|
300 | } |
||
301 | } |
||
302 | 3 | return $results; |
|
303 | } |
||
304 | |||
305 | /** |
||
306 | * Returns the number of records. |
||
307 | * @param string $q the COUNT expression. Defaults to '*'. |
||
308 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
309 | * @param Connection $db the database connection used to generate the SQL statement. |
||
310 | * If this parameter is not given (or null), the `db` application component will be used. |
||
311 | * @return int|string number of records. The result may be a string depending on the |
||
312 | * underlying database engine and to support integer values higher than a 32bit PHP integer can handle. |
||
313 | */ |
||
314 | 87 | public function count($q = '*', $db = null) |
|
321 | |||
322 | /** |
||
323 | * Returns the sum of the specified column values. |
||
324 | * @param string $q the column name or expression. |
||
325 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
326 | * @param Connection $db the database connection used to generate the SQL statement. |
||
327 | * If this parameter is not given, the `db` application component will be used. |
||
328 | * @return mixed the sum of the specified column values. |
||
329 | */ |
||
330 | 9 | public function sum($q, $db = null) |
|
337 | |||
338 | /** |
||
339 | * Returns the average of the specified column values. |
||
340 | * @param string $q the column name or expression. |
||
341 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
342 | * @param Connection $db the database connection used to generate the SQL statement. |
||
343 | * If this parameter is not given, the `db` application component will be used. |
||
344 | * @return mixed the average of the specified column values. |
||
345 | */ |
||
346 | 9 | public function average($q, $db = null) |
|
353 | |||
354 | /** |
||
355 | * Returns the minimum of the specified column values. |
||
356 | * @param string $q the column name or expression. |
||
357 | * Make sure you properly [quote](guide:db-dao#quoting-table-and-column-names) column names in the expression. |
||
358 | * @param Connection $db the database connection used to generate the SQL statement. |
||
359 | * If this parameter is not given, the `db` application component will be used. |
||
360 | * @return mixed the minimum of the specified column values. |
||
361 | */ |
||
362 | 9 | public function min($q, $db = null) |
|
366 | |||
367 | /** |
||
368 | * Returns the maximum 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 maximum of the specified column values. |
||
374 | */ |
||
375 | 9 | public function max($q, $db = null) |
|
379 | |||
380 | /** |
||
381 | * Returns a value indicating whether the query result contains any row of data. |
||
382 | * @param Connection $db the database connection used to generate the SQL statement. |
||
383 | * If this parameter is not given, the `db` application component will be used. |
||
384 | * @return bool whether the query result contains any row of data. |
||
385 | */ |
||
386 | 62 | public function exists($db = null) |
|
397 | |||
398 | /** |
||
399 | * Queries a scalar value by setting [[select]] first. |
||
400 | * Restores the value of select to make this query reusable. |
||
401 | * @param string|Expression $selectExpression |
||
402 | * @param Connection|null $db |
||
403 | * @return bool|string |
||
404 | */ |
||
405 | 87 | protected function queryScalar($selectExpression, $db) |
|
406 | { |
||
407 | 87 | if ($this->emulateExecution) { |
|
408 | 6 | return null; |
|
409 | } |
||
410 | |||
411 | if ( |
||
412 | 87 | !$this->distinct |
|
413 | && empty($this->groupBy) |
||
414 | && empty($this->having) |
||
415 | && empty($this->union) |
||
416 | ) { |
||
417 | 86 | $select = $this->select; |
|
418 | 86 | $order = $this->orderBy; |
|
419 | 86 | $limit = $this->limit; |
|
420 | 86 | $offset = $this->offset; |
|
421 | |||
422 | 86 | $this->select = [$selectExpression]; |
|
423 | 86 | $this->orderBy = null; |
|
424 | 86 | $this->limit = null; |
|
425 | 86 | $this->offset = null; |
|
426 | 86 | $command = $this->createCommand($db); |
|
427 | |||
428 | 86 | $this->select = $select; |
|
429 | 86 | $this->orderBy = $order; |
|
430 | 86 | $this->limit = $limit; |
|
431 | 86 | $this->offset = $offset; |
|
432 | |||
433 | 86 | return $command->queryScalar(); |
|
434 | } else { |
||
435 | 7 | return (new Query)->select([$selectExpression]) |
|
436 | 7 | ->from(['c' => $this]) |
|
437 | 7 | ->createCommand($db) |
|
438 | 7 | ->queryScalar(); |
|
439 | } |
||
440 | } |
||
441 | |||
442 | /** |
||
443 | * Sets the SELECT part of the query. |
||
444 | * @param string|array|Expression $columns the columns to be selected. |
||
445 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
446 | * Columns can be prefixed with table names (e.g. "user.id") and/or contain column aliases (e.g. "user.id AS user_id"). |
||
447 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
448 | * (which means the column contains a DB expression). A DB expression may also be passed in form of |
||
449 | * an [[Expression]] object. |
||
450 | * |
||
451 | * Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should |
||
452 | * use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts. |
||
453 | * |
||
454 | * When the columns are specified as an array, you may also use array keys as the column aliases (if a column |
||
455 | * does not need alias, do not use a string key). |
||
456 | * |
||
457 | * Starting from version 2.0.1, you may also select sub-queries as columns by specifying each such column |
||
458 | * as a `Query` instance representing the sub-query. |
||
459 | * |
||
460 | * @param string $option additional option that should be appended to the 'SELECT' keyword. For example, |
||
461 | * in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used. |
||
462 | * @return $this the query object itself |
||
463 | */ |
||
464 | 248 | public function select($columns, $option = null) |
|
465 | { |
||
466 | 248 | if ($columns instanceof Expression) { |
|
467 | 3 | $columns = [$columns]; |
|
468 | 245 | } elseif (!is_array($columns)) { |
|
469 | 80 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
470 | } |
||
471 | 248 | $this->select = $columns; |
|
472 | 248 | $this->selectOption = $option; |
|
473 | 248 | return $this; |
|
474 | } |
||
475 | |||
476 | /** |
||
477 | * Add more columns to the SELECT part of the query. |
||
478 | * |
||
479 | * Note, that if [[select]] has not been specified before, you should include `*` explicitly |
||
480 | * if you want to select all remaining columns too: |
||
481 | * |
||
482 | * ```php |
||
483 | * $query->addSelect(["*", "CONCAT(first_name, ' ', last_name) AS full_name"])->one(); |
||
484 | * ``` |
||
485 | * |
||
486 | * @param string|array|Expression $columns the columns to add to the select. See [[select()]] for more |
||
487 | * details about the format of this parameter. |
||
488 | * @return $this the query object itself |
||
489 | * @see select() |
||
490 | */ |
||
491 | 9 | public function addSelect($columns) |
|
492 | { |
||
493 | 9 | if ($columns instanceof Expression) { |
|
494 | 3 | $columns = [$columns]; |
|
495 | 9 | } elseif (!is_array($columns)) { |
|
496 | 3 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
497 | } |
||
498 | 9 | if ($this->select === null) { |
|
499 | 3 | $this->select = $columns; |
|
500 | } else { |
||
501 | 9 | $this->select = array_merge($this->select, $columns); |
|
502 | } |
||
503 | 9 | return $this; |
|
504 | } |
||
505 | |||
506 | /** |
||
507 | * Sets the value indicating whether to SELECT DISTINCT or not. |
||
508 | * @param bool $value whether to SELECT DISTINCT or not. |
||
509 | * @return $this the query object itself |
||
510 | */ |
||
511 | 6 | public function distinct($value = true) |
|
516 | |||
517 | /** |
||
518 | * Sets the FROM part of the query. |
||
519 | * @param string|array $tables the table(s) to be selected from. This can be either a string (e.g. `'user'`) |
||
520 | * or an array (e.g. `['user', 'profile']`) specifying one or several table names. |
||
521 | * Table names can contain schema prefixes (e.g. `'public.user'`) and/or table aliases (e.g. `'user u'`). |
||
522 | * The method will automatically quote the table names unless it contains some parenthesis |
||
523 | * (which means the table is given as a sub-query or DB expression). |
||
524 | * |
||
525 | * When the tables are specified as an array, you may also use the array keys as the table aliases |
||
526 | * (if a table does not need alias, do not use a string key). |
||
527 | * |
||
528 | * Use a Query object to represent a sub-query. In this case, the corresponding array key will be used |
||
529 | * as the alias for the sub-query. |
||
530 | * |
||
531 | * Here are some examples: |
||
532 | * |
||
533 | * ```php |
||
534 | * // SELECT * FROM `user` `u`, `profile`; |
||
535 | * $query = (new \yii\db\Query)->from(['u' => 'user', 'profile']); |
||
536 | * |
||
537 | * // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; |
||
538 | * $subquery = (new \yii\db\Query)->from('user')->where(['active' => true]) |
||
539 | * $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); |
||
540 | * |
||
541 | * // subquery can also be a string with plain SQL wrapped in parenthesis |
||
542 | * // SELECT * FROM (SELECT * FROM `user` WHERE `active` = 1) `activeusers`; |
||
543 | * $subquery = "(SELECT * FROM `user` WHERE `active` = 1)"; |
||
544 | * $query = (new \yii\db\Query)->from(['activeusers' => $subquery]); |
||
545 | * ``` |
||
546 | * |
||
547 | * @return $this the query object itself |
||
548 | */ |
||
549 | 250 | public function from($tables) |
|
550 | { |
||
551 | 250 | if (!is_array($tables)) { |
|
552 | 231 | $tables = preg_split('/\s*,\s*/', trim($tables), -1, PREG_SPLIT_NO_EMPTY); |
|
553 | } |
||
554 | 250 | $this->from = $tables; |
|
555 | 250 | return $this; |
|
556 | } |
||
557 | |||
558 | /** |
||
559 | * Sets the WHERE part of the query. |
||
560 | * |
||
561 | * The method requires a `$condition` parameter, and optionally a `$params` parameter |
||
562 | * specifying the values to be bound to the query. |
||
563 | * |
||
564 | * The `$condition` parameter should be either a string (e.g. `'id=1'`) or an array. |
||
565 | * |
||
566 | * @inheritdoc |
||
567 | * |
||
568 | * @param string|array|Expression $condition the conditions that should be put in the WHERE part. |
||
569 | * @param array $params the parameters (name => value) to be bound to the query. |
||
570 | * @return $this the query object itself |
||
571 | * @see andWhere() |
||
572 | * @see orWhere() |
||
573 | * @see QueryInterface::where() |
||
574 | */ |
||
575 | 499 | public function where($condition, $params = []) |
|
581 | |||
582 | /** |
||
583 | * Adds an additional WHERE condition to the existing one. |
||
584 | * The new condition and the existing one will be joined using the `AND` operator. |
||
585 | * @param string|array|Expression $condition the new WHERE condition. Please refer to [[where()]] |
||
586 | * on how to specify this parameter. |
||
587 | * @param array $params the parameters (name => value) to be bound to the query. |
||
588 | * @return $this the query object itself |
||
589 | * @see where() |
||
590 | * @see orWhere() |
||
591 | */ |
||
592 | 277 | public function andWhere($condition, $params = []) |
|
593 | { |
||
594 | 277 | if ($this->where === null) { |
|
595 | 250 | $this->where = $condition; |
|
596 | 75 | } elseif (is_array($this->where) && isset($this->where[0]) && strcasecmp($this->where[0], 'and') === 0) { |
|
597 | 15 | $this->where[] = $condition; |
|
598 | } else { |
||
599 | 75 | $this->where = ['and', $this->where, $condition]; |
|
600 | } |
||
601 | 277 | $this->addParams($params); |
|
602 | 277 | return $this; |
|
603 | } |
||
604 | |||
605 | /** |
||
606 | * Adds an additional WHERE condition to the existing one. |
||
607 | * The new condition and the existing one will be joined using the `OR` operator. |
||
608 | * @param string|array|Expression $condition the new WHERE condition. Please refer to [[where()]] |
||
609 | * on how to specify this parameter. |
||
610 | * @param array $params the parameters (name => value) to be bound to the query. |
||
611 | * @return $this the query object itself |
||
612 | * @see where() |
||
613 | * @see andWhere() |
||
614 | */ |
||
615 | 7 | public function orWhere($condition, $params = []) |
|
616 | { |
||
617 | 7 | if ($this->where === null) { |
|
618 | $this->where = $condition; |
||
619 | } else { |
||
620 | 7 | $this->where = ['or', $this->where, $condition]; |
|
621 | } |
||
622 | 7 | $this->addParams($params); |
|
623 | 7 | return $this; |
|
624 | } |
||
625 | |||
626 | /** |
||
627 | * Adds a filtering condition for a specific column and allow the user to choose a filter operator. |
||
628 | * |
||
629 | * It adds an additional WHERE condition for the given field and determines the comparison operator |
||
630 | * based on the first few characters of the given value. |
||
631 | * The condition is added in the same way as in [[andFilterWhere]] so [[isEmpty()|empty values]] are ignored. |
||
632 | * The new condition and the existing one will be joined using the `AND` operator. |
||
633 | * |
||
634 | * The comparison operator is intelligently determined based on the first few characters in the given value. |
||
635 | * In particular, it recognizes the following operators if they appear as the leading characters in the given value: |
||
636 | * |
||
637 | * - `<`: the column must be less than the given value. |
||
638 | * - `>`: the column must be greater than the given value. |
||
639 | * - `<=`: the column must be less than or equal to the given value. |
||
640 | * - `>=`: the column must be greater than or equal to the given value. |
||
641 | * - `<>`: the column must not be the same as the given value. |
||
642 | * - `=`: the column must be equal to the given value. |
||
643 | * - If none of the above operators is detected, the `$defaultOperator` will be used. |
||
644 | * |
||
645 | * @param string $name the column name. |
||
646 | * @param string $value the column value optionally prepended with the comparison operator. |
||
647 | * @param string $defaultOperator The operator to use, when no operator is given in `$value`. |
||
648 | * Defaults to `=`, performing an exact match. |
||
649 | * @return $this The query object itself |
||
650 | * @since 2.0.8 |
||
651 | */ |
||
652 | 3 | public function andFilterCompare($name, $value, $defaultOperator = '=') |
|
653 | { |
||
654 | 3 | if (preg_match('/^(<>|>=|>|<=|<|=)/', $value, $matches)) { |
|
655 | 3 | $operator = $matches[1]; |
|
656 | 3 | $value = substr($value, strlen($operator)); |
|
657 | } else { |
||
658 | 3 | $operator = $defaultOperator; |
|
659 | } |
||
660 | 3 | return $this->andFilterWhere([$operator, $name, $value]); |
|
661 | } |
||
662 | |||
663 | /** |
||
664 | * Appends a JOIN part to the query. |
||
665 | * The first parameter specifies what type of join it is. |
||
666 | * @param string $type the type of join, such as INNER JOIN, LEFT JOIN. |
||
667 | * @param string|array $table the table to be joined. |
||
668 | * |
||
669 | * Use a string to represent the name of the table to be joined. |
||
670 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
671 | * The method will automatically quote the table name unless it contains some parenthesis |
||
672 | * (which means the table is given as a sub-query or DB expression). |
||
673 | * |
||
674 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
675 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
676 | * represents the alias for the sub-query. |
||
677 | * |
||
678 | * @param string|array $on the join condition that should appear in the ON part. |
||
679 | * Please refer to [[where()]] on how to specify this parameter. |
||
680 | * |
||
681 | * Note that the array format of [[where()]] is designed to match columns to values instead of columns to columns, so |
||
682 | * the following would **not** work as expected: `['post.author_id' => 'user.id']`, it would |
||
683 | * match the `post.author_id` column value against the string `'user.id'`. |
||
684 | * It is recommended to use the string syntax here which is more suited for a join: |
||
685 | * |
||
686 | * ```php |
||
687 | * 'post.author_id = user.id' |
||
688 | * ``` |
||
689 | * |
||
690 | * @param array $params the parameters (name => value) to be bound to the query. |
||
691 | * @return $this the query object itself |
||
692 | */ |
||
693 | 39 | public function join($type, $table, $on = '', $params = []) |
|
698 | |||
699 | /** |
||
700 | * Appends an INNER JOIN part to the query. |
||
701 | * @param string|array $table the table to be joined. |
||
702 | * |
||
703 | * Use a string to represent the name of the table to be joined. |
||
704 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
705 | * The method will automatically quote the table name unless it contains some parenthesis |
||
706 | * (which means the table is given as a sub-query or DB expression). |
||
707 | * |
||
708 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
709 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
710 | * represents the alias for the sub-query. |
||
711 | * |
||
712 | * @param string|array $on the join condition that should appear in the ON part. |
||
713 | * Please refer to [[join()]] on how to specify this parameter. |
||
714 | * @param array $params the parameters (name => value) to be bound to the query. |
||
715 | * @return $this the query object itself |
||
716 | */ |
||
717 | public function innerJoin($table, $on = '', $params = []) |
||
722 | |||
723 | /** |
||
724 | * Appends a LEFT OUTER JOIN part to the query. |
||
725 | * @param string|array $table the table to be joined. |
||
726 | * |
||
727 | * Use a string to represent the name of the table to be joined. |
||
728 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
729 | * The method will automatically quote the table name unless it contains some parenthesis |
||
730 | * (which means the table is given as a sub-query or DB expression). |
||
731 | * |
||
732 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
733 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
734 | * represents the alias for the sub-query. |
||
735 | * |
||
736 | * @param string|array $on the join condition that should appear in the ON part. |
||
737 | * Please refer to [[join()]] on how to specify this parameter. |
||
738 | * @param array $params the parameters (name => value) to be bound to the query |
||
739 | * @return $this the query object itself |
||
740 | */ |
||
741 | 3 | public function leftJoin($table, $on = '', $params = []) |
|
746 | |||
747 | /** |
||
748 | * Appends a RIGHT OUTER JOIN part to the query. |
||
749 | * @param string|array $table the table to be joined. |
||
750 | * |
||
751 | * Use a string to represent the name of the table to be joined. |
||
752 | * The table name can contain a schema prefix (e.g. 'public.user') and/or table alias (e.g. 'user u'). |
||
753 | * The method will automatically quote the table name unless it contains some parenthesis |
||
754 | * (which means the table is given as a sub-query or DB expression). |
||
755 | * |
||
756 | * Use an array to represent joining with a sub-query. The array must contain only one element. |
||
757 | * The value must be a [[Query]] object representing the sub-query while the corresponding key |
||
758 | * represents the alias for the sub-query. |
||
759 | * |
||
760 | * @param string|array $on the join condition that should appear in the ON part. |
||
761 | * Please refer to [[join()]] on how to specify this parameter. |
||
762 | * @param array $params the parameters (name => value) to be bound to the query |
||
763 | * @return $this the query object itself |
||
764 | */ |
||
765 | public function rightJoin($table, $on = '', $params = []) |
||
770 | |||
771 | /** |
||
772 | * Sets the GROUP BY part of the query. |
||
773 | * @param string|array|Expression $columns the columns to be grouped by. |
||
774 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
775 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
776 | * (which means the column contains a DB expression). |
||
777 | * |
||
778 | * Note that if your group-by is an expression containing commas, you should always use an array |
||
779 | * to represent the group-by information. Otherwise, the method will not be able to correctly determine |
||
780 | * the group-by columns. |
||
781 | * |
||
782 | * Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
||
783 | * @return $this the query object itself |
||
784 | * @see addGroupBy() |
||
785 | */ |
||
786 | 21 | public function groupBy($columns) |
|
787 | { |
||
788 | 21 | if ($columns instanceof Expression) { |
|
789 | 3 | $columns = [$columns]; |
|
790 | 21 | } elseif (!is_array($columns)) { |
|
791 | 21 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
792 | } |
||
793 | 21 | $this->groupBy = $columns; |
|
794 | 21 | return $this; |
|
795 | } |
||
796 | |||
797 | /** |
||
798 | * Adds additional group-by columns to the existing ones. |
||
799 | * @param string|array $columns additional columns to be grouped by. |
||
800 | * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']). |
||
801 | * The method will automatically quote the column names unless a column contains some parenthesis |
||
802 | * (which means the column contains a DB expression). |
||
803 | * |
||
804 | * Note that if your group-by is an expression containing commas, you should always use an array |
||
805 | * to represent the group-by information. Otherwise, the method will not be able to correctly determine |
||
806 | * the group-by columns. |
||
807 | * |
||
808 | * Since version 2.0.7, an [[Expression]] object can be passed to specify the GROUP BY part explicitly in plain SQL. |
||
809 | * @return $this the query object itself |
||
810 | * @see groupBy() |
||
811 | */ |
||
812 | 3 | public function addGroupBy($columns) |
|
813 | { |
||
814 | 3 | if ($columns instanceof Expression) { |
|
815 | $columns = [$columns]; |
||
816 | 3 | } elseif (!is_array($columns)) { |
|
817 | 3 | $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY); |
|
818 | } |
||
819 | 3 | if ($this->groupBy === null) { |
|
820 | $this->groupBy = $columns; |
||
821 | } else { |
||
822 | 3 | $this->groupBy = array_merge($this->groupBy, $columns); |
|
823 | } |
||
824 | 3 | return $this; |
|
825 | } |
||
826 | |||
827 | /** |
||
828 | * Sets the HAVING part of the query. |
||
829 | * @param string|array|Expression $condition the conditions to be put after HAVING. |
||
830 | * Please refer to [[where()]] on how to specify this parameter. |
||
831 | * @param array $params the parameters (name => value) to be bound to the query. |
||
832 | * @return $this the query object itself |
||
833 | * @see andHaving() |
||
834 | * @see orHaving() |
||
835 | */ |
||
836 | 10 | public function having($condition, $params = []) |
|
842 | |||
843 | /** |
||
844 | * Adds an additional HAVING condition to the existing one. |
||
845 | * The new condition and the existing one will be joined using the `AND` operator. |
||
846 | * @param string|array|Expression $condition the new HAVING condition. Please refer to [[where()]] |
||
847 | * on how to specify this parameter. |
||
848 | * @param array $params the parameters (name => value) to be bound to the query. |
||
849 | * @return $this the query object itself |
||
850 | * @see having() |
||
851 | * @see orHaving() |
||
852 | */ |
||
853 | 3 | public function andHaving($condition, $params = []) |
|
854 | { |
||
855 | 3 | if ($this->having === null) { |
|
856 | $this->having = $condition; |
||
857 | } else { |
||
858 | 3 | $this->having = ['and', $this->having, $condition]; |
|
859 | } |
||
860 | 3 | $this->addParams($params); |
|
861 | 3 | return $this; |
|
862 | } |
||
863 | |||
864 | /** |
||
865 | * Adds an additional HAVING condition to the existing one. |
||
866 | * The new condition and the existing one will be joined using the `OR` operator. |
||
867 | * @param string|array|Expression $condition the new HAVING condition. Please refer to [[where()]] |
||
868 | * on how to specify this parameter. |
||
869 | * @param array $params the parameters (name => value) to be bound to the query. |
||
870 | * @return $this the query object itself |
||
871 | * @see having() |
||
872 | * @see andHaving() |
||
873 | */ |
||
874 | 3 | public function orHaving($condition, $params = []) |
|
875 | { |
||
876 | 3 | if ($this->having === null) { |
|
877 | $this->having = $condition; |
||
878 | } else { |
||
879 | 3 | $this->having = ['or', $this->having, $condition]; |
|
880 | } |
||
881 | 3 | $this->addParams($params); |
|
882 | 3 | return $this; |
|
883 | } |
||
884 | |||
885 | /** |
||
886 | * Sets the HAVING part of the query but ignores [[isEmpty()|empty operands]]. |
||
887 | * |
||
888 | * This method is similar to [[having()]]. The main difference is that this method will |
||
889 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
890 | * for building query conditions based on filter values entered by users. |
||
891 | * |
||
892 | * The following code shows the difference between this method and [[having()]]: |
||
893 | * |
||
894 | * ```php |
||
895 | * // HAVING `age`=:age |
||
896 | * $query->filterHaving(['name' => null, 'age' => 20]); |
||
897 | * // HAVING `age`=:age |
||
898 | * $query->having(['age' => 20]); |
||
899 | * // HAVING `name` IS NULL AND `age`=:age |
||
900 | * $query->having(['name' => null, 'age' => 20]); |
||
901 | * ``` |
||
902 | * |
||
903 | * Note that unlike [[having()]], you cannot pass binding parameters to this method. |
||
904 | * |
||
905 | * @param array $condition the conditions that should be put in the HAVING part. |
||
906 | * See [[having()]] on how to specify this parameter. |
||
907 | * @return $this the query object itself |
||
908 | * @see having() |
||
909 | * @see andFilterHaving() |
||
910 | * @see orFilterHaving() |
||
911 | * @since 2.0.11 |
||
912 | */ |
||
913 | 6 | public function filterHaving(array $condition) |
|
914 | { |
||
915 | 6 | $condition = $this->filterCondition($condition); |
|
916 | 6 | if ($condition !== []) { |
|
917 | 6 | $this->having($condition); |
|
918 | } |
||
919 | 6 | return $this; |
|
920 | } |
||
921 | |||
922 | /** |
||
923 | * Adds an additional HAVING condition to the existing one but ignores [[isEmpty()|empty operands]]. |
||
924 | * The new condition and the existing one will be joined using the `AND` operator. |
||
925 | * |
||
926 | * This method is similar to [[andHaving()]]. The main difference is that this method will |
||
927 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
928 | * for building query conditions based on filter values entered by users. |
||
929 | * |
||
930 | * @param array $condition the new HAVING condition. Please refer to [[having()]] |
||
931 | * on how to specify this parameter. |
||
932 | * @return $this the query object itself |
||
933 | * @see filterHaving() |
||
934 | * @see orFilterHaving() |
||
935 | * @since 2.0.11 |
||
936 | */ |
||
937 | 6 | public function andFilterHaving(array $condition) |
|
938 | { |
||
939 | 6 | $condition = $this->filterCondition($condition); |
|
940 | 6 | if ($condition !== []) { |
|
941 | $this->andHaving($condition); |
||
942 | } |
||
943 | 6 | return $this; |
|
944 | } |
||
945 | |||
946 | /** |
||
947 | * Adds an additional HAVING condition to the existing one but ignores [[isEmpty()|empty operands]]. |
||
948 | * The new condition and the existing one will be joined using the `OR` operator. |
||
949 | * |
||
950 | * This method is similar to [[orHaving()]]. The main difference is that this method will |
||
951 | * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited |
||
952 | * for building query conditions based on filter values entered by users. |
||
953 | * |
||
954 | * @param array $condition the new HAVING condition. Please refer to [[having()]] |
||
955 | * on how to specify this parameter. |
||
956 | * @return $this the query object itself |
||
957 | * @see filterHaving() |
||
958 | * @see andFilterHaving() |
||
959 | * @since 2.0.11 |
||
960 | */ |
||
961 | 6 | public function orFilterHaving(array $condition) |
|
962 | { |
||
963 | 6 | $condition = $this->filterCondition($condition); |
|
964 | 6 | if ($condition !== []) { |
|
965 | $this->orHaving($condition); |
||
966 | } |
||
967 | 6 | return $this; |
|
968 | } |
||
969 | |||
970 | /** |
||
971 | * Appends a SQL statement using UNION operator. |
||
972 | * @param string|Query $sql the SQL statement to be appended using UNION |
||
973 | * @param bool $all TRUE if using UNION ALL and FALSE if using UNION |
||
974 | * @return $this the query object itself |
||
975 | */ |
||
976 | 10 | public function union($sql, $all = false) |
|
981 | |||
982 | /** |
||
983 | * Sets the parameters to be bound to the query. |
||
984 | * @param array $params list of query parameter values indexed by parameter placeholders. |
||
985 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
986 | * @return $this the query object itself |
||
987 | * @see addParams() |
||
988 | */ |
||
989 | 6 | public function params($params) |
|
994 | |||
995 | /** |
||
996 | * Adds additional parameters to be bound to the query. |
||
997 | * @param array $params list of query parameter values indexed by parameter placeholders. |
||
998 | * For example, `[':name' => 'Dan', ':age' => 31]`. |
||
999 | * @return $this the query object itself |
||
1000 | * @see params() |
||
1001 | */ |
||
1002 | 717 | public function addParams($params) |
|
1003 | { |
||
1004 | 717 | if (!empty($params)) { |
|
1005 | 46 | if (empty($this->params)) { |
|
1006 | 46 | $this->params = $params; |
|
1007 | } else { |
||
1008 | 6 | foreach ($params as $name => $value) { |
|
1009 | 6 | if (is_int($name)) { |
|
1010 | $this->params[] = $value; |
||
1011 | } else { |
||
1012 | 6 | $this->params[$name] = $value; |
|
1019 | |||
1020 | /** |
||
1021 | * Creates a new Query object and copies its property values from an existing one. |
||
1022 | * The properties being copies are the ones to be used by query builders. |
||
1023 | * @param Query $from the source query object |
||
1024 | * @return Query the new Query object |
||
1025 | */ |
||
1026 | 323 | public static function create($from) |
|
1045 | } |
||
1046 |
This check looks from parameters that have been defined for a function or method, but which are not used in the method body.