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 |
||
| 12 | class TableQuery implements \IteratorAggregate, \ArrayAccess, \Countable |
||
| 13 | { |
||
| 14 | const SEP = '___'; |
||
| 15 | /** |
||
| 16 | * @var DBInterface |
||
| 17 | */ |
||
| 18 | protected $db; |
||
| 19 | /** |
||
| 20 | * @var Table |
||
| 21 | */ |
||
| 22 | protected $definition; |
||
| 23 | /** |
||
| 24 | * @var TableQueryIterator|null |
||
| 25 | */ |
||
| 26 | protected $qiterator; |
||
| 27 | |||
| 28 | /** |
||
| 29 | * @var array |
||
| 30 | */ |
||
| 31 | protected $where = []; |
||
| 32 | /** |
||
| 33 | * @var array |
||
| 34 | */ |
||
| 35 | protected $order = []; |
||
| 36 | /** |
||
| 37 | * @var array |
||
| 38 | */ |
||
| 39 | protected $group = []; |
||
| 40 | /** |
||
| 41 | * @var array |
||
| 42 | */ |
||
| 43 | protected $having = []; |
||
| 44 | /** |
||
| 45 | * @var int[] |
||
| 46 | */ |
||
| 47 | protected $li_of = [0,0,0]; |
||
| 48 | /** |
||
| 49 | * @var array |
||
| 50 | */ |
||
| 51 | protected $fields = []; |
||
| 52 | /** |
||
| 53 | * @var array |
||
| 54 | */ |
||
| 55 | protected $withr = []; |
||
| 56 | /** |
||
| 57 | * @var array |
||
| 58 | */ |
||
| 59 | protected $joins = []; |
||
| 60 | /** |
||
| 61 | * @var array |
||
| 62 | */ |
||
| 63 | protected $pkey = []; |
||
| 64 | /** |
||
| 65 | * @var array |
||
| 66 | */ |
||
| 67 | protected $aliases = []; |
||
| 68 | |||
| 69 | /** |
||
| 70 | * Create an instance |
||
| 71 | * @param DBInterface $db the database connection |
||
| 72 | * @param Table|string $table the name or definition of the main table in the query |
||
| 73 | */ |
||
| 74 | 50 | public function __construct(DBInterface $db, $table) |
|
| 87 | /** |
||
| 88 | * Get the table definition of the queried table |
||
| 89 | * @return Table the definition |
||
| 90 | */ |
||
| 91 | public function getDefinition() : Table |
||
| 95 | |||
| 96 | 50 | protected function getColumn($column) |
|
| 227 | |||
| 228 | 16 | protected function filterSQL(string $column, $value, bool $negate = false) : array |
|
| 321 | /** |
||
| 322 | * Filter the results by a column and a value |
||
| 323 | * @param string $column the column name to filter by (related columns can be used - for example: author.name) |
||
| 324 | * @param mixed $value a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3]) |
||
| 325 | * @param bool $negate optional boolean indicating that the filter should be negated |
||
| 326 | * @return $this |
||
| 327 | */ |
||
| 328 | 12 | public function filter(string $column, $value, bool $negate = false) : self |
|
| 333 | /** |
||
| 334 | * Filter the results matching any of the criteria |
||
| 335 | * @param array $criteria each row is a column, value and optional negate flag (same as filter method) |
||
| 336 | * @return $this |
||
| 337 | */ |
||
| 338 | 6 | public function any(array $criteria) : self |
|
| 351 | /** |
||
| 352 | * Filter the results matching all of the criteria |
||
| 353 | * @param array $criteria each row is a column, value and optional negate flag (same as filter method) |
||
| 354 | * @return $this |
||
| 355 | */ |
||
| 356 | 4 | public function all(array $criteria) : self |
|
| 369 | /** |
||
| 370 | * Sort by a column |
||
| 371 | * @param string $column the column name to sort by (related columns can be used - for example: author.name) |
||
| 372 | * @param bool|boolean $desc should the sorting be in descending order, defaults to `false` |
||
| 373 | * @return $this |
||
| 374 | */ |
||
| 375 | public function sort(string $column, bool $desc = false) : self |
||
| 379 | /** |
||
| 380 | * Group by a column (or columns) |
||
| 381 | * @param string|array $column the column name (or names) to group by |
||
| 382 | * @return $this |
||
| 383 | */ |
||
| 384 | 2 | public function group($column) : self |
|
| 394 | /** |
||
| 395 | * Get a part of the data |
||
| 396 | * @param int|integer $page the page number to get (1-based), defaults to 1 |
||
| 397 | * @param int|integer $perPage the number of records per page - defaults to 25 |
||
| 398 | * @return $this |
||
| 399 | */ |
||
| 400 | public function paginate(int $page = 1, int $perPage = 25) : self |
||
| 416 | /** |
||
| 417 | * Remove all filters, sorting, etc |
||
| 418 | * @return $this |
||
| 419 | */ |
||
| 420 | 6 | public function reset() : self |
|
| 433 | /** |
||
| 434 | * Apply advanced grouping |
||
| 435 | * @param string $sql SQL statement to use in the GROUP BY clause |
||
| 436 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 437 | * @return $this |
||
| 438 | */ |
||
| 439 | 2 | public function groupBy(string $sql, array $params = []) : self |
|
| 445 | /** |
||
| 446 | * Join a table to the query (no need to do this for relations defined with foreign keys) |
||
| 447 | * @param Table|string $table the table to join |
||
| 448 | * @param array $fields what to join on (joined_table_field => other_field) |
||
| 449 | * @param string|null $name alias for the join, defaults to the table name |
||
| 450 | * @param bool $multiple are multiple rows joined (results in a LEFT JOIN), default to true |
||
| 451 | * @return $this |
||
| 452 | */ |
||
| 453 | 2 | public function join($table, array $fields, string $name = null, bool $multiple = true) |
|
| 468 | /** |
||
| 469 | * Apply an advanced filter (can be called multiple times) |
||
| 470 | * @param string $sql SQL statement to be used in the where clause |
||
| 471 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 472 | * @return $this |
||
| 473 | */ |
||
| 474 | 18 | public function where(string $sql, array $params = []) : self |
|
| 480 | /** |
||
| 481 | * Apply an advanced HAVING filter (can be called multiple times) |
||
| 482 | * @param string $sql SQL statement to be used in the HAING clause |
||
| 483 | * @param array $params parameters for the SQL statement (defaults to an empty array) |
||
| 484 | * @return $this |
||
| 485 | */ |
||
| 486 | 2 | public function having(string $sql, array $params = []) : self |
|
| 492 | /** |
||
| 493 | * Apply advanced sorting |
||
| 494 | * @param string $sql SQL statement to use in the ORDER clause |
||
| 495 | * @param array $params optional params for the statement (defaults to an empty array) |
||
| 496 | * @return $this |
||
| 497 | */ |
||
| 498 | 2 | public function order(string $sql, array $params = []) : self |
|
| 513 | /** |
||
| 514 | * Apply an advanced limit |
||
| 515 | * @param int $limit number of rows to return |
||
| 516 | * @param int $offset number of rows to skip from the beginning (defaults to 0) |
||
| 517 | * @return $this |
||
| 518 | */ |
||
| 519 | 2 | public function limit(int $limit, int $offset = 0, bool $limitOnMainTable = false) : self |
|
| 525 | /** |
||
| 526 | * Get the number of records |
||
| 527 | * @return int the total number of records (does not respect pagination) |
||
| 528 | */ |
||
| 529 | 16 | public function count() : int |
|
| 643 | /** |
||
| 644 | * Specify which columns to fetch (be default all table columns are fetched) |
||
| 645 | * @param array $fields optional array of columns to select (related columns can be used too) |
||
| 646 | * @return $this |
||
| 647 | */ |
||
| 648 | 50 | public function columns(array $fields) : self |
|
| 700 | /** |
||
| 701 | * Perform the actual fetch |
||
| 702 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 703 | * @return \Iterator the query result as an iterator |
||
| 704 | */ |
||
| 705 | 36 | public function iterator(array $fields = null) : \Iterator |
|
| 706 | { |
||
| 707 | 36 | if ($this->qiterator) { |
|
| 708 | 22 | return $this->qiterator; |
|
| 709 | } |
||
| 710 | 36 | $aliases = []; |
|
| 711 | 36 | $getAlias = function ($name) use (&$aliases) { |
|
| 712 | // to bypass use: return $name; |
||
| 713 | 12 | return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases); |
|
| 714 | 36 | }; |
|
| 715 | 36 | $table = $this->definition->getName(); |
|
| 716 | 36 | if ($fields !== null) { |
|
| 717 | 2 | $this->columns($fields); |
|
| 718 | } |
||
| 719 | 36 | $relations = $this->withr; |
|
| 720 | 36 | foreach ($relations as $k => $v) { |
|
| 721 | 10 | $getAlias($k); |
|
| 722 | } |
||
| 723 | |||
| 724 | 36 | $f = $this->fields; |
|
| 725 | 36 | $w = $this->where; |
|
| 726 | 36 | $h = $this->having; |
|
| 727 | 36 | $o = $this->order; |
|
| 728 | 36 | $g = $this->group; |
|
| 729 | $j = array_map(function ($v) { return clone $v; }, $this->joins); |
||
| 730 | |||
| 731 | 36 | $porder = []; |
|
| 732 | 36 | foreach ($this->definition->getPrimaryKey() as $field) { |
|
| 733 | 36 | $porder[] = $this->getColumn($field)['name']; |
|
| 734 | } |
||
| 735 | |||
| 736 | 36 | foreach ($this->definition->getRelations() as $k => $relation) { |
|
| 737 | 36 | foreach ($f as $kk => $field) { |
|
| 738 | 36 | if (strpos($field, $k . '.') === 0) { |
|
| 739 | $relations[$k] = [ $relation, $table ]; |
||
| 740 | 36 | $f[$kk] = str_replace($k . '.', $getAlias($k) . '.', $field); |
|
| 741 | } |
||
| 742 | } |
||
| 743 | 36 | foreach ($w as $kk => $v) { |
|
| 744 | 4 | if (preg_match('(\b'.preg_quote($k . '.'). ')i', $v[0])) { |
|
| 745 | 2 | $relations[$k] = [ $relation, $table ]; |
|
| 746 | 4 | $w[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $v[0]); |
|
| 747 | } |
||
| 748 | } |
||
| 749 | 36 | foreach ($h as $kk => $v) { |
|
| 750 | 2 | if (preg_match('(\b'.preg_quote($k . '.'). ')i', $v[0])) { |
|
| 751 | $relations[$k] = [ $relation, $table ]; |
||
| 752 | 2 | $h[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $v[0]); |
|
| 753 | } |
||
| 754 | } |
||
| 755 | 36 | if (isset($o[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $o[0])) { |
|
| 756 | $relations[$k] = [ $relation, $table ]; |
||
| 757 | $o[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $o[0]); |
||
| 758 | } |
||
| 759 | 36 | if (isset($g[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $g[0])) { |
|
| 760 | $relations[$k] = [ $relation, $table ]; |
||
| 761 | $g[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $g[0]); |
||
| 762 | } |
||
| 763 | 36 | foreach ($j as $kk => $v) { |
|
| 764 | 2 | foreach ($v->keymap as $kkk => $vv) { |
|
| 765 | 2 | if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv)) { |
|
| 766 | $relations[$k] = [ $relation, $table ]; |
||
| 767 | 36 | $j[$k]->keymap[$kkk] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv); |
|
| 768 | } |
||
| 769 | } |
||
| 770 | } |
||
| 771 | } |
||
| 772 | 36 | $select = []; |
|
| 773 | 36 | foreach ($f as $k => $field) { |
|
| 774 | 36 | $select[] = $field . (!is_numeric($k) ? ' ' . $k : ''); |
|
| 775 | } |
||
| 776 | 36 | foreach ($this->withr as $name => $relation) { |
|
| 777 | 10 | foreach ($relation[0]->table->getColumns() as $column) { |
|
| 778 | 10 | $select[] = $getAlias($name) . '.' . $column . ' ' . $getAlias($name . static::SEP . $column); |
|
| 779 | } |
||
| 780 | } |
||
| 781 | 36 | $sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' '; |
|
| 782 | 36 | $par = []; |
|
| 783 | 36 | $many = false; |
|
| 784 | 36 | foreach ($j as $k => $v) { |
|
| 785 | 2 | if ($v->many) { |
|
| 786 | 2 | $many = true; |
|
| 787 | } |
||
| 788 | 2 | $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
|
| 789 | 2 | $tmp = []; |
|
| 790 | 2 | foreach ($v->keymap as $kk => $vv) { |
|
| 791 | 2 | $tmp[] = $kk.' = '.$vv; |
|
| 792 | } |
||
| 793 | 2 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 794 | } |
||
| 795 | 36 | foreach ($relations as $relation => $v) { |
|
| 796 | 12 | $table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1]; |
|
| 797 | 12 | $v = $v[0]; |
|
| 798 | 12 | if ($v->many || $v->pivot) { |
|
| 799 | 10 | $many = true; |
|
| 800 | } |
||
| 801 | 12 | if ($v->pivot) { |
|
| 802 | 8 | $alias = $getAlias($relation.'_pivot'); |
|
| 803 | 8 | $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON '; |
|
| 804 | 8 | $tmp = []; |
|
| 805 | 8 | foreach ($v->keymap as $kk => $vv) { |
|
| 806 | 8 | $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
| 807 | } |
||
| 808 | 8 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 809 | 8 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($relation).' ON '; |
|
| 810 | 8 | $tmp = []; |
|
| 811 | 8 | foreach ($v->pivot_keymap as $kk => $vv) { |
|
| 812 | 8 | $tmp[] = $getAlias($relation).'.'.$vv.' = '.$alias.'.'.$kk.' '; |
|
| 813 | } |
||
| 814 | 8 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 815 | } else { |
||
| 816 | 10 | $alias = $getAlias($relation); |
|
| 817 | |||
| 818 | 10 | $sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON '; |
|
| 819 | 10 | $tmp = []; |
|
| 820 | 10 | foreach ($v->keymap as $kk => $vv) { |
|
| 821 | 10 | $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
| 822 | } |
||
| 823 | 10 | if ($v->sql) { |
|
| 824 | $tmp[] = $v->sql . ' '; |
||
| 825 | $par = array_merge($par, $v->par ?? []); |
||
| 826 | } |
||
| 827 | 12 | $sql .= implode(' AND ', $tmp) . ' '; |
|
| 828 | } |
||
| 829 | } |
||
| 830 | |||
| 831 | 36 | if ($many && count($porder) && $this->li_of[2] === 1) { |
|
| 832 | $ids = $this->ids(); |
||
| 833 | if (count($ids)) { |
||
| 834 | if (count($porder) > 1) { |
||
| 835 | $pkw = []; |
||
| 836 | foreach ($porder as $name) { |
||
| 837 | $pkw[] = $name . ' = ?'; |
||
| 838 | } |
||
| 839 | $pkw = '(' . implode(' AND ', $pkw) . ')'; |
||
| 840 | $pkp = []; |
||
| 841 | foreach ($ids as $id) { |
||
| 842 | foreach ($id as $p) { |
||
| 843 | $pkp[] = $p; |
||
| 844 | } |
||
| 845 | } |
||
| 846 | $w[] = [ |
||
| 847 | implode(' OR ', array_fill(0, count($ids), $pkw)), |
||
| 848 | $pkp |
||
| 849 | ]; |
||
| 850 | } else { |
||
| 851 | $w[] = [ $porder[0] . ' IN ('.implode(',', array_fill(0, count($ids), '?')).')', $ids ]; |
||
| 852 | } |
||
| 853 | } else { |
||
| 854 | $w[] = [ '1=0', [] ]; |
||
| 855 | } |
||
| 856 | } |
||
| 857 | 36 | if (count($w)) { |
|
| 858 | 4 | $sql .= 'WHERE '; |
|
| 859 | 4 | $tmp = []; |
|
| 860 | 4 | foreach ($w as $v) { |
|
| 861 | 4 | $tmp[] = '(' . $v[0] . ')'; |
|
| 862 | 4 | $par = array_merge($par, $v[1]); |
|
| 863 | } |
||
| 864 | 4 | $sql .= implode(' AND ', $tmp).' '; |
|
| 865 | } |
||
| 866 | 36 | if (count($g)) { |
|
| 867 | 2 | $sql .= 'GROUP BY ' . $g[0] . ' '; |
|
| 868 | 2 | $par = array_merge($par, $g[1]); |
|
| 869 | } |
||
| 870 | 36 | if (count($h)) { |
|
| 871 | 2 | $sql .= 'HAVING '; |
|
| 872 | 2 | $tmp = []; |
|
| 873 | 2 | foreach ($h as $v) { |
|
| 874 | 2 | $tmp[] = '(' . $v[0] . ')'; |
|
| 875 | 2 | $par = array_merge($par, $v[1]); |
|
| 876 | } |
||
| 877 | 2 | $sql .= implode(' AND ', $tmp).' '; |
|
| 878 | } |
||
| 879 | 36 | if (count($o)) { |
|
| 880 | 2 | $sql .= 'ORDER BY ' . $o[0] . ' '; |
|
| 881 | 2 | $par = array_merge($par, $o[1]); |
|
| 882 | } |
||
| 883 | 36 | if (count($porder)) { |
|
| 884 | 36 | $pdir = (count($o) && strpos($o[0], 'DESC') !== false) ? 'DESC' : 'ASC'; |
|
| 885 | $porder = array_map(function ($v) use ($pdir) { return $v . ' ' . $pdir; }, $porder); |
||
| 886 | 36 | $sql .= (count($o) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' '; |
|
| 887 | } |
||
| 888 | 36 | if ((!$many || $this->li_of[2] === 0 || !count($porder)) && $this->li_of[0]) { |
|
| 889 | 2 | if ($this->db->driverName() === 'oracle') { |
|
| 890 | if ((int)$this->db->driverOption('version', 0) >= 12) { |
||
| 891 | $sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY'; |
||
| 892 | } else { |
||
| 893 | $f = array_map(function ($v) { |
||
| 894 | $v = explode(' ', trim($v), 2); |
||
| 895 | if (count($v) === 2) { return $v[1]; } |
||
| 896 | $v = explode('.', $v[0], 2); |
||
| 897 | return count($v) === 2 ? $v[1] : $v[0]; |
||
| 898 | }, $select); |
||
| 899 | $sql = "SELECT " . implode(', ', $f) . " |
||
| 900 | FROM ( |
||
| 901 | SELECT tbl__.*, rownum rnum__ FROM ( |
||
| 902 | " . $sql . " |
||
| 903 | ) tbl__ |
||
| 904 | WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . " |
||
| 905 | ) WHERE rnum__ > " . $this->li_of[1]; |
||
| 906 | } |
||
| 907 | } else { |
||
| 908 | 2 | $sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1]; |
|
| 909 | } |
||
| 910 | } |
||
| 911 | 36 | return $this->qiterator = new TableQueryIterator( |
|
| 912 | 36 | $this->db->get($sql, $par, null, false, false), |
|
| 913 | 36 | $this->pkey, |
|
| 914 | 36 | $this->withr, |
|
| 915 | 36 | $aliases |
|
| 916 | ); |
||
| 917 | } |
||
| 918 | /** |
||
| 919 | * Perform the actual fetch |
||
| 920 | * @param array|null $fields optional array of columns to select (related columns can be used too) |
||
| 921 | * @return array the query result as an array |
||
| 922 | */ |
||
| 923 | 2 | public function select(array $fields = null) : array |
|
| 927 | /** |
||
| 928 | * Insert a new row in the table |
||
| 929 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 930 | * @return array the inserted ID where keys are column names and values are column values |
||
| 931 | */ |
||
| 932 | 6 | public function insert(array $data) : array |
|
| 970 | /** |
||
| 971 | * Update the filtered rows with new data |
||
| 972 | * @param array $data key value pairs, where each key is the column name and the value is the value to insert |
||
| 973 | * @return int the number of affected rows |
||
| 974 | */ |
||
| 975 | 2 | public function update(array $data) : int |
|
| 1007 | /** |
||
| 1008 | * Delete the filtered rows from the DB |
||
| 1009 | * @return int the number of deleted rows |
||
| 1010 | */ |
||
| 1011 | 2 | public function delete() : int |
|
| 1031 | /** |
||
| 1032 | * Solve the n+1 queries problem by prefetching a relation by name |
||
| 1033 | * @param string $relation the relation name to fetch along with the data |
||
| 1034 | * @return $this |
||
| 1035 | */ |
||
| 1036 | 10 | public function with(string $relation) : self |
|
| 1056 | |||
| 1057 | 6 | public function getIterator() |
|
| 1061 | |||
| 1062 | 30 | public function offsetGet($offset) |
|
| 1078 | |||
| 1079 | public function collection(array $fields = null) : Collection |
||
| 1083 | |||
| 1084 | public function ids() |
||
| 1266 | public function find($primary) |
||
| 1267 | { |
||
| 1285 | } |
||
| 1286 |
Loading history...
This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently.
Both the
$myVarassignment in line 1 and the$higherassignment in line 2 are dead. The first because$myVaris never used and the second because$higheris always overwritten for every possible time line.