Complex classes like QueryBuilder 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 QueryBuilder, and based on these observations, apply Extract Interface, too.
1 | <?php declare(strict_types=1); |
||
26 | class QueryBuilder implements QueryBuilderInterface { |
||
27 | |||
28 | /** |
||
29 | * Convenience property for connection management |
||
30 | * @var string |
||
31 | */ |
||
32 | public $connName = ''; |
||
33 | |||
34 | /** |
||
35 | * List of queries executed |
||
36 | * @var array |
||
37 | */ |
||
38 | public $queries = [ |
||
39 | 'total_time' => 0 |
||
40 | ]; |
||
41 | |||
42 | /** |
||
43 | * Whether to do only an explain on the query |
||
44 | * @var boolean |
||
45 | */ |
||
46 | protected $explain = FALSE; |
||
47 | |||
48 | /** |
||
49 | * The current database driver |
||
50 | * @var DriverInterface |
||
51 | */ |
||
52 | public $driver; |
||
53 | |||
54 | /** |
||
55 | * Query parser class instance |
||
56 | * @var QueryParser |
||
57 | */ |
||
58 | protected $parser; |
||
59 | |||
60 | /** |
||
61 | * Query Builder state |
||
62 | * @var State |
||
63 | */ |
||
64 | protected $state; |
||
65 | |||
66 | // -------------------------------------------------------------------------- |
||
67 | // ! Methods |
||
68 | // -------------------------------------------------------------------------- |
||
69 | |||
70 | /** |
||
71 | * Constructor |
||
72 | * |
||
73 | * @param DriverInterface $driver |
||
74 | * @param QueryParser $parser |
||
75 | */ |
||
76 | public function __construct(DriverInterface $driver, QueryParser $parser) |
||
85 | |||
86 | /** |
||
87 | * Destructor |
||
88 | * @codeCoverageIgnore |
||
89 | */ |
||
90 | public function __destruct() |
||
94 | |||
95 | /** |
||
96 | * Calls a function further down the inheritance chain |
||
97 | * |
||
98 | * @param string $name |
||
99 | * @param array $params |
||
100 | * @return mixed |
||
101 | * @throws BadMethodCallException |
||
102 | */ |
||
103 | public function __call(string $name, array $params) |
||
112 | |||
113 | // -------------------------------------------------------------------------- |
||
114 | // ! Select Queries |
||
115 | // -------------------------------------------------------------------------- |
||
116 | |||
117 | /** |
||
118 | * Specifies rows to select in a query |
||
119 | * |
||
120 | * @param string $fields |
||
121 | * @return QueryBuilderInterface |
||
122 | */ |
||
123 | public function select(string $fields): QueryBuilderInterface |
||
157 | |||
158 | /** |
||
159 | * Selects the maximum value of a field from a query |
||
160 | * |
||
161 | * @param string $field |
||
162 | * @param string|bool $as |
||
163 | * @return QueryBuilderInterface |
||
164 | */ |
||
165 | public function selectMax(string $field, $as=FALSE): QueryBuilderInterface |
||
171 | |||
172 | /** |
||
173 | * Selects the minimum value of a field from a query |
||
174 | * |
||
175 | * @param string $field |
||
176 | * @param string|bool $as |
||
177 | * @return QueryBuilderInterface |
||
178 | */ |
||
179 | public function selectMin(string $field, $as=FALSE): QueryBuilderInterface |
||
185 | |||
186 | /** |
||
187 | * Selects the average value of a field from a query |
||
188 | * |
||
189 | * @param string $field |
||
190 | * @param string|bool $as |
||
191 | * @return QueryBuilderInterface |
||
192 | */ |
||
193 | public function selectAvg(string $field, $as=FALSE): QueryBuilderInterface |
||
199 | |||
200 | /** |
||
201 | * Selects the sum of a field from a query |
||
202 | * |
||
203 | * @param string $field |
||
204 | * @param string|bool $as |
||
205 | * @return QueryBuilderInterface |
||
206 | */ |
||
207 | public function selectSum(string $field, $as=FALSE): QueryBuilderInterface |
||
213 | |||
214 | /** |
||
215 | * Adds the 'distinct' keyword to a query |
||
216 | * |
||
217 | * @return QueryBuilderInterface |
||
218 | */ |
||
219 | public function distinct(): QueryBuilderInterface |
||
225 | |||
226 | /** |
||
227 | * Tell the database to give you the query plan instead of result set |
||
228 | * |
||
229 | * @return QueryBuilderInterface |
||
230 | */ |
||
231 | public function explain(): QueryBuilderInterface |
||
236 | |||
237 | /** |
||
238 | * Specify the database table to select from |
||
239 | * |
||
240 | * @param string $tblname |
||
241 | * @return QueryBuilderInterface |
||
242 | */ |
||
243 | public function from(string $tblname): QueryBuilderInterface |
||
258 | |||
259 | // -------------------------------------------------------------------------- |
||
260 | // ! 'Like' methods |
||
261 | // -------------------------------------------------------------------------- |
||
262 | |||
263 | /** |
||
264 | * Creates a Like clause in the sql statement |
||
265 | * |
||
266 | * @param string $field |
||
267 | * @param mixed $val |
||
268 | * @param string $pos |
||
269 | * @return QueryBuilderInterface |
||
270 | */ |
||
271 | public function like(string $field, $val, string $pos='both'): QueryBuilderInterface |
||
275 | |||
276 | /** |
||
277 | * Generates an OR Like clause |
||
278 | * |
||
279 | * @param string $field |
||
280 | * @param mixed $val |
||
281 | * @param string $pos |
||
282 | * @return QueryBuilderInterface |
||
283 | */ |
||
284 | public function orLike(string $field, $val, string $pos='both'): QueryBuilderInterface |
||
288 | |||
289 | /** |
||
290 | * Generates a NOT LIKE clause |
||
291 | * |
||
292 | * @param string $field |
||
293 | * @param mixed $val |
||
294 | * @param string $pos |
||
295 | * @return QueryBuilderInterface |
||
296 | */ |
||
297 | public function notLike(string $field, $val, string $pos='both'): QueryBuilderInterface |
||
301 | |||
302 | /** |
||
303 | * Generates a OR NOT LIKE clause |
||
304 | * |
||
305 | * @param string $field |
||
306 | * @param mixed $val |
||
307 | * @param string $pos |
||
308 | * @return QueryBuilderInterface |
||
309 | */ |
||
310 | public function orNotLike(string $field, $val, string $pos='both'): QueryBuilderInterface |
||
314 | |||
315 | // -------------------------------------------------------------------------- |
||
316 | // ! Having methods |
||
317 | // -------------------------------------------------------------------------- |
||
318 | |||
319 | /** |
||
320 | * Generates a 'Having' clause |
||
321 | * |
||
322 | * @param mixed $key |
||
323 | * @param mixed $val |
||
324 | * @return QueryBuilderInterface |
||
325 | */ |
||
326 | public function having($key, $val=[]): QueryBuilderInterface |
||
330 | |||
331 | /** |
||
332 | * Generates a 'Having' clause prefixed with 'OR' |
||
333 | * |
||
334 | * @param mixed $key |
||
335 | * @param mixed $val |
||
336 | * @return QueryBuilderInterface |
||
337 | */ |
||
338 | public function orHaving($key, $val=[]): QueryBuilderInterface |
||
342 | |||
343 | // -------------------------------------------------------------------------- |
||
344 | // ! 'Where' methods |
||
345 | // -------------------------------------------------------------------------- |
||
346 | |||
347 | /** |
||
348 | * Specify condition(s) in the where clause of a query |
||
349 | * Note: this function works with key / value, or a |
||
350 | * passed array with key / value pairs |
||
351 | * |
||
352 | * @param mixed $key |
||
353 | * @param mixed $val |
||
354 | * @param mixed $escape |
||
355 | * @return QueryBuilderInterface |
||
356 | */ |
||
357 | public function where($key, $val=[], $escape=NULL): QueryBuilderInterface |
||
361 | |||
362 | /** |
||
363 | * Where clause prefixed with "OR" |
||
364 | * |
||
365 | * @param string $key |
||
366 | * @param mixed $val |
||
367 | * @return QueryBuilderInterface |
||
368 | */ |
||
369 | public function orWhere($key, $val=[]): QueryBuilderInterface |
||
373 | |||
374 | /** |
||
375 | * Where clause with 'IN' statement |
||
376 | * |
||
377 | * @param mixed $field |
||
378 | * @param mixed $val |
||
379 | * @return QueryBuilderInterface |
||
380 | */ |
||
381 | public function whereIn($field, $val=[]): QueryBuilderInterface |
||
385 | |||
386 | /** |
||
387 | * Where in statement prefixed with "or" |
||
388 | * |
||
389 | * @param string $field |
||
390 | * @param mixed $val |
||
391 | * @return QueryBuilderInterface |
||
392 | */ |
||
393 | public function orWhereIn($field, $val=[]): QueryBuilderInterface |
||
397 | |||
398 | /** |
||
399 | * WHERE NOT IN (FOO) clause |
||
400 | * |
||
401 | * @param string $field |
||
402 | * @param mixed $val |
||
403 | * @return QueryBuilderInterface |
||
404 | */ |
||
405 | public function whereNotIn($field, $val=[]): QueryBuilderInterface |
||
409 | |||
410 | /** |
||
411 | * OR WHERE NOT IN (FOO) clause |
||
412 | * |
||
413 | * @param string $field |
||
414 | * @param mixed $val |
||
415 | * @return QueryBuilderInterface |
||
416 | */ |
||
417 | public function orWhereNotIn($field, $val=[]): QueryBuilderInterface |
||
421 | |||
422 | // -------------------------------------------------------------------------- |
||
423 | // ! Other Query Modifier methods |
||
424 | // -------------------------------------------------------------------------- |
||
425 | |||
426 | /** |
||
427 | * Sets values for inserts / updates / deletes |
||
428 | * |
||
429 | * @param mixed $key |
||
430 | * @param mixed $val |
||
431 | * @return QueryBuilderInterface |
||
432 | */ |
||
433 | public function set($key, $val = NULL): QueryBuilderInterface |
||
464 | |||
465 | /** |
||
466 | * Creates a join phrase in a compiled query |
||
467 | * |
||
468 | * @param string $table |
||
469 | * @param string $condition |
||
470 | * @param string $type |
||
471 | * @return QueryBuilderInterface |
||
472 | */ |
||
473 | public function join(string $table, string $condition, string $type=''): QueryBuilderInterface |
||
489 | |||
490 | /** |
||
491 | * Group the results by the selected field(s) |
||
492 | * |
||
493 | * @param mixed $field |
||
494 | * @return QueryBuilderInterface |
||
495 | */ |
||
496 | public function groupBy($field): QueryBuilderInterface |
||
514 | |||
515 | /** |
||
516 | * Order the results by the selected field(s) |
||
517 | * |
||
518 | * @param string $field |
||
519 | * @param string $type |
||
520 | * @return QueryBuilderInterface |
||
521 | */ |
||
522 | public function orderBy(string $field, string $type=''): QueryBuilderInterface |
||
553 | |||
554 | /** |
||
555 | * Set a limit on the current sql statement |
||
556 | * |
||
557 | * @param int $limit |
||
558 | * @param int|bool $offset |
||
559 | * @return QueryBuilderInterface |
||
560 | */ |
||
561 | public function limit(int $limit, $offset=FALSE): QueryBuilderInterface |
||
568 | |||
569 | // -------------------------------------------------------------------------- |
||
570 | // ! Query Grouping Methods |
||
571 | // -------------------------------------------------------------------------- |
||
572 | |||
573 | /** |
||
574 | * Adds a paren to the current query for query grouping |
||
575 | * |
||
576 | * @return QueryBuilderInterface |
||
577 | */ |
||
578 | public function groupStart(): QueryBuilderInterface |
||
586 | |||
587 | /** |
||
588 | * Adds a paren to the current query for query grouping, |
||
589 | * prefixed with 'NOT' |
||
590 | * |
||
591 | * @return QueryBuilderInterface |
||
592 | */ |
||
593 | public function notGroupStart(): QueryBuilderInterface |
||
601 | |||
602 | /** |
||
603 | * Adds a paren to the current query for query grouping, |
||
604 | * prefixed with 'OR' |
||
605 | * |
||
606 | * @return QueryBuilderInterface |
||
607 | */ |
||
608 | public function orGroupStart(): QueryBuilderInterface |
||
614 | |||
615 | /** |
||
616 | * Adds a paren to the current query for query grouping, |
||
617 | * prefixed with 'OR NOT' |
||
618 | * |
||
619 | * @return QueryBuilderInterface |
||
620 | */ |
||
621 | public function orNotGroupStart(): QueryBuilderInterface |
||
627 | |||
628 | /** |
||
629 | * Ends a query group |
||
630 | * |
||
631 | * @return QueryBuilderInterface |
||
632 | */ |
||
633 | public function groupEnd(): QueryBuilderInterface |
||
639 | |||
640 | // -------------------------------------------------------------------------- |
||
641 | // ! Query execution methods |
||
642 | // -------------------------------------------------------------------------- |
||
643 | |||
644 | /** |
||
645 | * Select and retrieve all records from the current table, and/or |
||
646 | * execute current compiled query |
||
647 | * |
||
648 | * @param string $table |
||
649 | * @param int|bool $limit |
||
650 | * @param int|bool $offset |
||
651 | * @return PDOStatement |
||
652 | */ |
||
653 | public function get(string $table='', $limit=FALSE, $offset=FALSE): PDOStatement |
||
669 | |||
670 | /** |
||
671 | * Convenience method for get() with a where clause |
||
672 | * |
||
673 | * @param string $table |
||
674 | * @param mixed $where |
||
675 | * @param int|bool $limit |
||
676 | * @param int|bool $offset |
||
677 | * @return PDOStatement |
||
678 | */ |
||
679 | public function getWhere(string $table, $where=[], $limit=FALSE, $offset=FALSE): PDOStatement |
||
687 | |||
688 | /** |
||
689 | * Retrieve the number of rows in the selected table |
||
690 | * |
||
691 | * @param string $table |
||
692 | * @return int |
||
693 | */ |
||
694 | public function countAll(string $table): int |
||
700 | |||
701 | /** |
||
702 | * Retrieve the number of results for the generated query - used |
||
703 | * in place of the get() method |
||
704 | * |
||
705 | * @param string $table |
||
706 | * @param boolean $reset |
||
707 | * @return int |
||
708 | */ |
||
709 | public function countAllResults(string $table='', bool $reset = TRUE): int |
||
722 | |||
723 | /** |
||
724 | * Creates an insert clause, and executes it |
||
725 | * |
||
726 | * @param string $table |
||
727 | * @param mixed $data |
||
728 | * @return PDOStatement |
||
729 | */ |
||
730 | public function insert(string $table, $data=[]): PDOStatement |
||
739 | |||
740 | /** |
||
741 | * Creates and executes a batch insertion query |
||
742 | * |
||
743 | * @param string $table |
||
744 | * @param array $data |
||
745 | * @return PDOStatement |
||
746 | */ |
||
747 | public function insertBatch(string $table, $data=[]): PDOStatement |
||
756 | |||
757 | /** |
||
758 | * Creates an update clause, and executes it |
||
759 | * |
||
760 | * @param string $table |
||
761 | * @param mixed $data |
||
762 | * @return PDOStatement |
||
763 | */ |
||
764 | public function update(string $table, $data=[]): PDOStatement |
||
773 | |||
774 | /** |
||
775 | * Creates a batch update, and executes it. |
||
776 | * Returns the number of affected rows |
||
777 | * |
||
778 | * @param string $table |
||
779 | * @param array $data |
||
780 | * @param string $where |
||
781 | * @return int|null |
||
782 | */ |
||
783 | public function updateBatch(string $table, array $data, string $where): ?int |
||
796 | |||
797 | /** |
||
798 | * Deletes data from a table |
||
799 | * |
||
800 | * @param string $table |
||
801 | * @param mixed $where |
||
802 | * @return PDOStatement |
||
803 | */ |
||
804 | public function delete(string $table, $where=''): PDOStatement |
||
814 | |||
815 | // -------------------------------------------------------------------------- |
||
816 | // ! SQL Returning Methods |
||
817 | // -------------------------------------------------------------------------- |
||
818 | |||
819 | /** |
||
820 | * Returns the generated 'select' sql query |
||
821 | * |
||
822 | * @param string $table |
||
823 | * @param bool $reset |
||
824 | * @return string |
||
825 | */ |
||
826 | public function getCompiledSelect(string $table='', bool $reset=TRUE): string |
||
836 | |||
837 | /** |
||
838 | * Returns the generated 'insert' sql query |
||
839 | * |
||
840 | * @param string $table |
||
841 | * @param bool $reset |
||
842 | * @return string |
||
843 | */ |
||
844 | public function getCompiledInsert(string $table, bool $reset=TRUE): string |
||
848 | |||
849 | /** |
||
850 | * Returns the generated 'update' sql query |
||
851 | * |
||
852 | * @param string $table |
||
853 | * @param bool $reset |
||
854 | * @return string |
||
855 | */ |
||
856 | public function getCompiledUpdate(string $table='', bool $reset=TRUE): string |
||
860 | |||
861 | /** |
||
862 | * Returns the generated 'delete' sql query |
||
863 | * |
||
864 | * @param string $table |
||
865 | * @param bool $reset |
||
866 | * @return string |
||
867 | */ |
||
868 | public function getCompiledDelete(string $table='', bool $reset=TRUE): string |
||
872 | |||
873 | // -------------------------------------------------------------------------- |
||
874 | // ! Miscellaneous Methods |
||
875 | // -------------------------------------------------------------------------- |
||
876 | |||
877 | /** |
||
878 | * Clear out the class variables, so the next query can be run |
||
879 | * |
||
880 | * @return void |
||
881 | */ |
||
882 | public function resetQuery(): void |
||
887 | |||
888 | /** |
||
889 | * Method to simplify select_ methods |
||
890 | * |
||
891 | * @param string $field |
||
892 | * @param string|bool $as |
||
893 | * @return string |
||
894 | */ |
||
895 | protected function _select(string $field, $as = FALSE): string |
||
910 | |||
911 | /** |
||
912 | * Helper function for returning sql strings |
||
913 | * |
||
914 | * @param string $type |
||
915 | * @param string $table |
||
916 | * @param bool $reset |
||
917 | * @return string |
||
918 | */ |
||
919 | protected function _getCompile(string $type, string $table, bool $reset): string |
||
931 | |||
932 | /** |
||
933 | * Simplify 'like' methods |
||
934 | * |
||
935 | * @param string $field |
||
936 | * @param mixed $val |
||
937 | * @param string $pos |
||
938 | * @param string $like |
||
939 | * @param string $conj |
||
940 | * @return self |
||
941 | */ |
||
942 | protected function _like(string $field, $val, string $pos, string $like='LIKE', string $conj='AND'): self |
||
970 | |||
971 | /** |
||
972 | * Simplify building having clauses |
||
973 | * |
||
974 | * @param mixed $key |
||
975 | * @param mixed $values |
||
976 | * @param string $conj |
||
977 | * @return self |
||
978 | */ |
||
979 | protected function _having($key, $values=[], string $conj='AND'): self |
||
1006 | |||
1007 | /** |
||
1008 | * Do all the redundant stuff for where/having type methods |
||
1009 | * |
||
1010 | * @param mixed $key |
||
1011 | * @param mixed $val |
||
1012 | * @return array |
||
1013 | */ |
||
1014 | protected function _where($key, $val=[]): array |
||
1036 | |||
1037 | /** |
||
1038 | * Simplify generating where string |
||
1039 | * |
||
1040 | * @param mixed $key |
||
1041 | * @param mixed $values |
||
1042 | * @param string $defaultConj |
||
1043 | * @return self |
||
1044 | */ |
||
1045 | protected function _whereString($key, $values=[], string $defaultConj='AND'): self |
||
1082 | |||
1083 | /** |
||
1084 | * Simplify where_in methods |
||
1085 | * |
||
1086 | * @param mixed $key |
||
1087 | * @param mixed $val |
||
1088 | * @param string $in - The (not) in fragment |
||
1089 | * @param string $conj - The where in conjunction |
||
1090 | * @return self |
||
1091 | */ |
||
1092 | protected function _whereIn($key, $val=[], string $in='IN', string $conj='AND'): self |
||
1105 | |||
1106 | /** |
||
1107 | * Executes the compiled query |
||
1108 | * |
||
1109 | * @param string $type |
||
1110 | * @param string $table |
||
1111 | * @param string $sql |
||
1112 | * @param array|null $vals |
||
1113 | * @param boolean $reset |
||
1114 | * @return PDOStatement |
||
1115 | */ |
||
1116 | protected function _run(string $type, string $table, string $sql=NULL, array $vals=NULL, bool $reset=TRUE): PDOStatement |
||
1148 | |||
1149 | /** |
||
1150 | * Convert the prepared statement into readable sql |
||
1151 | * |
||
1152 | * @param array $values |
||
1153 | * @param string $sql |
||
1154 | * @param int $totalTime |
||
1155 | * @return void |
||
1156 | */ |
||
1157 | protected function _appendQuery(array $values, string $sql, int $totalTime): void |
||
1185 | |||
1186 | /** |
||
1187 | * Sub-method for generating sql strings |
||
1188 | * |
||
1189 | * @codeCoverageIgnore |
||
1190 | * @param string $type |
||
1191 | * @param string $table |
||
1192 | * @return string |
||
1193 | */ |
||
1194 | protected function _compileType(string $type='', string $table=''): string |
||
1234 | |||
1235 | /** |
||
1236 | * String together the sql statements for sending to the db |
||
1237 | * |
||
1238 | * @param string $type |
||
1239 | * @param string $table |
||
1240 | * @return string |
||
1241 | */ |
||
1242 | protected function _compile(string $type='', string $table=''): string |
||
1288 | } |
||
1289 |
This check looks at variables that are passed out again to other methods.
If the outgoing method call has stricter type requirements than the method itself, an issue is raised.
An additional type check may prevent trouble.