FindObjectsFromRawSqlQueryFactory::generateSimpleSqlCount() - Code Metrics - Inspection of "Fixing count issue on DISTINCT queries with findFr..." - thecodingmachine/tdbm - Measure and Improve Code Quality continuously with Scrutinizer

Passed

Pull Request — master (#67)

by David

created 2018-02-07 12:03 UTC

generateSimpleSqlCount() B

↳ Parent: FindObjectsFromRawSqlQueryFactory

Complexity

Conditions	2
Paths	2

Size

Total Lines	36
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
cc	2
eloc	20
nc	2
nop	1
dl	0
loc	36
rs	8.8571
c	0
b	0
f	0

<?php

namespace TheCodingMachine\TDBM\QueryFactory;

use Doctrine\DBAL\Schema\Schema;
use TheCodingMachine\TDBM\TDBMException;
use TheCodingMachine\TDBM\TDBMService;
use PHPSQLParser\PHPSQLCreator;
use PHPSQLParser\PHPSQLParser;

/**
 * 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)
    {
        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;
    }

    private function compute(string $sql, ?string $sqlCount)
    {
        $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];

    }

    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();
            $parsedSql = $parser->parse($selectProcessedSql);


            $parsedSqlList[] = $parsedSql;
        }

        // 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();

        $processedSql = $generator->create($query);
        $processedSqlCount = $generator->create($countQuery);

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

    /**
     * @param array $parsedSql
     * @param null|string $sqlCount
     * @return mixed[]
     */
    private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array
    {
        // 1: let's reformat the SELECT and construct our columns
        list($select, $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) {
            $parsedSqlCount = $this->generateParsedSqlCount($parsedSql);
            $processedSqlCount = $generator->create($parsedSqlCount);
        } else {
            $processedSqlCount = $sqlCount;
        }

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

    private function formatSelect($baseSelect)
    {
        $relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
        $tableGroup = $this->getTableGroupName($relatedTables);

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

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

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

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

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

            $table = $this->schema->getTable($tableName);
            foreach ($table->getColumns() as $column) {
                $columnName = $column->getName();
                $alias = "{$tableName}____{$columnName}";
                $formattedSelect[] = [
                    'expr_type' => 'colref',
                    'base_expr' => $connection->quoteIdentifier($tableName).'.'.$connection->quoteIdentifier($columnName),
                    'no_quotes' => [
                        'delim' => '.',
                        'parts' => [
                            $tableName,
                            $columnName
                        ]
                    ],
                    'alias' => [
                        'as' => true,
                        'name' => $alias,
                    ]
                ];

                $columnDescritors[$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'] = ',';
            }
        }
        return [$formattedSelect, $columnDescritors];
    }

    private function generateParsedSqlCount($parsedSql)
    {
        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);
        }
    }

    private function generateSimpleSqlCount($parsedSql)
    {
        // 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)) {
            $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;
    }

    private function isDistinctQuery(array $parsedSql): bool
    {
        foreach ($parsedSql['SELECT'] as $item) {
            if ($item['expr_type'] === 'reserved' && $item['base_expr'] === 'DISTINCT') {
                return true;
            }
        }
        return false;
    }

    private function generateGroupedSqlCount($parsedSql)
    {
        $group = $parsedSql['GROUP'];
        unset($parsedSql['GROUP']);
        $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,
        ]];
        return $parsedSql;
    }

    private function generateWrappedSqlCount($parsedSql)
    {
        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,
            ]]
        ];
    }

    protected function getTableGroupName(array $relatedTables)
    {
        sort($relatedTables);
        return implode('_``_', $relatedTables);
    }
}


1			<?php
2
3			namespace TheCodingMachine\TDBM\QueryFactory;
4
5			use Doctrine\DBAL\Schema\Schema;
6			use TheCodingMachine\TDBM\TDBMException;
7			use TheCodingMachine\TDBM\TDBMService;
8			use PHPSQLParser\PHPSQLCreator;
9			use PHPSQLParser\PHPSQLParser;
10
11			/**
12			* This class is in charge of formatting the SQL passed to findObjectsFromRawSql method.
13			*/
14			class FindObjectsFromRawSqlQueryFactory implements QueryFactory
15			{
16			/**
17			* @var array[]
18			*/
19			protected $columnDescriptors;
20			/**
21			* @var Schema
22			*/
23			private $schema;
24			/**
25			* @var string
26			*/
27			private $processedSql;
28			/**
29			* @var string
30			*/
31			private $processedSqlCount;
32			/**
33			* @var TDBMService
34			*/
35			private $tdbmService;
36			/**
37			* @var string
38			*/
39			private $mainTable;
40
41			/**
42			* FindObjectsFromRawSqlQueryFactory constructor.
43			* @param TDBMService $tdbmService
44			* @param Schema $schema
45			* @param string $mainTable
46			* @param string $sql
47			* @param string $sqlCount
48			*/
49			public function __construct(TDBMService $tdbmService, Schema $schema, string $mainTable, string $sql, string $sqlCount = null)
50			{
51			$this->tdbmService = $tdbmService;
52			$this->schema = $schema;
53			$this->mainTable = $mainTable;
54
55			[$this->processedSql, $this->processedSqlCount, $this->columnDescriptors] = $this->compute($sql, $sqlCount);
56			}
57
58			public function sort($orderBy)
59			{
60			throw new TDBMException('sort not supported for raw sql queries');
61			}
62
63			public function getMagicSql(): string
64			{
65			return $this->processedSql;
66			}
67
68			public function getMagicSqlCount(): string
69			{
70			return $this->processedSqlCount;
71			}
72
73			public function getColumnDescriptors(): array
74			{
75			return $this->columnDescriptors;
76			}
77
78			private function compute(string $sql, ?string $sqlCount)
79			{
80			$parser = new PHPSQLParser();
81			$parsedSql = $parser->parse($sql);
82
83			if (isset($parsedSql['SELECT'])) {
84			[$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedSelectQuery($parsedSql, $sqlCount);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSql` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$columnDescriptors` does not exist. Did you forget to declare it? This check marks access to variables or properties that have not been declared yet. While PHP has no explicit notion of declaring a variable, accessing it before a value is assigned to it is most likely a bug. Loading history...
85			} elseif (isset($parsedSql['UNION'])) {
86			[$processedSql, $processedSqlCount, $columnDescriptors] = $this->processParsedUnionQuery($parsedSql, $sqlCount);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
87			} else {
88			throw new TDBMException('Unable to analyze query "'.$sql.'"');
89			}
90
91			return [$processedSql, $processedSqlCount, $columnDescriptors];
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$processedSqlCount` does not exist. Did you mean `$sqlCount`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
92			}
93
94			private function processParsedUnionQuery(array $parsedSql, ?string $sqlCount): array
95			{
96			$selects = $parsedSql['UNION'];
97
98			$parsedSqlList = [];
99			$columnDescriptors = [];
100
101			foreach ($selects as $select) {
102			[$selectProcessedSql, $selectProcessedCountSql, $columnDescriptors] = $this->processParsedSelectQuery($select, '');
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history... Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedCountSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
103
104			// Let's reparse the returned SQL (not the most efficient way of doing things)
105			$parser = new PHPSQLParser();
106			$parsedSql = $parser->parse($selectProcessedSql);
			0 ignored issues – show Bug introduced 2017-11-22 10:57 UTC by Report Bug Copy Issue Report The variable `$selectProcessedSql` does not exist. Did you mean `$select`? This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name. The variable may have been renamed without also renaming all references. Loading history...
107
108			$parsedSqlList[] = $parsedSql;
109			}
110
111			// Let's rebuild the UNION query
112			$query = ['UNION' => $parsedSqlList];
113
114			// The count is the SUM of the count of the UNIONs
115			$countQuery = $this->generateWrappedSqlCount($query);
116
117			$generator = new PHPSQLCreator();
118
119			$processedSql = $generator->create($query);
120			$processedSqlCount = $generator->create($countQuery);
121
122			return [$processedSql, $sqlCount ?? $processedSqlCount, $columnDescriptors];
123			}
124
125			/**
126			* @param array $parsedSql
127			* @param null\|string $sqlCount
128			* @return mixed[]
129			*/
130			private function processParsedSelectQuery(array $parsedSql, ?string $sqlCount): array
131			{
132			// 1: let's reformat the SELECT and construct our columns
133			list($select, $columnDescriptors) = $this->formatSelect($parsedSql['SELECT']);
134			$generator = new PHPSQLCreator();
135			$parsedSql['SELECT'] = $select;
136			$processedSql = $generator->create($parsedSql);
137
138			// 2: let's compute the count query if needed
139			if ($sqlCount === null) {
140			$parsedSqlCount = $this->generateParsedSqlCount($parsedSql);
141			$processedSqlCount = $generator->create($parsedSqlCount);
142			} else {
143			$processedSqlCount = $sqlCount;
144			}
145
146			return [$processedSql, $processedSqlCount, $columnDescriptors];
147			}
148
149			private function formatSelect($baseSelect)
150			{
151			$relatedTables = $this->tdbmService->_getRelatedTablesByInheritance($this->mainTable);
152			$tableGroup = $this->getTableGroupName($relatedTables);
153
154			$connection = $this->tdbmService->getConnection();
155			$formattedSelect = [];
156			$columnDescritors = [];
157			$fetchedTables = [];
158
159			foreach ($baseSelect as $entry) {
160			if ($entry['expr_type'] !== 'colref') {
161			$formattedSelect[] = $entry;
162			continue;
163			}
164
165			$noQuotes = $entry['no_quotes'];
166			if ($noQuotes['delim'] !== '.' \|\| count($noQuotes['parts']) !== 2) {
167			$formattedSelect[] = $entry;
168			continue;
169			}
170
171			$tableName = $noQuotes['parts'][0];
172			if (!in_array($tableName, $relatedTables)) {
173			$formattedSelect[] = $entry;
174			continue;
175			}
176
177			$columnName = $noQuotes['parts'][1];
178			if ($columnName !== '*') {
179			$formattedSelect[] = $entry;
180			continue;
181			}
182
183			$table = $this->schema->getTable($tableName);
184			foreach ($table->getColumns() as $column) {
185			$columnName = $column->getName();
186			$alias = "{$tableName}____{$columnName}";
187			$formattedSelect[] = [
188			'expr_type' => 'colref',
189			'base_expr' => $connection->quoteIdentifier($tableName).'.'.$connection->quoteIdentifier($columnName),
190			'no_quotes' => [
191			'delim' => '.',
192			'parts' => [
193			$tableName,
194			$columnName
195			]
196			],
197			'alias' => [
198			'as' => true,
199			'name' => $alias,
200			]
201			];
202
203			$columnDescritors[$alias] = [
204			'as' => $alias,
205			'table' => $tableName,
206			'column' => $columnName,
207			'type' => $column->getType(),
208			'tableGroup' => $tableGroup,
209			];
210			}
211			$fetchedTables[] = $tableName;
212			}
213
214			$missingTables = array_diff($relatedTables, $fetchedTables);
215			if (!empty($missingTables)) {
216			throw new TDBMException('Missing tables '.implode(', ', $missingTables).' in SELECT statement');
217			}
218
219			for ($i = 0; $i < count($formattedSelect) - 1; $i++) {
220			if (!isset($formattedSelect[$i]['delim'])) {
221			$formattedSelect[$i]['delim'] = ',';
222			}
223			}
224			return [$formattedSelect, $columnDescritors];
225			}
226
227			private function generateParsedSqlCount($parsedSql)
228			{
229			if (isset($parsedSql['ORDER'])) {
230			unset($parsedSql['ORDER']);
231			}
232
233			if (!isset($parsedSql['GROUP'])) {
234			// most simple case:no GROUP BY in query
235			return $this->generateSimpleSqlCount($parsedSql);
236			} elseif (!isset($parsedSql['HAVING'])) {
237			// GROUP BY without HAVING statement: let's COUNT the DISTINCT grouped columns
238			return $this->generateGroupedSqlCount($parsedSql);
239			} else {
240			// GROUP BY with a HAVING statement: we'll have to wrap the query
241			return $this->generateWrappedSqlCount($parsedSql);
242			}
243			}
244
245			private function generateSimpleSqlCount($parsedSql)
246			{
247			// If the query is a DISTINCT, we need to deal with the count.
248
249
250			// We need to count on the same columns: COUNT(DISTINCT country.id, country.label) ....
251			// but we need to remove the "alias" bit.
252
253			if ($this->isDistinctQuery($parsedSql)) {
254			$countSubExpr = array_map(function(array $item) {
255			unset($item['alias']);
256			return $item;
257			}, $parsedSql['SELECT']);
258			} else {
259			$countSubExpr = [
260			[
261			'expr_type' => 'colref',
262			'base_expr' => '*',
263			'sub_tree' => false
264			]
265			];
266			}
267
268			$parsedSql['SELECT'] = [[
269			'expr_type' => 'aggregate_function',
270			'alias' => [
271			'as' => true,
272			'name' => 'cnt',
273			],
274			'base_expr' => 'COUNT',
275			'sub_tree' => $countSubExpr,
276			'delim' => false,
277			]];
278
279			return $parsedSql;
280			}
281
282			private function isDistinctQuery(array $parsedSql): bool
283			{
284			foreach ($parsedSql['SELECT'] as $item) {
285			if ($item['expr_type'] === 'reserved' && $item['base_expr'] === 'DISTINCT') {
286			return true;
287			}
288			}
289			return false;
290			}
291
292			private function generateGroupedSqlCount($parsedSql)
293			{
294			$group = $parsedSql['GROUP'];
295			unset($parsedSql['GROUP']);
296			$parsedSql['SELECT'] = [[
297			'expr_type' => 'aggregate_function',
298			'alias' => [
299			'as' => true,
300			'name' => 'cnt',
301			],
302			'base_expr' => 'COUNT',
303			'sub_tree' => array_merge([[
304			'expr_type' => 'reserved',
305			'base_expr' => 'DISTINCT',
306			'delim' => ','
307			]], $group),
308			'delim' => false,
309			]];
310			return $parsedSql;
311			}
312
313			private function generateWrappedSqlCount($parsedSql)
314			{
315			return [
316			'SELECT' => [[
317			'expr_type' => 'aggregate_function',
318			'alias' => [
319			'as' => true,
320			'name' => 'cnt',
321			],
322			'base_expr' => 'COUNT',
323			'sub_tree' => [
324			[
325			'expr_type' => 'colref',
326			'base_expr' => '*',
327			'sub_tree' => false
328			]
329			],
330			'delim' => false,
331			]],
332			'FROM' => [[
333			'expr_type' => 'subquery',
334			'alias' => [
335			'as' => true,
336			'name' => '____query'
337			],
338			'sub_tree' => $parsedSql,
339			]]
340			];
341			}
342
343			protected function getTableGroupName(array $relatedTables)
344			{
345			sort($relatedTables);
346			return implode('_``_', $relatedTables);
347			}
348			}
349

thecodingmachine / tdbm

Pull Request — master (#67)

generateSimpleSqlCount() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like