FindObjectsFromRawSqlQueryFactory::buildUnion() - Code Metrics - Inspection of "Adding ORDER support for UNION queries" - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Push — 5.3 ( 77be5c...7b5354 )

unknown

created 2023-05-24 20:21 UTC

FindObjectsFromRawSqlQueryFactory::buildUnion() A

↳ Parent: FindObjectsFromRawSqlQueryFactory

Complexity

Conditions	1
Paths	1

Size

Total Lines	7
Code Lines	4

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
eloc	4
c	0
b	0
f	0
dl	0
loc	7
rs	10
cc	1
nc	1
nop	1

<?php

declare(strict_types=1);

namespace TheCodingMachine\TDBM\QueryFactory;

use Doctrine\DBAL\Platforms\MySqlPlatform;
use Doctrine\DBAL\Schema\Schema;
use PHPSQLParser\builders\OrderByBuilder;
use PHPSQLParser\builders\SelectStatementBuilder;
use TheCodingMachine\TDBM\TDBMException;
use TheCodingMachine\TDBM\TDBMService;
use PHPSQLParser\PHPSQLCreator;
use PHPSQLParser\PHPSQLParser;

use function array_merge;

/**
 * This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
 */
class FindObjectsFromRawSqlQueryFactory implements QueryFactory
{
    /**
     * @var array[]
     */
    protected $columnDescriptors;
    /**
     * @var Schema
     */
    private $schema;
    /**
     * @var string
     */
    private $processedSql;
    /**
     * @var string
     */
    private $processedSqlCount;
    /**
     * @var TDBMService
     */
    private $tdbmService;
    /**
     * @var string
     */
    private $mainTable;

    /**
     * FindObjectsFromRawSqlQueryFactory constructor.
     * @param TDBMService $tdbmService
     * @param Schema $schema
     * @param string $mainTable
     * @param string $sql
     * @param string $sqlCount
     */
    public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null)
    {
        $this->tdbmService = $tdbmService;
        $this->schema = $schema;
        $this->mainTable = $mainTable;

        [$this->processedSql, $this->processedSqlCount, $this->columnDescriptors] = $this->compute($sql, $sqlCount);
    }

    public function sort($orderBy): void
    {
        throw new TDBMException('sort not supported for raw sql queries');
    }

    public function getMagicSql(): string
    {
        return $this->processedSql;
    }

    public function getMagicSqlCount(): string
    {
        return $this->processedSqlCount;
    }

    public function getColumnDescriptors(): array
    {
        return $this->columnDescriptors;
    }

    /**
     * @param string $sql
     * @param null|string $sqlCount
     * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors]
     * @throws TDBMException
     */
    private function compute(string $sql, ?string $sqlCount): array
    {
        $parser = new PHPSQLParser();
        $parsedSql = $parser->parse($sql);

        if (isset($parsedSql['SELECT'])) {
            [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedSelectQuery($parsedSql, $sqlCount);
        } elseif (isset($parsedSql['UNION'])) {
            [$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedUnionQuery($parsedSql, $sqlCount);
        } else {
            throw new TDBMException('Unable to analyze query "'.$sql.'"');
        }

        return [$processedSql, $processedSqlCount, $columnDescriptors];
    }

    /**
     * @param mixed[] $parsedSql
     * @param null|string $sqlCount
     * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors]
     * @throws \PHPSQLParser\exceptions\UnsupportedFeatureException|\PHPSQLParser\exceptions\UnableToCreateSQLException
     */
    private function processParsedUnionQuery(array $parsedSql, ?string $sqlCount): array
    {
        $selects = $parsedSql['UNION'];

        $parsedSqlList = [];
        $columnDescriptors = [];

        foreach ($selects as $select) {
            [$selectProcessedSql, $selectProcessedCountSql, $columnDescriptors] = $this->processParsedSelectQuery($select, '');

            // Let's reparse the returned SQL (not the most efficient way of doing things)
            $parser = new PHPSQLParser();
            $parsedSelectSql = $parser->parse($selectProcessedSql);

            $parsedSqlList[] = $parsedSelectSql;
        }

        // Let's rebuild the UNION query
        $query = ['UNION' => $parsedSqlList];

        // The count is the SUM of the count of the UNIONs
        $countQuery = $this->generateWrappedSqlCount($query);

        $generator = new PHPSQLCreator();

        // Replaced the default generator by our own to add parenthesis around each SELECT
        $processedSql = $this->buildUnion($query);
        $processedSqlCount = $generator->create($countQuery);

        // Let's add the ORDER BY if any
        if (isset($parsedSql['0']['ORDER'])) {
            $orderByBuilder = new OrderByBuilder();
            $processedSql .= " " . $orderByBuilder->build($parsedSql['0']['ORDER']);
        }

        return [$processedSql, $sqlCount ?? $processedSqlCount, $columnDescriptors];
    }

    /**
     * @param mixed[] $parsed
     */
    private function buildUnion(array $parsed): string
    {
        $selectBuilder = new SelectStatementBuilder();

        return implode(' UNION ', array_map(function ($clause) use ($selectBuilder) {
            return '(' . $selectBuilder->build($clause) . ')';
        }, $parsed['UNION']));
    }

    /**
     * @param mixed[] $parsedSql
     * @param null|string $sqlCount
     * @return mixed[] An array of 3 elements: [$processedSql, $processedSqlCount, $columnDescriptors]
     */
    private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array
    {
        // 1: let's reformat the SELECT and construct our columns
        list($select, $countSelect, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
        $generator = new PHPSQLCreator();
        $parsedSql['SELECT'] = $select;
        $processedSql = $generator->create($parsedSql);

        // 2: let's compute the count query if needed
        if ($sqlCount === null) {
            $parsedCountSql = $parsedSql;
            $parsedCountSql['SELECT'] = $countSelect;
            $parsedSqlCount = $this->generateParsedSqlCount($parsedCountSql);
            $processedSqlCount = $generator->create($parsedSqlCount);
        } else {
            $processedSqlCount = $sqlCount;
        }

        return [$processedSql, $processedSqlCount, $columnDescriptors];
    }

    /**
     * @param mixed[] $baseSelect
     * @return mixed[] An array of 2 elements: [$formattedSelect, $columnDescriptors]
     * @throws TDBMException
     * @throws \Doctrine\DBAL\Schema\SchemaException
     */
    private function formatSelect(array $baseSelect): array
    {
        $relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
        $tableGroup = $this->getTableGroupName($relatedTables);

        $connection = $this->tdbmService->getConnection();
        $formattedSelect = [];
        $formattedCountSelect = [];
        $columnDescriptors = [];
        $fetchedTables = [];

        foreach ($baseSelect as $entry) {
            if ($entry['expr_type'] !== 'colref') {
                $formattedSelect[] = $entry;
                $formattedCountSelect[] = $entry;
                continue;
            }

            $noQuotes = $entry['no_quotes'];
            if ($noQuotes['delim'] !== '.' || count($noQuotes['parts']) !== 2) {
                $formattedSelect[] = $entry;
                $formattedCountSelect[] = $entry;
                continue;
            }

            $tableName = $noQuotes['parts'][0];
            if (!in_array($tableName, $relatedTables)) {
                $formattedSelect[] = $entry;
                $formattedCountSelect[] = $entry;
                continue;
            }

            $columnName = $noQuotes['parts'][1];
            if ($columnName !== '*') {
                $formattedSelect[] = $entry;
                $formattedCountSelect[] = $entry;
                continue;
            }

            $table = $this->schema->getTable($tableName);
            $pkColumns = $table->getPrimaryKeyColumns();
            foreach ($table->getColumns() as $column) {
                $columnName = $column->getName();
                $alias = AbstractQueryFactory::getColumnAlias($tableName, $columnName);
                $astColumn = [
                    'expr_type' => 'colref',
                    'base_expr' => $connection->quoteIdentifier($tableName) . '.' . $connection->quoteIdentifier($columnName),
                    'no_quotes' => [
                        'delim' => '.',
                        'parts' => [
                            $tableName,
                            $columnName
                        ]
                    ],
                    'alias' => [
                        'as' => true,
                        'name' => $connection->quoteIdentifier($alias),
                    ]
                ];
                $formattedSelect[] = $astColumn;
                if (in_array($columnName, $pkColumns)) {
                    $formattedCountSelect[] = $astColumn;
                }
                $columnDescriptors[$alias] = [
                    'as' => $alias,
                    'table' => $tableName,
                    'column' => $columnName,
                    'type' => $column->getType(),
                    'tableGroup' => $tableGroup,
                ];
            }
            $fetchedTables[] = $tableName;
        }

        $missingTables = array_diff($relatedTables, $fetchedTables);
        if (!empty($missingTables)) {
            throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement');
        }

        for ($i = 0; $i < count($formattedSelect) - 1; $i++) {
            if (!isset($formattedSelect[$i]['delim'])) {
                $formattedSelect[$i]['delim'] = ',';
            }
        }

        for ($i = 0; $i < count($formattedCountSelect) - 1; $i++) {
            if (!isset($formattedCountSelect[$i]['delim'])) {
                $formattedCountSelect[$i]['delim'] = ',';
            }
        }
        return [$formattedSelect, $formattedCountSelect, $columnDescriptors];
    }

    /**
     * @param mixed[] $parsedSql
     * @return mixed[]
     */
    private function generateParsedSqlCount(array $parsedSql): array
    {
        if (isset($parsedSql['ORDER'])) {
            unset($parsedSql['ORDER']);
        }

        if (!isset($parsedSql['GROUP'])) {
            // most simple case:no GROUP BY in query
            return $this->generateSimpleSqlCount($parsedSql);
        } elseif (!isset($parsedSql['HAVING'])) {
            // GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns
            return $this->generateGroupedSqlCount($parsedSql);
        } else {
            // GROUP BY with a HAVING statement: we'll have to wrap the query
            return $this->generateWrappedSqlCount($parsedSql);
        }
    }

    /**
     * @param mixed[] $parsedSql The AST of the SQL query
     * @return mixed[] An AST representing the matching COUNT query
     */
    private function generateSimpleSqlCount(array $parsedSql): array
    {
        // If the query is a DISTINCT, we need to deal with the count.


        // We need to count on the same columns: COUNT(DISTINCT country.id, country.label) ....
        // but we need to remove the "alias" bit.

        if ($this->isDistinctQuery($parsedSql)) {
            // Only MySQL can do DISTINCT counts.
            // Other databases should wrap the query
            if (!$this->tdbmService->getConnection()->getSchemaManager()->getDatabasePlatform() instanceof MySqlPlatform) {
                return $this->generateWrappedSqlCount($parsedSql);
            }

            $countSubExpr = array_map(function (array $item) {
                unset($item['alias']);
                return $item;
            }, $parsedSql['SELECT']);
        } else {
            $countSubExpr = [
                [
                'expr_type' => 'colref',
                'base_expr' => '*',
                'sub_tree' => false
                ]
            ];
        }

        $parsedSql['SELECT'] = [[
            'expr_type' => 'aggregate_function',
            'alias' => [
                'as' => true,
                'name' => 'cnt',
            ],
            'base_expr' => 'COUNT',
            'sub_tree' => $countSubExpr,
            'delim' => false,
        ]];

        return $parsedSql;
    }

    /**
     * @param mixed[] $parsedSql AST to analyze
     * @return bool
     */
    private function isDistinctQuery(array $parsedSql): bool
    {
        foreach ($parsedSql['SELECT'] as $item) {
            if ($item['expr_type'] === 'reserved' && $item['base_expr'] === 'DISTINCT') {
                return true;
            }
        }
        return false;
    }

    /**
     * @param mixed[] $parsedSql The AST of the SQL query
     * @return mixed[] An AST representing the matching COUNT query
     */
    private function generateGroupedSqlCount(array $parsedSql): array
    {
        $group = $parsedSql['GROUP'];
        unset($parsedSql['GROUP']);

        // Count(DISTINCT ...) on multiple columns is only valid in MySQL (unsupported on Pgsql or Oracle). For those, we need to do a subquery.
        if (count($group) === 1 || $this->tdbmService->getConnection()->getDatabasePlatform() instanceof MySqlPlatform) {
            $parsedSql['SELECT'] = [[
                'expr_type' => 'aggregate_function',
                'alias' => [
                    'as' => true,
                    'name' => 'cnt',
                ],
                'base_expr' => 'COUNT',
                'sub_tree' => array_merge([[
                    'expr_type' => 'reserved',
                    'base_expr' => 'DISTINCT',
                    'delim' => ','
                ]], $group),
                'delim' => false,
            ]];
        } else {
            $innerColumns = [[
                'expr_type' => 'reserved',
                'base_expr' => 'DISTINCT',
                'delim' => ' '
            ]];
            foreach ($group as $item) {
                $item['delim'] = ',';
                $innerColumns[] = $item;
            }
            $innerColumns[count($innerColumns)-1]['delim'] = false;
            $parsedSql['SELECT'] = $innerColumns;

            $parsedSql = [
                'SELECT' =>
                    [
                        0 =>
                            [
                                'expr_type' => 'aggregate_function',
                                'alias' =>
                                    [
                                        'as' => true,
                                        'name' => 'cnt',
                                        'base_expr' => 'AS cnt',
                                        'no_quotes' =>
                                            [
                                                'delim' => false,
                                                'parts' =>
                                                    [
                                                        0 => 'cnt',
                                                    ],
                                            ],
                                    ],
                                'base_expr' => 'COUNT',
                                'sub_tree' =>
                                    [
                                        0 =>
                                            [
                                                'expr_type' => 'colref',
                                                'base_expr' => '*',
                                                'sub_tree' => false,
                                            ],
                                    ],
                                'delim' => false,
                            ],
                    ],
                'FROM' =>
                    [
                        0 =>
                            [
                                'expr_type' => 'subquery',
                                'alias' =>
                                    [
                                        'as' => false,
                                        'name' => 'subquery',
                                        'no_quotes' =>
                                            [
                                                'delim' => false,
                                                'parts' =>
                                                    [
                                                        0 => 'subquery',
                                                    ],
                                            ],
                                        'base_expr' => 'subquery',
                                    ],
                                'hints' => false,
                                'join_type' => 'JOIN',
                                'ref_type' => false,
                                'ref_clause' => false,
                                //'base_expr' => 'SELECT id FROM country',
                                'sub_tree' => $parsedSql
                            ],
                    ],
                ];
        }
        return $parsedSql;
    }

    /**
     * @param mixed[] $parsedSql The AST of the SQL query
     * @return mixed[] An AST representing the matching COUNT query
     */
    private function generateWrappedSqlCount(array $parsedSql): array
    {
        return [
            'SELECT' => [[
                'expr_type' => 'aggregate_function',
                'alias' => [
                    'as' => true,
                    'name' => 'cnt',
                ],
                'base_expr' => 'COUNT',
                'sub_tree' => [
                    [
                        'expr_type' => 'colref',
                        'base_expr' => '*',
                        'sub_tree' => false
                    ]
                ],
                'delim' => false,
            ]],
            'FROM' => [[
                'expr_type' => 'subquery',
                'alias' => [
                    'as' => true,
                    'name' => '____query'
                ],
                'sub_tree' => $parsedSql,
            ]]
        ];
    }

    /**
     * @param string[] $relatedTables
     * @return string
     */
    protected function getTableGroupName(array $relatedTables): string
    {
        sort($relatedTables);
        return implode('_``_', $relatedTables);
    }

    /**
     * Returns a sub-query to be used in another query.
     * A sub-query is similar to a query except it returns only the primary keys of the table (to be used as filters)
     *
     * @return string
     */
    public function getMagicSqlSubQuery(): string
    {
        throw new TDBMException('Using resultset generated from findFromRawSql as subqueries is unsupported for now.');
    }

    /**
     * @return array<int, array{table: string, column: string}> An array of column descriptors.
     */
    public function getSubQueryColumnDescriptors(): array
    {
        throw new TDBMException('Using resultset generated from findFromRawSql as subqueries is unsupported for now.');
    }
}


1			<?php
2
3			declare(strict_types=1);
4
5			namespace TheCodingMachine\TDBM\QueryFactory;
6
7			use Doctrine\DBAL\Platforms\MySqlPlatform;
8			use Doctrine\DBAL\Schema\Schema;
9			use PHPSQLParser\builders\OrderByBuilder;
10			use PHPSQLParser\builders\SelectStatementBuilder;
11			use TheCodingMachine\TDBM\TDBMException;
12			use TheCodingMachine\TDBM\TDBMService;
13			use PHPSQLParser\PHPSQLCreator;
14			use PHPSQLParser\PHPSQLParser;
15
16			use function array_merge;
17
18			/**
19			* This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
20			*/
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
169			{
170			// 1: let's reformat the SELECT and construct our columns
171			list($select, $countSelect, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
172			$generator = new PHPSQLCreator();
173			$parsedSql['SELECT'] = $select;
174			$processedSql = $generator->create($parsedSql);
175
176			// 2: let's compute the count query if needed
177			if ($sqlCount === null) {
178			$parsedCountSql = $parsedSql;
179			$parsedCountSql['SELECT'] = $countSelect;
180			$parsedSqlCount = $this->generateParsedSqlCount($parsedCountSql);
181			$processedSqlCount = $generator->create($parsedSqlCount);
182			} else {
183			$processedSqlCount = $sqlCount;
184			}
185
186			return [$processedSql, $processedSqlCount, $columnDescriptors];
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 (in_array($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()->getSchemaManager()->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
376			{
377			$group = $parsedSql['GROUP'];
378			unset($parsedSql['GROUP']);
379
380			// Count(DISTINCT ...) on multiple columns is only valid in MySQL (unsupported on Pgsql or Oracle). For those, we need to do a subquery.
381			if (count($group) === 1 \|\| $this->tdbmService->getConnection()->getDatabasePlatform() instanceof MySqlPlatform) {
382			$parsedSql['SELECT'] = [[
383			'expr_type' => 'aggregate_function',
384			'alias' => [
385			'as' => true,
386			'name' => 'cnt',
387			],
388			'base_expr' => 'COUNT',
389			'sub_tree' => array_merge([[
390			'expr_type' => 'reserved',
391			'base_expr' => 'DISTINCT',
392			'delim' => ','
393			]], $group),
394			'delim' => false,
395			]];
396			} else {
397			$innerColumns = [[
398			'expr_type' => 'reserved',
399			'base_expr' => 'DISTINCT',
400			'delim' => ' '
401			]];
402			foreach ($group as $item) {
403			$item['delim'] = ',';
404			$innerColumns[] = $item;
405			}
406			$innerColumns[count($innerColumns)-1]['delim'] = false;
407			$parsedSql['SELECT'] = $innerColumns;
408
409			$parsedSql = [
410			'SELECT' =>
411			[
412			0 =>
413			[
414			'expr_type' => 'aggregate_function',
415			'alias' =>
416			[
417			'as' => true,
418			'name' => 'cnt',
419			'base_expr' => 'AS cnt',
420			'no_quotes' =>
421			[
422			'delim' => false,
423			'parts' =>
424			[
425			0 => 'cnt',
426			],
427			],
428			],
429			'base_expr' => 'COUNT',
430			'sub_tree' =>
431			[
432			0 =>
433			[
434			'expr_type' => 'colref',
435			'base_expr' => '*',
436			'sub_tree' => false,
437			],
438			],
439			'delim' => false,
440			],
441			],
442			'FROM' =>
443			[
444			0 =>
445			[
446			'expr_type' => 'subquery',
447			'alias' =>
448			[
449			'as' => false,
450			'name' => 'subquery',
451			'no_quotes' =>
452			[
453			'delim' => false,
454			'parts' =>
455			[
456			0 => 'subquery',
457			],
458			],
459			'base_expr' => 'subquery',
460			],
461			'hints' => false,
462			'join_type' => 'JOIN',
463			'ref_type' => false,
464			'ref_clause' => false,
465			//'base_expr' => 'SELECT id FROM country',
466			'sub_tree' => $parsedSql
467			],
468			],
469			];
470			}
471			return $parsedSql;
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
513			{
514			sort($relatedTables);
515			return implode('_``_', $relatedTables);
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			{
526			throw new TDBMException('Using resultset generated from findFromRawSql as subqueries is unsupported for now.');
527			}
528
529			/**
530			* @return array<int, array{table: string, column: string}> An array of column descriptors.
531			*/
532			public function getSubQueryColumnDescriptors(): array
533			{
534			throw new TDBMException('Using resultset generated from findFromRawSql as subqueries is unsupported for now.');
535			}
536			}
537

thecodingmachine / tdbm

Push — 5.3 ( 77be5c...7b5354 )

FindObjectsFromRawSqlQueryFactory::buildUnion() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like