thecodingmachine /
tdbm
| Total Complexity | 47 |
| Total Lines | 514 |
| Duplicated Lines | 0 % |
| Changes | 6 | ||
| Bugs | 0 | Features | 0 |
Complex classes like FindObjectsFromRawSqlQueryFactory 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.
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 FindObjectsFromRawSqlQueryFactory, and based on these observations, apply Extract Interface, too.
| 1 | <?php |
||
| 21 | class FindObjectsFromRawSqlQueryFactory implements QueryFactory |
||
| 22 | { |
||
| 23 | /** |
||
| 24 | * @var array[] |
||
| 25 | */ |
||
| 26 | protected $columnDescriptors; |
||
| 27 | /** |
||
| 28 | * @var Schema |
||
| 29 | */ |
||
| 30 | private $schema; |
||
| 31 | /** |
||
| 32 | * @var string |
||
| 33 | */ |
||
| 34 | private $processedSql; |
||
| 35 | /** |
||
| 36 | * @var string |
||
| 37 | */ |
||
| 38 | private $processedSqlCount; |
||
| 39 | /** |
||
| 40 | * @var TDBMService |
||
| 41 | */ |
||
| 42 | private $tdbmService; |
||
| 43 | /** |
||
| 44 | * @var string |
||
| 45 | */ |
||
| 46 | private $mainTable; |
||
| 47 | |||
| 48 | /** |
||
| 49 | * FindObjectsFromRawSqlQueryFactory constructor. |
||
| 50 | * @param TDBMService $tdbmService |
||
| 51 | * @param Schema $schema |
||
| 52 | * @param string $mainTable |
||
| 53 | * @param string $sql |
||
| 54 | * @param string $sqlCount |
||
| 55 | */ |
||
| 56 | public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null) |
||
| 57 | { |
||
| 58 | $this->tdbmService = $tdbmService; |
||
| 59 | $this->schema = $schema; |
||
| 60 | $this->mainTable = $mainTable; |
||
| 61 | |||
| 62 | [$this->processedSql, $this->processedSqlCount, $this->columnDescriptors] = $this->compute($sql, $sqlCount); |
||
| 63 | } |
||
| 64 | |||
| 65 | public function sort($orderBy): void |
||
| 66 | { |
||
| 67 | throw new TDBMException('sort not supported for raw sql queries'); |
||
| 68 | } |
||
| 69 | |||
| 70 | public function getMagicSql(): string |
||
| 71 | { |
||
| 72 | return $this->processedSql; |
||
| 73 | } |
||
| 74 | |||
| 75 | public function getMagicSqlCount(): string |
||
| 76 | { |
||
| 77 | return $this->processedSqlCount; |
||
| 78 | } |
||
| 79 | |||
| 80 | public function getColumnDescriptors(): array |
||
| 81 | { |
||
| 82 | return $this->columnDescriptors; |
||
| 83 | } |
||
| 84 | |||
| 85 | /** |
||
| 86 | * @param string $sql |
||
| 87 | * @param null|string $sqlCount |
||
| 88 | * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors] |
||
| 89 | * @throws TDBMException |
||
| 90 | */ |
||
| 91 | private function compute(string $sql, ?string $sqlCount): array |
||
| 92 | { |
||
| 93 | $parser = new PHPSQLParser(); |
||
| 94 | $parsedSql = $parser->parse($sql); |
||
| 95 | |||
| 96 | if (isset($parsedSql['SELECT'])) { |
||
| 97 | [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedSelectQuery($parsedSql, $sqlCount); |
||
| 98 | } elseif (isset($parsedSql['UNION'])) { |
||
| 99 | [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedUnionQuery($parsedSql, $sqlCount); |
||
| 100 | } else { |
||
| 101 | throw new TDBMException('Unable to analyze query "'.$sql.'"'); |
||
| 102 | } |
||
| 103 | |||
| 104 | return [$processedSql, $processedSqlCount, $columnDescriptors]; |
||
| 105 | } |
||
| 106 | |||
| 107 | /** |
||
| 108 | * @param mixed[] $parsedSql |
||
| 109 | * @param null|string $sqlCount |
||
| 110 | * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors] |
||
| 111 | * @throws \PHPSQLParser\exceptions\UnsupportedFeatureException|\PHPSQLParser\exceptions\UnableToCreateSQLException |
||
| 112 | */ |
||
| 113 | private function processParsedUnionQuery(array $parsedSql, ?string $sqlCount): array |
||
| 114 | { |
||
| 115 | $selects = $parsedSql['UNION']; |
||
| 116 | |||
| 117 | $parsedSqlList = []; |
||
| 118 | $columnDescriptors = []; |
||
| 119 | |||
| 120 | foreach ($selects as $select) { |
||
| 121 | [$selectProcessedSql, $selectProcessedCountSql, $columnDescriptors] = $this->processParsedSelectQuery($select, ''); |
||
| 122 | |||
| 123 | // Let's reparse the returned SQL (not the most efficient way of doing things) |
||
| 124 | $parser = new PHPSQLParser(); |
||
| 125 | $parsedSelectSql = $parser->parse($selectProcessedSql); |
||
| 126 | |||
| 127 | $parsedSqlList[] = $parsedSelectSql; |
||
| 128 | } |
||
| 129 | |||
| 130 | // Let's rebuild the UNION query |
||
| 131 | $query = ['UNION' => $parsedSqlList]; |
||
| 132 | |||
| 133 | // The count is the SUM of the count of the UNIONs |
||
| 134 | $countQuery = $this->generateWrappedSqlCount($query); |
||
| 135 | |||
| 136 | $generator = new PHPSQLCreator(); |
||
| 137 | |||
| 138 | // Replaced the default generator by our own to add parenthesis around each SELECT |
||
| 139 | $processedSql = $this->buildUnion($query); |
||
| 140 | $processedSqlCount = $generator->create($countQuery); |
||
| 141 | |||
| 142 | // Let's add the ORDER BY if any |
||
| 143 | if (isset($parsedSql['0']['ORDER'])) { |
||
| 144 | $orderByBuilder = new OrderByBuilder(); |
||
| 145 | $processedSql .= " " . $orderByBuilder->build($parsedSql['0']['ORDER']); |
||
| 146 | } |
||
| 147 | |||
| 148 | return [$processedSql, $sqlCount ?? $processedSqlCount, $columnDescriptors]; |
||
| 149 | } |
||
| 150 | |||
| 151 | /** |
||
| 152 | * @param mixed[] $parsed |
||
| 153 | */ |
||
| 154 | private function buildUnion(array $parsed): string |
||
| 155 | { |
||
| 156 | $selectBuilder = new SelectStatementBuilder(); |
||
| 157 | |||
| 158 | return implode(' UNION ', array_map(function ($clause) use ($selectBuilder) { |
||
| 159 | return '(' . $selectBuilder->build($clause) . ')'; |
||
| 160 | }, $parsed['UNION'])); |
||
| 161 | } |
||
| 162 | |||
| 163 | /** |
||
| 164 | * @param mixed[] $parsedSql |
||
| 165 | * @param null|string $sqlCount |
||
| 166 | * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors] |
||
| 167 | */ |
||
| 168 | private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array |
||
| 187 | } |
||
| 188 | |||
| 189 | /** |
||
| 190 | * @param mixed[] $baseSelect |
||
| 191 | * @return mixed[] An array of 2 elements: [$formattedSelect, $columnDescriptors] |
||
| 192 | * @throws TDBMException |
||
| 193 | * @throws \Doctrine\DBAL\Schema\SchemaException |
||
| 194 | */ |
||
| 195 | private function formatSelect(array $baseSelect): array |
||
| 196 | { |
||
| 197 | $relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable); |
||
| 198 | $tableGroup = $this->getTableGroupName($relatedTables); |
||
| 199 | |||
| 200 | $connection = $this->tdbmService->getConnection(); |
||
| 201 | $formattedSelect = []; |
||
| 202 | $formattedCountSelect = []; |
||
| 203 | $columnDescriptors = []; |
||
| 204 | $fetchedTables = []; |
||
| 205 | |||
| 206 | foreach ($baseSelect as $entry) { |
||
| 207 | if ($entry['expr_type'] !== 'colref') { |
||
| 208 | $formattedSelect[] = $entry; |
||
| 209 | $formattedCountSelect[] = $entry; |
||
| 210 | continue; |
||
| 211 | } |
||
| 212 | |||
| 213 | $noQuotes = $entry['no_quotes']; |
||
| 214 | if ($noQuotes['delim'] !== '.' || count($noQuotes['parts']) !== 2) { |
||
| 215 | $formattedSelect[] = $entry; |
||
| 216 | $formattedCountSelect[] = $entry; |
||
| 217 | continue; |
||
| 218 | } |
||
| 219 | |||
| 220 | $tableName = $noQuotes['parts'][0]; |
||
| 221 | if (!in_array($tableName, $relatedTables)) { |
||
| 222 | $formattedSelect[] = $entry; |
||
| 223 | $formattedCountSelect[] = $entry; |
||
| 224 | continue; |
||
| 225 | } |
||
| 226 | |||
| 227 | $columnName = $noQuotes['parts'][1]; |
||
| 228 | if ($columnName !== '*') { |
||
| 229 | $formattedSelect[] = $entry; |
||
| 230 | $formattedCountSelect[] = $entry; |
||
| 231 | continue; |
||
| 232 | } |
||
| 233 | |||
| 234 | $table = $this->schema->getTable($tableName); |
||
| 235 | $pkColumns = $table->getPrimaryKeyColumns(); |
||
|
|
|||
| 236 | foreach ($table->getColumns() as $column) { |
||
| 237 | $columnName = $column->getName(); |
||
| 238 | $alias = AbstractQueryFactory::getColumnAlias($tableName, $columnName); |
||
| 239 | $astColumn = [ |
||
| 240 | 'expr_type' => 'colref', |
||
| 241 | 'base_expr' => $connection->quoteIdentifier($tableName) . '.' . $connection->quoteIdentifier($columnName), |
||
| 242 | 'no_quotes' => [ |
||
| 243 | 'delim' => '.', |
||
| 244 | 'parts' => [ |
||
| 245 | $tableName, |
||
| 246 | $columnName |
||
| 247 | ] |
||
| 248 | ], |
||
| 249 | 'alias' => [ |
||
| 250 | 'as' => true, |
||
| 251 | 'name' => $connection->quoteIdentifier($alias), |
||
| 252 | ] |
||
| 253 | ]; |
||
| 254 | $formattedSelect[] = $astColumn; |
||
| 255 | if (array_key_exists($columnName, $pkColumns)) { |
||
| 256 | $formattedCountSelect[] = $astColumn; |
||
| 257 | } |
||
| 258 | $columnDescriptors[$alias] = [ |
||
| 259 | 'as' => $alias, |
||
| 260 | 'table' => $tableName, |
||
| 261 | 'column' => $columnName, |
||
| 262 | 'type' => $column->getType(), |
||
| 263 | 'tableGroup' => $tableGroup, |
||
| 264 | ]; |
||
| 265 | } |
||
| 266 | $fetchedTables[] = $tableName; |
||
| 267 | } |
||
| 268 | |||
| 269 | $missingTables = array_diff($relatedTables, $fetchedTables); |
||
| 270 | if (!empty($missingTables)) { |
||
| 271 | throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement'); |
||
| 272 | } |
||
| 273 | |||
| 274 | for ($i = 0; $i < count($formattedSelect) - 1; $i++) { |
||
| 275 | if (!isset($formattedSelect[$i]['delim'])) { |
||
| 276 | $formattedSelect[$i]['delim'] = ','; |
||
| 277 | } |
||
| 278 | } |
||
| 279 | |||
| 280 | for ($i = 0; $i < count($formattedCountSelect) - 1; $i++) { |
||
| 281 | if (!isset($formattedCountSelect[$i]['delim'])) { |
||
| 282 | $formattedCountSelect[$i]['delim'] = ','; |
||
| 283 | } |
||
| 284 | } |
||
| 285 | return [$formattedSelect, $formattedCountSelect, $columnDescriptors]; |
||
| 286 | } |
||
| 287 | |||
| 288 | /** |
||
| 289 | * @param mixed[] $parsedSql |
||
| 290 | * @return mixed[] |
||
| 291 | */ |
||
| 292 | private function generateParsedSqlCount(array $parsedSql): array |
||
| 293 | { |
||
| 294 | if (isset($parsedSql['ORDER'])) { |
||
| 295 | unset($parsedSql['ORDER']); |
||
| 296 | } |
||
| 297 | |||
| 298 | if (!isset($parsedSql['GROUP'])) { |
||
| 299 | // most simple case:no GROUP BY in query |
||
| 300 | return $this->generateSimpleSqlCount($parsedSql); |
||
| 301 | } elseif (!isset($parsedSql['HAVING'])) { |
||
| 302 | // GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns |
||
| 303 | return $this->generateGroupedSqlCount($parsedSql); |
||
| 304 | } else { |
||
| 305 | // GROUP BY with a HAVING statement: we'll have to wrap the query |
||
| 306 | return $this->generateWrappedSqlCount($parsedSql); |
||
| 307 | } |
||
| 308 | } |
||
| 309 | |||
| 310 | /** |
||
| 311 | * @param mixed[] $parsedSql The AST of the SQL query |
||
| 312 | * @return mixed[] An AST representing the matching COUNT query |
||
| 313 | */ |
||
| 314 | private function generateSimpleSqlCount(array $parsedSql): array |
||
| 315 | { |
||
| 316 | // If the query is a DISTINCT, we need to deal with the count. |
||
| 317 | |||
| 318 | |||
| 319 | // We need to count on the same columns: COUNT(DISTINCT country.id, country.label) .... |
||
| 320 | // but we need to remove the "alias" bit. |
||
| 321 | |||
| 322 | if ($this->isDistinctQuery($parsedSql)) { |
||
| 323 | // Only MySQL can do DISTINCT counts. |
||
| 324 | // Other databases should wrap the query |
||
| 325 | if (!$this->tdbmService->getConnection()->getDatabasePlatform() instanceof MySqlPlatform) { |
||
| 326 | return $this->generateWrappedSqlCount($parsedSql); |
||
| 327 | } |
||
| 328 | |||
| 329 | $countSubExpr = array_map(function (array $item) { |
||
| 330 | unset($item['alias']); |
||
| 331 | return $item; |
||
| 332 | }, $parsedSql['SELECT']); |
||
| 333 | } else { |
||
| 334 | $countSubExpr = [ |
||
| 335 | [ |
||
| 336 | 'expr_type' => 'colref', |
||
| 337 | 'base_expr' => '*', |
||
| 338 | 'sub_tree' => false |
||
| 339 | ] |
||
| 340 | ]; |
||
| 341 | } |
||
| 342 | |||
| 343 | $parsedSql['SELECT'] = [[ |
||
| 344 | 'expr_type' => 'aggregate_function', |
||
| 345 | 'alias' => [ |
||
| 346 | 'as' => true, |
||
| 347 | 'name' => 'cnt', |
||
| 348 | ], |
||
| 349 | 'base_expr' => 'COUNT', |
||
| 350 | 'sub_tree' => $countSubExpr, |
||
| 351 | 'delim' => false, |
||
| 352 | ]]; |
||
| 353 | |||
| 354 | return $parsedSql; |
||
| 355 | } |
||
| 356 | |||
| 357 | /** |
||
| 358 | * @param mixed[] $parsedSql AST to analyze |
||
| 359 | * @return bool |
||
| 360 | */ |
||
| 361 | private function isDistinctQuery(array $parsedSql): bool |
||
| 362 | { |
||
| 363 | foreach ($parsedSql['SELECT'] as $item) { |
||
| 364 | if ($item['expr_type'] === 'reserved' && $item['base_expr'] === 'DISTINCT') { |
||
| 365 | return true; |
||
| 366 | } |
||
| 367 | } |
||
| 368 | return false; |
||
| 369 | } |
||
| 370 | |||
| 371 | /** |
||
| 372 | * @param mixed[] $parsedSql The AST of the SQL query |
||
| 373 | * @return mixed[] An AST representing the matching COUNT query |
||
| 374 | */ |
||
| 375 | private function generateGroupedSqlCount(array $parsedSql): array |
||
| 472 | } |
||
| 473 | |||
| 474 | /** |
||
| 475 | * @param mixed[] $parsedSql The AST of the SQL query |
||
| 476 | * @return mixed[] An AST representing the matching COUNT query |
||
| 477 | */ |
||
| 478 | private function generateWrappedSqlCount(array $parsedSql): array |
||
| 479 | { |
||
| 480 | return [ |
||
| 481 | 'SELECT' => [[ |
||
| 482 | 'expr_type' => 'aggregate_function', |
||
| 483 | 'alias' => [ |
||
| 484 | 'as' => true, |
||
| 485 | 'name' => 'cnt', |
||
| 486 | ], |
||
| 487 | 'base_expr' => 'COUNT', |
||
| 488 | 'sub_tree' => [ |
||
| 489 | [ |
||
| 490 | 'expr_type' => 'colref', |
||
| 491 | 'base_expr' => '*', |
||
| 492 | 'sub_tree' => false |
||
| 493 | ] |
||
| 494 | ], |
||
| 495 | 'delim' => false, |
||
| 496 | ]], |
||
| 497 | 'FROM' => [[ |
||
| 498 | 'expr_type' => 'subquery', |
||
| 499 | 'alias' => [ |
||
| 500 | 'as' => true, |
||
| 501 | 'name' => '____query' |
||
| 502 | ], |
||
| 503 | 'sub_tree' => $parsedSql, |
||
| 504 | ]] |
||
| 505 | ]; |
||
| 506 | } |
||
| 507 | |||
| 508 | /** |
||
| 509 | * @param string[] $relatedTables |
||
| 510 | * @return string |
||
| 511 | */ |
||
| 512 | protected function getTableGroupName(array $relatedTables): string |
||
| 516 | } |
||
| 517 | |||
| 518 | /** |
||
| 519 | * Returns a sub-query to be used in another query. |
||
| 520 | * A sub-query is similar to a query except it returns only the primary keys of the table (to be used as filters) |
||
| 521 | * |
||
| 522 | * @return string |
||
| 523 | */ |
||
| 524 | public function getMagicSqlSubQuery(): string |
||
| 525 | { |
||
| 527 | } |
||
| 528 | |||
| 529 | /** |
||
| 530 | * @return array<int, array{table: string, column: string}> An array of column descriptors. |
||
| 531 | */ |
||
| 532 | public function getSubQueryColumnDescriptors(): array |
||
| 537 |
This function has been deprecated. The supplier of the function has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the function will be removed and what other function to use instead.