vakata /
database
Complex classes like TableQuery 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 TableQuery, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 11 | class TableQuery implements \IteratorAggregate, \ArrayAccess, \Countable |
||
| 12 | { |
||
| 13 | /** |
||
| 14 | * @var DatabaseInterface |
||
| 15 | */ |
||
| 16 | protected $db; |
||
| 17 | /** |
||
| 18 | * @var Table |
||
| 19 | */ |
||
| 20 | protected $definition; |
||
| 21 | /** |
||
| 22 | * @var TableQueryIterator|null |
||
| 23 | */ |
||
| 24 | protected $qiterator; |
||
| 25 | |||
| 26 | /** |
||
| 27 | * @var array |
||
| 28 | */ |
||
| 29 | protected $where = []; |
||
| 30 | /** |
||
| 31 | * @var array |
||
| 32 | */ |
||
| 33 | protected $order = []; |
||
| 34 | /** |
||
| 35 | * @var array |
||
| 36 | */ |
||
| 37 | protected $group = []; |
||
| 38 | /** |
||
| 39 | * @var array |
||
| 40 | */ |
||
| 41 | protected $having = []; |
||
| 42 | /** |
||
| 43 | * @var int[] |
||
| 44 | */ |
||
| 45 | protected $li_of = [0,0]; |
||
| 46 | protected $fields = []; |
||
| 47 | /** |
||
| 48 | * @var array |
||
| 49 | */ |
||
| 50 | protected $withr = []; |
||
| 51 | /** |
||
| 52 | * @var array |
||
| 53 | */ |
||
| 54 | protected $joins = []; |
||
| 55 | |||
| 56 | /** |
||
| 57 | * Create an instance |
||
| 58 | * @param DBInterface $db the database connection |
||
| 59 | * @param Table|string $definition the name or definition of the main table in the query |
||
|
|
|||
| 60 | */ |
||
| 61 | public function __construct(DBInterface $db, $table) |
||
| 62 | { |
||
| 63 | $this->db = $db; |
||
| 64 | $this->definition = $table instanceof Table ? $table : $this->db->definition((string)$table); |
||
| 65 | $this->columns($this->definition->getColumns()); |
||
| 66 | } |
||
| 67 | public function __clone() |
||
| 71 | /** |
||
| 72 | * Get the table definition of the queried table |
||
| 73 | * @return Table the definition |
||
| 74 | */ |
||
| 75 | public function getDefinition() : Table |
||
| 79 | |||
| 80 | protected function getColumn($column) |
||
| 81 | { |
||
| 82 | $column = explode('.', $column, 2); |
||
| 83 | if (count($column) === 1) { |
||
| 84 | $column = [ $this->definition->getName(), $column[0] ]; |
||
| 85 | } |
||
| 86 | if ($column[0] === $this->definition->getName()) { |
||
| 87 | $col = $this->definition->getColumn($column[1]); |
||
| 88 | if (!$col) { |
||
| 89 | throw new DBException('Invalid column name in own table'); |
||
| 90 | } |
||
| 91 | } else { |
||
| 92 | if ($this->definition->hasRelation($column[0])) { |
||
| 93 | $col = $this->definition->getRelation($column[0])->table->getColumn($column[1]); |
||
| 94 | if (!$col) { |
||
| 95 | throw new DBException('Invalid column name in related table'); |
||
| 96 | } |
||
| 97 | } else if (isset($this->joins[$column[0]])) { |
||
| 98 | $col = $this->joins[$column[0]]->table->getColumn($column[1]); |
||
| 99 | if (!$col) { |
||
| 100 | throw new DBException('Invalid column name in related table'); |
||
| 101 | } |
||
| 102 | } else { |
||
| 103 | throw new DBException('Invalid foreign table name: ' . implode(',', $column)); |
||
| 104 | } |
||
| 105 | } |
||
| 106 | return [ 'name' => implode('.', $column), 'data' => $col ]; |
||
| 107 | } |
||
| 108 | protected function normalizeValue(TableColumn $col, $value) |
||
| 109 | { |
||
| 110 | if ($value === null && $col->isNullable()) { |
||
| 111 | return null; |
||
| 112 | } |
||
| 113 | switch ($col->getBasicType()) { |
||
| 114 | case 'date': |
||
| 115 | if (is_string($value)) { |
||
| 116 | return date('Y-m-d', strtotime($value)); |
||
| 117 | } |
||
| 118 | if (is_int($value)) { |
||
| 119 | return date('Y-m-d', $value); |
||
| 120 | } |
||
| 121 | if ($value instanceof \DateTime) { |
||
| 122 | return $value->format('Y-m-d'); |
||
| 123 | } |
||
| 124 | return $value; |
||
| 125 | case 'datetime': |
||
| 126 | if (is_string($value)) { |
||
| 127 | return date('Y-m-d H:i:s', strtotime($value)); |
||
| 128 | } |
||
| 129 | if (is_int($value)) { |
||
| 130 | return date('Y-m-d H:i:s', $value); |
||
| 131 | } |
||
| 132 | if ($value instanceof \DateTime) { |
||
| 133 | return $value->format('Y-m-d H:i:s'); |
||
| 134 | } |
||
| 135 | return $value; |
||
| 136 | case 'enum': |
||
| 137 | if (is_int($value)) { |
||
| 138 | return $value; |
||
| 139 | } |
||
| 140 | if (!in_array($value, $col->getValues())) { |
||
| 141 | return 0; |
||
| 142 | } |
||
| 143 | return $value; |
||
| 144 | case 'int': |
||
| 145 | return (int)$value; |
||
| 146 | default: |
||
| 147 | return $value; |
||
| 148 | } |
||
| 149 | } |
||
| 150 | /** |
||
| 151 | * Filter the results by a column and a value |
||
| 152 | * @param string $column the column name to filter by (related columns can be used - for example: author.name) |
||
| 153 | * @param mixed $value a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3]) |
||
| 154 | * @return $this |
||
| 155 | */ |
||
| 156 | public function filter(string $column, $value) : TableQuery |
||
| 157 | { |
||
| 158 | list($name, $column) = array_values($this->getColumn($column)); |
||
| 159 | if (is_null($value)) { |
||
| 160 | return $this->where($name . ' IS NULL'); |
||
| 161 | } |
||
| 162 | if (!is_array($value)) { |
||
| 163 | return $this->where( |
||
| 164 | $name . ' = ?', |
||
| 165 | [ $this->normalizeValue($column, $value) ] |
||
| 166 | ); |
||
| 167 | } |
||
| 168 | if (isset($value['beg']) && isset($value['end'])) { |
||
| 169 | return $this->where( |
||
| 170 | $name.' BETWEEN ? AND ?', |
||
| 171 | [ |
||
| 172 | $this->normalizeValue($column, $value['beg']), |
||
| 173 | $this->normalizeValue($column, $value['end']) |
||
| 174 | ] |
||
| 175 | ); |
||
| 176 | } |
||
| 177 | return $this->where( |
||
| 178 | $name . ' IN (??)', |
||
| 179 | [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ] |
||
| 180 | ); |
||
| 181 | } |
||
| 182 | /** |
||
| 183 | * Sort by a column |
||
| 184 | * @param string $column the column name to sort by (related columns can be used - for example: author.name) |
||
| 185 | * @param bool|boolean $desc should the sorting be in descending order, defaults to `false` |
||
| 186 | * @return $this |
||
| 187 | */ |
||
| 188 | public function sort(string $column, bool $desc = false) : TableQuery |
||
| 192 | /** |
||
| 193 | * Group by a column (or columns) |
||
| 194 | * @param string|array $column the column name (or names) to group by |
||
| 195 | * @return $this |
||
| 196 | */ |
||
| 197 | public function group($column) : TableQuery |
||
| 198 | { |
||
| 199 | if (!is_array($column)) { |
||
| 200 | $column = [ $column ]; |
||
| 201 | } |
||
| 202 | foreach ($column as $k => $v) { |
||
| 203 | $column[$k] = $this->getColumn($v)['name']; |
||
| 204 | } |
||
| 205 | return $this->groupBy(implode(', ', $column), []); |
||
| 206 | } |
||
| 207 | /** |
||
| 208 | * Get a part of the data |
||
| 209 | * @param int|integer $page the page number to get (1-based), defaults to 1 |
||
| 210 | * @param int|integer $perPage the number of records per page - defaults to 25 |
||
| 211 | * @return $this |
||
| 212 | */ |
||
| 213 | public function paginate(int $page = 1, int $perPage = 25) : TableQuery |
||
| 217 | public function __call($name, $data) |
||
| 218 | { |
||
| 219 | if (strpos($name, 'filterBy') === 0) { |
||
| 220 | return $this->filter(strtolower(substr($name, 8)), $data[0]); |
||
| 221 | } |
||
| 222 | if (strpos($name, 'sortBy') === 0) { |
||
| 223 | return $this->sort(strtolower(substr($name, 6)), $data[0]); |
||
| 224 | } |
||
| 225 | if (strpos($name, 'groupBy') === 0) { |
||
| 226 | return $this->group(strtolower(substr($name, 7))); |
||
| 227 | } |
||
| 228 | } |
||
| 229 | /** |
||
| 230 | * Remove all filters, sorting, etc |
||
| 231 | * @return $this |
||
| 232 | */ |
||
| 233 | public function reset() : TableQuery |
||
| 245 | /** |
||
| 246 | * Apply advanced grouping |
||
| 247 | * @param string $sql SQL statement to use in the GROUP BY clause |
||
| 248 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 249 | * @return $this |
||
| 250 | */ |
||
| 251 | public function groupBy(string $sql, array $params = []) : TableQuery |
||
| 252 | { |
||
| 253 | $this->qiterator = null; |
||
| 254 | $this->group = [ $sql, $params ]; |
||
| 255 | return $this; |
||
| 256 | } |
||
| 257 | /** |
||
| 258 | * Join a table to the query (no need to do this for relations defined with foreign keys) |
||
| 259 | * @param Table|string $table the table to join |
||
| 260 | * @param array $fields what to join on (joined_table_field => other_field) |
||
| 261 | * @param string|null $name alias for the join, defaults to the table name |
||
| 262 | * @param bool $multiple are multiple rows joined (results in a LEFT JOIN), default to true |
||
| 263 | * @return $this |
||
| 264 | */ |
||
| 265 | public function join($table, array $fields, string $name = null, bool $multiple = true) |
||
| 266 | { |
||
| 267 | $table = $table instanceof Table ? $table : $this->db->definition((string)$table); |
||
| 268 | $name = $name ?? $table->getName(); |
||
| 269 | if (isset($this->joins[$name]) || $this->definition->hasRelation($name)) { |
||
| 270 | throw new DBException('Alias / table name already in use'); |
||
| 271 | } |
||
| 272 | $this->joins[$name] = new TableRelation($name, $table, [], $multiple); |
||
| 273 | foreach ($fields as $k => $v) { |
||
| 274 | $k = explode('.', $k, 2); |
||
| 275 | $k = count($k) == 2 ? $k[1] : $k[0]; |
||
| 276 | $this->joins[$name]->keymap[$this->getColumn($name . '.' . $k)['name']] = $this->getColumn($v)['name']; |
||
| 277 | } |
||
| 278 | return $this; |
||
| 279 | } |
||
| 280 | /** |
||
| 281 | * Apply an advanced filter (can be called multiple times) |
||
| 282 | * @param string $sql SQL statement to be used in the where clause |
||
| 283 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 284 | * @return $this |
||
| 285 | */ |
||
| 286 | public function where(string $sql, array $params = []) : TableQuery |
||
| 287 | { |
||
| 288 | $this->qiterator = null; |
||
| 289 | $this->where[] = [ $sql, $params ]; |
||
| 290 | return $this; |
||
| 291 | } |
||
| 292 | /** |
||
| 293 | * Apply an advanced HAVING filter (can be called multiple times) |
||
| 294 | * @param string $sql SQL statement to be used in the HAING clause |
||
| 295 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 296 | * @return $this |
||
| 297 | */ |
||
| 298 | public function having(string $sql, array $params = []) : TableQuery |
||
| 299 | { |
||
| 300 | $this->qiterator = null; |
||
| 301 | $this->having[] = [ $sql, $params ]; |
||
| 302 | return $this; |
||
| 303 | } |
||
| 304 | /** |
||
| 305 | * Apply advanced sorting |
||
| 306 | * @param string $sql SQL statement to use in the ORDER clause |
||
| 307 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 308 | * @return $this |
||
| 309 | */ |
||
| 310 | public function order(string $sql, array $params = []) : TableQuery |
||
| 311 | { |
||
| 312 | $this->qiterator = null; |
||
| 313 | $this->order = [ $sql, $params ]; |
||
| 314 | return $this; |
||
| 315 | } |
||
| 316 | /** |
||
| 317 | * Apply an advanced limit |
||
| 318 | * @param int $limit number of rows to return |
||
| 319 | * @param int $offset number of rows to skip from the beginning (defaults to 0) |
||
| 320 | * @return $this |
||
| 321 | */ |
||
| 322 | public function limit(int $limit, int $offset = 0) : TableQuery |
||
| 323 | { |
||
| 324 | $this->qiterator = null; |
||
| 325 | $this->li_of = [ $limit, $offset ]; |
||
| 326 | return $this; |
||
| 327 | } |
||
| 328 | /** |
||
| 329 | * Get the number of records |
||
| 330 | * @return int the total number of records (does not respect pagination) |
||
| 331 | */ |
||
| 332 | public function count() : int |
||
| 333 | { |
||
| 334 | $table = $this->definition->getName(); |
||
| 335 | $primary = $this->definition->getPrimaryKey(); |
||
| 336 | $sql = 'SELECT COUNT(DISTINCT '.$table.'.'.implode(', '.$table.'.', $primary).') FROM '.$table.' '; |
||
| 337 | $par = []; |
||
| 338 | |||
| 339 | $relations = $this->withr; |
||
| 340 | foreach ($this->definition->getRelations() as $k => $v) { |
||
| 341 | foreach ($this->where as $vv) { |
||
| 342 | if (strpos($vv[0], $k . '.') !== false) { |
||
| 343 | $relations[] = $k; |
||
| 344 | } |
||
| 345 | } |
||
| 346 | if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) { |
||
| 347 | $relations[] = $k; |
||
| 348 | } |
||
| 349 | } |
||
| 350 | |||
| 351 | foreach ($this->joins as $k => $v) { |
||
| 352 | $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
||
| 353 | $tmp = []; |
||
| 354 | foreach ($v->keymap as $kk => $vv) { |
||
| 355 | $tmp[] = $kk.' = '.$vv; |
||
| 356 | } |
||
| 357 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 358 | } |
||
| 359 | foreach (array_unique($relations) as $k) { |
||
| 360 | $v = $this->definition->getRelation($k); |
||
| 361 | if ($v->pivot) { |
||
| 362 | $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$k.'_pivot ON '; |
||
| 363 | $tmp = []; |
||
| 364 | foreach ($v->keymap as $kk => $vv) { |
||
| 365 | $tmp[] = $table.'.'.$kk.' = '.$k.'_pivot.'.$vv.' '; |
||
| 366 | } |
||
| 367 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 368 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON '; |
||
| 369 | $tmp = []; |
||
| 370 | foreach ($v->pivot_keymap as $kk => $vv) { |
||
| 371 | $tmp[] = $k.'.'.$vv.' = '.$k.'_pivot.'.$kk.' '; |
||
| 372 | } |
||
| 373 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 374 | } else { |
||
| 375 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON '; |
||
| 376 | $tmp = []; |
||
| 377 | foreach ($v->keymap as $kk => $vv) { |
||
| 378 | $tmp[] = $table.'.'.$kk.' = '.$k.'.'.$vv.' '; |
||
| 379 | } |
||
| 380 | if ($v->sql) { |
||
| 381 | $tmp[] = $v->sql . ' '; |
||
| 382 | $par = array_merge($par, $v->par ?? []); |
||
| 383 | } |
||
| 384 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 385 | } |
||
| 386 | } |
||
| 387 | if (count($this->where)) { |
||
| 388 | $sql .= 'WHERE '; |
||
| 389 | $tmp = []; |
||
| 390 | foreach ($this->where as $v) { |
||
| 391 | $tmp[] = '(' . $v[0] . ')'; |
||
| 392 | $par = array_merge($par, $v[1]); |
||
| 393 | } |
||
| 394 | $sql .= implode(' AND ', $tmp).' '; |
||
| 395 | } |
||
| 396 | if (count($this->group)) { |
||
| 397 | $sql .= 'GROUP BY ' . $this->group[0] . ' '; |
||
| 398 | $par = array_merge($par, $this->group[1]); |
||
| 399 | } |
||
| 400 | if (count($this->having)) { |
||
| 401 | $sql .= 'HAVING '; |
||
| 402 | $tmp = []; |
||
| 403 | foreach ($this->having as $v) { |
||
| 404 | $tmp[] = '(' . $v[0] . ')'; |
||
| 405 | $par = array_merge($par, $v[1]); |
||
| 406 | } |
||
| 407 | $sql .= implode(' AND ', $tmp).' '; |
||
| 408 | } |
||
| 409 | return $this->db->one($sql, $par); |
||
| 410 | } |
||
| 411 | /** |
||
| 412 | * Specify which columns to fetch (be default all table columns are fetched) |
||
| 413 | * @param array $fields optional array of columns to select (related columns can be used too) |
||
| 414 | * @return $this |
||
| 415 | */ |
||
| 416 | public function columns(array $fields) : TableQuery |
||
| 417 | { |
||
| 418 | foreach ($fields as $k => $v) { |
||
| 419 | if (strpos($v, '*') !== false) { |
||
| 420 | $temp = explode('.', $v); |
||
| 421 | if (count($temp) == 1) { |
||
| 422 | $table = $this->definition->getName(); |
||
| 423 | } else { |
||
| 424 | $table = $temp[0]; |
||
| 425 | } |
||
| 426 | $cols = []; |
||
| 427 | if ($this->definition->hasRelation($table)) { |
||
| 428 | $cols = $this->definition->getRelation($table)->table->getColumns(); |
||
| 429 | } else if (isset($this->joins[$table])) { |
||
| 430 | $cols = $this->joins[$table]->table->getColumns(); |
||
| 431 | } else { |
||
| 432 | throw new DBException('Invalid foreign table name'); |
||
| 433 | } |
||
| 434 | foreach ($cols as $col) { |
||
| 435 | $fields[] = $table . '.' . $col; |
||
| 436 | } |
||
| 437 | unset($fields[$k]); |
||
| 438 | } |
||
| 439 | } |
||
| 440 | $primary = $this->definition->getPrimaryKey(); |
||
| 441 | foreach ($fields as $k => $v) { |
||
| 442 | try { |
||
| 443 | $fields[$k] = $this->getColumn($v)['name']; |
||
| 444 | } catch (DBException $e) { |
||
| 445 | $fields[$k] = $v; |
||
| 446 | } |
||
| 447 | } |
||
| 448 | foreach ($primary as $field) { |
||
| 449 | $field = $this->getColumn($field)['name']; |
||
| 450 | if (!in_array($field, $fields)) { |
||
| 451 | $fields[] = $field; |
||
| 452 | } |
||
| 453 | } |
||
| 454 | $this->fields = $fields; |
||
| 455 | return $this; |
||
| 456 | } |
||
| 457 | /** |
||
| 458 | * Perform the actual fetch |
||
| 459 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 460 | * @return TableQueryIterator the query result as an iterator |
||
| 461 | */ |
||
| 462 | public function iterator(array $fields = null) : TableQueryIterator |
||
| 463 | { |
||
| 464 | if ($this->qiterator) { |
||
| 465 | return $this->qiterator; |
||
| 466 | } |
||
| 467 | $table = $this->definition->getName(); |
||
| 468 | $primary = $this->definition->getPrimaryKey(); |
||
| 469 | if ($fields !== null) { |
||
| 470 | $this->columns($fields); |
||
| 471 | } |
||
| 472 | $relations = $this->withr; |
||
| 473 | foreach ($this->definition->getRelations() as $k => $v) { |
||
| 474 | foreach ($this->fields as $field) { |
||
| 475 | if (strpos($field, $k . '.') === 0) { |
||
| 476 | $relations[] = $k; |
||
| 477 | } |
||
| 478 | } |
||
| 479 | foreach ($this->where as $v) { |
||
| 480 | if (strpos($v[0], $k . '.') !== false) { |
||
| 481 | $relations[] = $k; |
||
| 482 | } |
||
| 483 | } |
||
| 484 | if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) { |
||
| 485 | $relations[] = $k; |
||
| 486 | } |
||
| 487 | } |
||
| 488 | $select = []; |
||
| 489 | foreach ($this->fields as $k => $field) { |
||
| 490 | $select[] = $field . (!is_numeric($k) ? ' ' . $k : ''); |
||
| 491 | } |
||
| 492 | foreach ($this->withr as $relation) { |
||
| 493 | foreach ($this->definition->getRelation($relation)->table->getColumns() as $column) { |
||
| 494 | $select[] = $relation . '.' . $column . ' ' . $relation . '___' . $column; |
||
| 495 | } |
||
| 496 | } |
||
| 497 | $sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' '; |
||
| 498 | $par = []; |
||
| 499 | foreach ($this->joins as $k => $v) { |
||
| 500 | $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
||
| 501 | $tmp = []; |
||
| 502 | foreach ($v->keymap as $kk => $vv) { |
||
| 503 | $tmp[] = $kk.' = '.$vv; |
||
| 504 | } |
||
| 505 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 506 | } |
||
| 507 | foreach (array_unique($relations) as $relation) { |
||
| 508 | $v = $this->definition->getRelation($relation); |
||
| 509 | if ($v->pivot) { |
||
| 510 | $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$relation.'_pivot ON '; |
||
| 511 | $tmp = []; |
||
| 512 | foreach ($v->keymap as $kk => $vv) { |
||
| 513 | $tmp[] = $table.'.'.$kk.' = '.$relation.'_pivot.'.$vv.' '; |
||
| 514 | } |
||
| 515 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 516 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON '; |
||
| 517 | $tmp = []; |
||
| 518 | foreach ($v->pivot_keymap as $kk => $vv) { |
||
| 519 | $tmp[] = $relation.'.'.$vv.' = '.$relation.'_pivot.'.$kk.' '; |
||
| 520 | } |
||
| 521 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 522 | } else { |
||
| 523 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON '; |
||
| 524 | $tmp = []; |
||
| 525 | foreach ($v->keymap as $kk => $vv) { |
||
| 526 | $tmp[] = $table.'.'.$kk.' = '.$relation.'.'.$vv.' '; |
||
| 527 | } |
||
| 528 | if ($v->sql) { |
||
| 529 | $tmp[] = $v->sql . ' '; |
||
| 530 | $par = array_merge($par, $v->par ?? []); |
||
| 531 | } |
||
| 532 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 533 | } |
||
| 534 | } |
||
| 535 | if (count($this->where)) { |
||
| 536 | $sql .= 'WHERE '; |
||
| 537 | $tmp = []; |
||
| 538 | foreach ($this->where as $v) { |
||
| 539 | $tmp[] = '(' . $v[0] . ')'; |
||
| 540 | $par = array_merge($par, $v[1]); |
||
| 541 | } |
||
| 542 | $sql .= implode(' AND ', $tmp).' '; |
||
| 543 | } |
||
| 544 | if (count($this->group)) { |
||
| 545 | $sql .= 'GROUP BY ' . $this->group[0] . ' '; |
||
| 546 | $par = array_merge($par, $this->group[1]); |
||
| 547 | } |
||
| 548 | if (count($this->having)) { |
||
| 549 | $sql .= 'HAVING '; |
||
| 550 | $tmp = []; |
||
| 551 | foreach ($this->having as $v) { |
||
| 552 | $tmp[] = '(' . $v[0] . ')'; |
||
| 553 | $par = array_merge($par, $v[1]); |
||
| 554 | } |
||
| 555 | $sql .= implode(' AND ', $tmp).' '; |
||
| 556 | } |
||
| 557 | //if ($this->definition->hasRelations()) { |
||
| 558 | // $sql .= 'GROUP BY '.$table.'.'.implode(', '.$table.'.', $primary).' '; |
||
| 559 | //} |
||
| 560 | if (count($this->order)) { |
||
| 561 | $sql .= 'ORDER BY ' . $this->order[0] . ' '; |
||
| 562 | $par = array_merge($par, $this->order[1]); |
||
| 563 | } |
||
| 564 | $porder = []; |
||
| 565 | foreach ($primary as $field) { |
||
| 566 | $porder[] = $this->getColumn($field)['name']; |
||
| 567 | } |
||
| 568 | $sql .= (count($this->order) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' '; |
||
| 569 | |||
| 570 | if ($this->li_of[0]) { |
||
| 571 | if ($this->db->driver() === 'oracle') { |
||
| 572 | if ((int)($this->db->settings()->options['version'] ?? 0) >= 12) { |
||
| 573 | $sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY'; |
||
| 574 | } else { |
||
| 575 | $f = array_map(function ($v) { |
||
| 576 | $v = explode(' ', trim($v), 2); |
||
| 577 | if (count($v) === 2) { return $v[1]; } |
||
| 578 | $v = explode('.', $v[0], 2); |
||
| 579 | return count($v) === 2 ? $v[1] : $v[0]; |
||
| 580 | }, $select); |
||
| 581 | $sql = "SELECT " . implode(', ', $f) . " |
||
| 582 | FROM ( |
||
| 583 | SELECT tbl__.*, rownum rnum__ FROM ( |
||
| 584 | " . $sql . " |
||
| 585 | ) tbl__ |
||
| 586 | WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . " |
||
| 587 | ) WHERE rnum__ > " . $this->li_of[1]; |
||
| 588 | } |
||
| 589 | } else { |
||
| 590 | $sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1]; |
||
| 591 | } |
||
| 592 | } |
||
| 593 | return $this->qiterator = new TableQueryIterator( |
||
| 594 | $this->db->get($sql, $par), |
||
| 595 | $this->definition->getPrimaryKey(), |
||
| 596 | array_combine( |
||
| 597 | $this->withr, |
||
| 598 | array_map(function ($relation) { |
||
| 599 | return $this->definition->getRelation($relation); |
||
| 600 | }, $this->withr) |
||
| 601 | ) |
||
| 602 | ); |
||
| 603 | } |
||
| 604 | /** |
||
| 605 | * Perform the actual fetch |
||
| 606 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 607 | * @return array the query result as an array |
||
| 608 | */ |
||
| 609 | public function select(array $fields = null) : array |
||
| 610 | { |
||
| 611 | return iterator_to_array($this->iterator($fields)); |
||
| 612 | } |
||
| 613 | /** |
||
| 614 | * Insert a new row in the table |
||
| 615 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 616 | * @return array the inserted ID where keys are column names and values are column values |
||
| 617 | */ |
||
| 618 | public function insert(array $data) : array |
||
| 619 | { |
||
| 620 | $table = $this->definition->getName(); |
||
| 621 | $columns = $this->definition->getFullColumns(); |
||
| 622 | $insert = []; |
||
| 623 | foreach ($data as $column => $value) { |
||
| 624 | if (isset($columns[$column])) { |
||
| 625 | $insert[$column] = $this->normalizeValue($columns[$column], $value); |
||
| 626 | } |
||
| 627 | } |
||
| 628 | if (!count($insert)) { |
||
| 629 | throw new DBException('No valid columns to insert'); |
||
| 630 | } |
||
| 631 | $sql = 'INSERT INTO '.$table.' ('.implode(', ', array_keys($insert)).') VALUES (??)'; |
||
| 632 | $par = [$insert]; |
||
| 633 | //if ($update) { |
||
| 634 | // $sql .= 'ON DUPLICATE KEY UPDATE '; |
||
| 635 | // $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($insert))) . ' '; |
||
| 636 | // $par = array_merge($par, $insert); |
||
| 637 | //} |
||
| 638 | if ($this->db->driver() === 'oracle') { |
||
| 639 | $primary = $this->definition->getPrimaryKey(); |
||
| 640 | $ret = []; |
||
| 641 | foreach ($primary as $k) { |
||
| 642 | $ret[$k] = str_repeat(' ', 255); |
||
| 643 | $par[] = &$ret[$k]; |
||
| 644 | } |
||
| 645 | $sql .= ' RETURNING ' . implode(',', $primary) . ' INTO ' . implode(',', array_fill(0, count($primary), '?')); |
||
| 646 | $this->db->query($sql, $par); |
||
| 647 | return $ret; |
||
| 648 | } else { |
||
| 649 | $ret = []; |
||
| 650 | $ins = $this->db->query($sql, $par)->insertID(); |
||
| 651 | foreach ($this->definition->getPrimaryKey() as $k) { |
||
| 652 | $ret[$k] = $data[$k] ?? $ins; |
||
| 653 | } |
||
| 654 | return $ret; |
||
| 655 | } |
||
| 656 | } |
||
| 657 | /** |
||
| 658 | * Update the filtered rows with new data |
||
| 659 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 660 | * @return int the number of affected rows |
||
| 661 | */ |
||
| 662 | public function update(array $data) : int |
||
| 663 | { |
||
| 664 | $table = $this->definition->getName(); |
||
| 665 | $columns = $this->definition->getFullColumns(); |
||
| 666 | $update = []; |
||
| 667 | foreach ($data as $column => $value) { |
||
| 668 | if (isset($columns[$column])) { |
||
| 669 | $update[$column] = $this->normalizeValue($columns[$column], $value); |
||
| 670 | } |
||
| 671 | } |
||
| 672 | if (!count($update)) { |
||
| 673 | throw new DBException('No valid columns to update'); |
||
| 674 | } |
||
| 675 | $sql = 'UPDATE '.$table.' SET '; |
||
| 676 | $par = []; |
||
| 677 | $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($update))) . ' '; |
||
| 678 | $par = array_merge($par, array_values($update)); |
||
| 679 | if (count($this->where)) { |
||
| 680 | $sql .= 'WHERE '; |
||
| 681 | $tmp = []; |
||
| 682 | foreach ($this->where as $v) { |
||
| 683 | $tmp[] = $v[0]; |
||
| 684 | $par = array_merge($par, $v[1]); |
||
| 685 | } |
||
| 686 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 687 | } |
||
| 688 | if (count($this->order)) { |
||
| 689 | $sql .= $this->order[0]; |
||
| 690 | $par = array_merge($par, $this->order[1]); |
||
| 691 | } |
||
| 692 | return $this->db->query($sql, $par)->affected(); |
||
| 693 | } |
||
| 694 | /** |
||
| 695 | * Delete the filtered rows from the DB |
||
| 696 | * @return int the number of deleted rows |
||
| 697 | */ |
||
| 698 | public function delete() : int |
||
| 699 | { |
||
| 700 | $table = $this->definition->getName(); |
||
| 701 | $sql = 'DELETE FROM '.$table.' '; |
||
| 702 | $par = []; |
||
| 703 | if (count($this->where)) { |
||
| 704 | $sql .= 'WHERE '; |
||
| 705 | $tmp = []; |
||
| 706 | foreach ($this->where as $v) { |
||
| 707 | $tmp[] = $v[0]; |
||
| 708 | $par = array_merge($par, $v[1]); |
||
| 709 | } |
||
| 710 | $sql .= implode(' AND ', $tmp) . ' '; |
||
| 711 | } |
||
| 712 | if (count($this->order)) { |
||
| 713 | $sql .= $this->order[0]; |
||
| 714 | $par = array_merge($par, $this->order[1]); |
||
| 715 | } |
||
| 716 | return $this->db->query($sql, $par)->affected(); |
||
| 717 | } |
||
| 718 | /** |
||
| 719 | * Solve the n+1 queries problem by prefetching a relation by name |
||
| 720 | * @param string $relation the relation name to fetch along with the data |
||
| 721 | * @return $this |
||
| 722 | */ |
||
| 723 | public function with(string $relation) : TableQuery |
||
| 724 | { |
||
| 725 | if (!$this->definition->hasRelation($relation)) { |
||
| 726 | throw new DBException('Invalid relation name'); |
||
| 727 | } |
||
| 728 | $this->qiterator = null; |
||
| 729 | $this->withr[$relation] = $relation; |
||
| 730 | return $this; |
||
| 731 | } |
||
| 732 | |||
| 733 | public function getIterator() |
||
| 734 | { |
||
| 735 | return $this->iterator(); |
||
| 736 | } |
||
| 737 | |||
| 738 | public function offsetGet($offset) |
||
| 739 | { |
||
| 740 | return $this->iterator()->offsetGet($offset); |
||
| 741 | } |
||
| 742 | public function offsetExists($offset) |
||
| 754 | |||
| 755 | public function collection(array $fields = null) : Collection |
||
| 759 | } |
||
| 760 |
Loading history...
This check looks for PHPDoc comments describing methods or function parameters that do not exist on the corresponding method or function.
Consider the following example. The parameter
$italyis not defined by the methodfinale(...).The most likely cause is that the parameter was removed, but the annotation was not.