ProxyQuery::getFixedQueryBuilder() - Code Metrics - Inspection of "Fix sorting by multiple columns in PostgreSQL and..." - sonata-project/SonataDoctrineORMAdminBundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#728)

unknown

created 2017-09-24 08:30 UTC

ProxyQuery::getFixedQueryBuilder() F

↳ Parent: ProxyQuery

Complexity

Conditions	12
Paths	270

Size

Total Lines	77
Code Lines	43

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	77
rs	3.9257
c	0
b	0
f	0
cc	12
eloc	43
nc	270
nop	1

How to fix Long Method Complexity

<?php

/*
 * This file is part of the Sonata Project package.
 *
 * (c) Thomas Rabaix <[email protected]>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace Sonata\DoctrineORMAdminBundle\Datagrid;

use Doctrine\Common\Collections\Criteria;
use Doctrine\DBAL\Types\Type;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;

/**
 * This class try to unify the query usage with Doctrine.
 */
class ProxyQuery implements ProxyQueryInterface
{
    /**
     * @var QueryBuilder
     */
    protected $queryBuilder;

    /**
     * @var string
     */
    protected $sortBy;

    /**
     * @var mixed
     */
    protected $sortOrder;

    /**
     * @var int
     */
    protected $uniqueParameterId;

    /**
     * @var string[]
     */
    protected $entityJoinAliases;

    /**
     * @param QueryBuilder $queryBuilder
     */
    public function __construct($queryBuilder)
    {
        $this->queryBuilder = $queryBuilder;
        $this->uniqueParameterId = 0;
        $this->entityJoinAliases = array();
    }

    /**
     * {@inheritdoc}
     */
    public function __call($name, $args)
    {
        return call_user_func_array(array($this->queryBuilder, $name), $args);
    }

    /**
     * {@inheritdoc}
     */
    public function __get($name)
    {
        return $this->queryBuilder->$name;
    }

    /**
     * {@inheritdoc}
     */
    public function __clone()
    {
        $this->queryBuilder = clone $this->queryBuilder;
    }

    /**
     * {@inheritdoc}
     */
    public function execute(array $params = array(), $hydrationMode = null)
    {
        // always clone the original queryBuilder
        $queryBuilder = clone $this->queryBuilder;

        $rootAlias = current($queryBuilder->getRootAliases());

        // todo : check how doctrine behave, potential SQL injection here ...
        if ($this->getSortBy()) {
            $sortBy = $this->getSortBy();
            if (strpos($sortBy, '.') === false) { // add the current alias
                $sortBy = $rootAlias.'.'.$sortBy;
            }
            $queryBuilder->addOrderBy($sortBy, $this->getSortOrder());
        } else {
            $queryBuilder->resetDQLPart('orderBy');
        }

        /* By default, always add a sort on the identifier fields of the first
         * used entity in the query, because RDBMS do not guarantee a
         * particular order when no ORDER BY clause is specified, or when
         * the field used for sorting is not unique.
         */

        $identifierFields = $queryBuilder
            ->getEntityManager()
            ->getMetadataFactory()
            ->getMetadataFor(current($queryBuilder->getRootEntities()))
            ->getIdentifierFieldNames();

        $existingOrders = array();
        /** @var Query\Expr\OrderBy $order */
        foreach ($queryBuilder->getDQLPart('orderBy') as $order) {
            foreach ($order->getParts() as $part) {
                $existingOrders[] = trim(str_replace(array(Criteria::DESC, Criteria::ASC), '', $part));
            }
        }

        foreach ($identifierFields as $identifierField) {
            $order = $rootAlias.'.'.$identifierField;
            if (!in_array($order, $existingOrders)) {
                $queryBuilder->addOrderBy(
                    $order,
                    $this->getSortOrder() // reusing the sort order is the most natural way to go
                );
            }
        }

        return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
    }

    /**
     * {@inheritdoc}
     */
    public function setSortBy($parentAssociationMappings, $fieldMapping)
    {
        $alias = $this->entityJoin($parentAssociationMappings);
        $this->sortBy = $alias.'.'.$fieldMapping['fieldName'];

        return $this;
    }

    /**
     * {@inheritdoc}
     */
    public function getSortBy()
    {
        return $this->sortBy;
    }

    /**
     * {@inheritdoc}
     */
    public function setSortOrder($sortOrder)
    {
        $this->sortOrder = $sortOrder;

        return $this;
    }

    /**
     * {@inheritdoc}
     */
    public function getSortOrder()
    {
        return $this->sortOrder;
    }

    /**
     * {@inheritdoc}
     */
    public function getSingleScalarResult()
    {
        $query = $this->queryBuilder->getQuery();

        return $query->getSingleScalarResult();
    }

    /**
     * @return mixed
     */
    public function getQueryBuilder()
    {
        return $this->queryBuilder;
    }

    /**
     * {@inheritdoc}
     */
    public function setFirstResult($firstResult)
    {
        $this->queryBuilder->setFirstResult($firstResult);

        return $this;
    }

    /**
     * {@inheritdoc}
     */
    public function getFirstResult()
    {
        return $this->queryBuilder->getFirstResult();
    }

    /**
     * {@inheritdoc}
     */
    public function setMaxResults($maxResults)
    {
        $this->queryBuilder->setMaxResults($maxResults);

        return $this;
    }

    /**
     * {@inheritdoc}
     */
    public function getMaxResults()
    {
        return $this->queryBuilder->getMaxResults();
    }

    /**
     * {@inheritdoc}
     */
    public function getUniqueParameterId()
    {
        return $this->uniqueParameterId++;
    }

    /**
     * {@inheritdoc}
     */
    public function entityJoin(array $associationMappings)
    {
        $alias = $this->queryBuilder->getRootAlias();


        $newAlias = 's';

        $joinedEntities = $this->queryBuilder->getDQLPart('join');

        foreach ($associationMappings as $associationMapping) {
            // Do not add left join to already joined entities with custom query
            foreach ($joinedEntities as $joinExprList) {
                foreach ($joinExprList as $joinExpr) {
                    $newAliasTmp = $joinExpr->getAlias();

                    if (sprintf('%s.%s', $alias, $associationMapping['fieldName']) === $joinExpr->getJoin()) {
                        $this->entityJoinAliases[] = $newAliasTmp;
                        $alias = $newAliasTmp;

                        continue 3;
                    }
                }
            }

            $newAlias .= '_'.$associationMapping['fieldName'];
            if (!in_array($newAlias, $this->entityJoinAliases)) {
                $this->entityJoinAliases[] = $newAlias;
                $this->queryBuilder->leftJoin(sprintf('%s.%s', $alias, $associationMapping['fieldName']), $newAlias);
            }

            $alias = $newAlias;
        }

        return $alias;
    }

    /**
     * This method alters the query to return a clean set of object with a working
     * set of Object.
     *
     * @param QueryBuilder $queryBuilder
     *
     * @return QueryBuilder
     */
    protected function getFixedQueryBuilder(QueryBuilder $queryBuilder)
    {
        $queryBuilderId = clone $queryBuilder;
        $rootAlias = current($queryBuilderId->getRootAliases());

        // step 1 : retrieve the targeted class
        $from = $queryBuilderId->getDQLPart('from');
        $class = $from[0]->getFrom();
        $metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);

        // step 2 : retrieve identifier columns
        $idNames = $metadata->getIdentifierFieldNames();

        // step 3 : retrieve the different subjects ids
        $selects = array();
        $idxSelect = '';
        foreach ($idNames as $idName) {
            $select = sprintf('%s.%s', $rootAlias, $idName);
            // Put the ID select on this array to use it on results QB
            $selects[$idName] = $select;
            // Use IDENTITY if id is a relation too.
            // See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
            // Should work only with doctrine/orm: ~2.2
            $idSelect = $select;
            if ($metadata->hasAssociation($idName)) {
                $idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);
            }
            $idxSelect .= ($idxSelect !== '' ? ', ' : '').$idSelect;
        }
        $queryBuilderId->resetDQLPart('select');
        $queryBuilderId->add('select', 'DISTINCT '.$idxSelect);
function acceptsInteger($int) { }

$x = '123'; // string "123"

// Instead of
acceptsInteger($x);

// we recommend to use
acceptsInteger((integer) $x);

        // for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list
        /* Consider
            SELECT DISTINCT x FROM tab ORDER BY y;
        For any particular x-value in the table there might be many different y
        values.  Which one will you use to sort that x-value in the output?
        */
        // todo : check how doctrine behave, potential SQL injection here ...
        if ($this->getSortBy()) {
            $sortBy = $this->getSortBy();
            if (strpos($sortBy, '.') === false) { // add the current alias
                $sortBy = $rootAlias.'.'.$sortBy;
            }
            $sortBy .= ' AS __order_by';
            $queryBuilderId->addSelect($sortBy);
        }

        $this->addOrderedColumns($queryBuilderId);

        $results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
        $platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
        $idxMatrix = array();
        foreach ($results as $id) {
            foreach ($idNames as $idName) {
                // Convert ids to database value in case of custom type, if provided.
                $fieldType = $metadata->getTypeOfField($idName);
                $idxMatrix[$idName][] = $fieldType && Type::hasType($fieldType)
                    ? Type::getType($fieldType)->convertToDatabaseValue($id[$idName], $platform)
                    : $id[$idName];
            }
        }

        // step 4 : alter the query to match the targeted ids
        foreach ($idxMatrix as $idName => $idx) {
            if (count($idx) > 0) {
                $idxParamName = sprintf('%s_idx', $idName);
                $idxParamName = preg_replace('/[^\w]+/', '_', $idxParamName);
                $queryBuilder->andWhere(sprintf('%s IN (:%s)', $selects[$idName], $idxParamName));
                $queryBuilder->setParameter($idxParamName, $idx);
                $queryBuilder->setMaxResults(null);
                $queryBuilder->setFirstResult(null);
            }
        }

        return $queryBuilder;
    }

    private function addOrderedColumns(QueryBuilder $queryBuilder)
    {
        /* For each ORDER BY clause defined directly in the DQL parts of the query,
           we add an entry in the SELECT clause. */
        $dqlParts = $queryBuilder->getDqlParts();
        if ($dqlParts['orderBy'] && count($dqlParts['orderBy'])) {
            foreach ($dqlParts['orderBy'] as $part) {
                foreach ($part->getParts() as $orderBy) {
                    $queryBuilder->addSelect(preg_replace("/\s+(ASC|DESC)$/i", '', $orderBy));
                }
            }
        }
    }
}


1			<?php
2
3			/*
4			* This file is part of the Sonata Project package.
5			*
6			* (c) Thomas Rabaix <[email protected]>
7			*
8			* For the full copyright and license information, please view the LICENSE
9			* file that was distributed with this source code.
10			*/
11
12			namespace Sonata\DoctrineORMAdminBundle\Datagrid;
13
14			use Doctrine\Common\Collections\Criteria;
15			use Doctrine\DBAL\Types\Type;
16			use Doctrine\ORM\Query;
17			use Doctrine\ORM\QueryBuilder;
18			use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;
19
20			/**
21			* This class try to unify the query usage with Doctrine.
22			*/
23			class ProxyQuery implements ProxyQueryInterface
24			{
25			/**
26			* @var QueryBuilder
27			*/
28			protected $queryBuilder;
29
30			/**
31			* @var string
32			*/
33			protected $sortBy;
34
35			/**
36			* @var mixed
37			*/
38			protected $sortOrder;
39
40			/**
41			* @var int
42			*/
43			protected $uniqueParameterId;
44
45			/**
46			* @var string[]
47			*/
48			protected $entityJoinAliases;
49
50			/**
51			* @param QueryBuilder $queryBuilder
52			*/
53			public function __construct($queryBuilder)
54			{
55			$this->queryBuilder = $queryBuilder;
56			$this->uniqueParameterId = 0;
57			$this->entityJoinAliases = array();
58			}
59
60			/**
61			* {@inheritdoc}
62			*/
63			public function __call($name, $args)
64			{
65			return call_user_func_array(array($this->queryBuilder, $name), $args);
66			}
67
68			/**
69			* {@inheritdoc}
70			*/
71			public function __get($name)
72			{
73			return $this->queryBuilder->$name;
74			}
75
76			/**
77			* {@inheritdoc}
78			*/
79			public function __clone()
80			{
81			$this->queryBuilder = clone $this->queryBuilder;
82			}
83
84			/**
85			* {@inheritdoc}
86			*/
87			public function execute(array $params = array(), $hydrationMode = null)
88			{
89			// always clone the original queryBuilder
90			$queryBuilder = clone $this->queryBuilder;
91
92			$rootAlias = current($queryBuilder->getRootAliases());
93
94			// todo : check how doctrine behave, potential SQL injection here ...
95			if ($this->getSortBy()) {
96			$sortBy = $this->getSortBy();
97			if (strpos($sortBy, '.') === false) { // add the current alias
98			$sortBy = $rootAlias.'.'.$sortBy;
99			}
100			$queryBuilder->addOrderBy($sortBy, $this->getSortOrder());
101			} else {
102			$queryBuilder->resetDQLPart('orderBy');
103			}
104
105			/* By default, always add a sort on the identifier fields of the first
106			* used entity in the query, because RDBMS do not guarantee a
107			* particular order when no ORDER BY clause is specified, or when
108			* the field used for sorting is not unique.
109			*/
110
111			$identifierFields = $queryBuilder
112			->getEntityManager()
113			->getMetadataFactory()
114			->getMetadataFor(current($queryBuilder->getRootEntities()))
115			->getIdentifierFieldNames();
116
117			$existingOrders = array();
118			/** @var Query\Expr\OrderBy $order */
119			foreach ($queryBuilder->getDQLPart('orderBy') as $order) {
120			foreach ($order->getParts() as $part) {
121			$existingOrders[] = trim(str_replace(array(Criteria::DESC, Criteria::ASC), '', $part));
122			}
123			}
124
125			foreach ($identifierFields as $identifierField) {
126			$order = $rootAlias.'.'.$identifierField;
127			if (!in_array($order, $existingOrders)) {
128			$queryBuilder->addOrderBy(
129			$order,
130			$this->getSortOrder() // reusing the sort order is the most natural way to go
131			);
132			}
133			}
134
135			return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
136			}
137
138			/**
139			* {@inheritdoc}
140			*/
141			public function setSortBy($parentAssociationMappings, $fieldMapping)
142			{
143			$alias = $this->entityJoin($parentAssociationMappings);
144			$this->sortBy = $alias.'.'.$fieldMapping['fieldName'];
145
146			return $this;
147			}
148
149			/**
150			* {@inheritdoc}
151			*/
152			public function getSortBy()
153			{
154			return $this->sortBy;
155			}
156
157			/**
158			* {@inheritdoc}
159			*/
160			public function setSortOrder($sortOrder)
161			{
162			$this->sortOrder = $sortOrder;
163
164			return $this;
165			}
166
167			/**
168			* {@inheritdoc}
169			*/
170			public function getSortOrder()
171			{
172			return $this->sortOrder;
173			}
174
175			/**
176			* {@inheritdoc}
177			*/
178			public function getSingleScalarResult()
179			{
180			$query = $this->queryBuilder->getQuery();
181
182			return $query->getSingleScalarResult();
183			}
184
185			/**
186			* @return mixed
187			*/
188			public function getQueryBuilder()
189			{
190			return $this->queryBuilder;
191			}
192
193			/**
194			* {@inheritdoc}
195			*/
196			public function setFirstResult($firstResult)
197			{
198			$this->queryBuilder->setFirstResult($firstResult);
199
200			return $this;
201			}
202
203			/**
204			* {@inheritdoc}
205			*/
206			public function getFirstResult()
207			{
208			return $this->queryBuilder->getFirstResult();
209			}
210
211			/**
212			* {@inheritdoc}
213			*/
214			public function setMaxResults($maxResults)
215			{
216			$this->queryBuilder->setMaxResults($maxResults);
217
218			return $this;
219			}
220
221			/**
222			* {@inheritdoc}
223			*/
224			public function getMaxResults()
225			{
226			return $this->queryBuilder->getMaxResults();
227			}
228
229			/**
230			* {@inheritdoc}
231			*/
232			public function getUniqueParameterId()
233			{
234			return $this->uniqueParameterId++;
235			}
236
237			/**
238			* {@inheritdoc}
239			*/
240			public function entityJoin(array $associationMappings)
241			{
242			$alias = $this->queryBuilder->getRootAlias();
			0 ignored issues – show Deprecated Code introduced 2015-11-23 21:03 UTC by Report Bug Copy Issue Report The method `Doctrine\ORM\QueryBuilder::getRootAlias()` has been deprecated with message: Please use $qb->getRootAliases() instead. This method has been deprecated. The supplier of the class has supplied an explanatory message. The explanatory message should give you some clue as to whether and when the method will be removed from the class and what other method or class to use instead. Loading history...
243
244			$newAlias = 's';
245
246			$joinedEntities = $this->queryBuilder->getDQLPart('join');
247
248			foreach ($associationMappings as $associationMapping) {
249			// Do not add left join to already joined entities with custom query
250			foreach ($joinedEntities as $joinExprList) {
251			foreach ($joinExprList as $joinExpr) {
252			$newAliasTmp = $joinExpr->getAlias();
253
254			if (sprintf('%s.%s', $alias, $associationMapping['fieldName']) === $joinExpr->getJoin()) {
255			$this->entityJoinAliases[] = $newAliasTmp;
256			$alias = $newAliasTmp;
257
258			continue 3;
259			}
260			}
261			}
262
263			$newAlias .= '_'.$associationMapping['fieldName'];
264			if (!in_array($newAlias, $this->entityJoinAliases)) {
265			$this->entityJoinAliases[] = $newAlias;
266			$this->queryBuilder->leftJoin(sprintf('%s.%s', $alias, $associationMapping['fieldName']), $newAlias);
267			}
268
269			$alias = $newAlias;
270			}
271
272			return $alias;
273			}
274
275			/**
276			* This method alters the query to return a clean set of object with a working
277			* set of Object.
278			*
279			* @param QueryBuilder $queryBuilder
280			*
281			* @return QueryBuilder
282			*/
283			protected function getFixedQueryBuilder(QueryBuilder $queryBuilder)
284			{
285			$queryBuilderId = clone $queryBuilder;
286			$rootAlias = current($queryBuilderId->getRootAliases());
287
288			// step 1 : retrieve the targeted class
289			$from = $queryBuilderId->getDQLPart('from');
290			$class = $from[0]->getFrom();
291			$metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);
292
293			// step 2 : retrieve identifier columns
294			$idNames = $metadata->getIdentifierFieldNames();
295
296			// step 3 : retrieve the different subjects ids
297			$selects = array();
298			$idxSelect = '';
299			foreach ($idNames as $idName) {
300			$select = sprintf('%s.%s', $rootAlias, $idName);
301			// Put the ID select on this array to use it on results QB
302			$selects[$idName] = $select;
303			// Use IDENTITY if id is a relation too.
304			// See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
305			// Should work only with doctrine/orm: ~2.2
306			$idSelect = $select;
307			if ($metadata->hasAssociation($idName)) {
308			$idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);
309			}
310			$idxSelect .= ($idxSelect !== '' ? ', ' : '').$idSelect;
311			}
312			$queryBuilderId->resetDQLPart('select');
313			$queryBuilderId->add('select', 'DISTINCT '.$idxSelect);
			0 ignored issues – show Documentation introduced 2015-11-23 21:03 UTC by Report Bug Copy Issue Report `'DISTINCT ' . $idxSelect` is of type `string`, but the function expects a `object<Doctrine\ORM\Query\Expr\Base>`. It seems like the type of the argument is not accepted by the function/method which you are calling. In some cases, in particular if PHP’s automatic type-juggling kicks in this might be fine. In other cases, however this might be a bug. We suggest to add an explicit type cast like in the following example: function acceptsInteger($int) { } $x = '123'; // string "123" // Instead of acceptsInteger($x); // we recommend to use acceptsInteger((integer) $x); Loading history...
314
315			// for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list
316			/* Consider
317			SELECT DISTINCT x FROM tab ORDER BY y;
318			For any particular x-value in the table there might be many different y
319			values. Which one will you use to sort that x-value in the output?
320			*/
321			// todo : check how doctrine behave, potential SQL injection here ...
322			if ($this->getSortBy()) {
323			$sortBy = $this->getSortBy();
324			if (strpos($sortBy, '.') === false) { // add the current alias
325			$sortBy = $rootAlias.'.'.$sortBy;
326			}
327			$sortBy .= ' AS __order_by';
328			$queryBuilderId->addSelect($sortBy);
329			}
330
331			$this->addOrderedColumns($queryBuilderId);
332
333			$results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
334			$platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
335			$idxMatrix = array();
336			foreach ($results as $id) {
337			foreach ($idNames as $idName) {
338			// Convert ids to database value in case of custom type, if provided.
339			$fieldType = $metadata->getTypeOfField($idName);
340			$idxMatrix[$idName][] = $fieldType && Type::hasType($fieldType)
341			? Type::getType($fieldType)->convertToDatabaseValue($id[$idName], $platform)
342			: $id[$idName];
343			}
344			}
345
346			// step 4 : alter the query to match the targeted ids
347			foreach ($idxMatrix as $idName => $idx) {
348			if (count($idx) > 0) {
349			$idxParamName = sprintf('%s_idx', $idName);
350			$idxParamName = preg_replace('/[^\w]+/', '_', $idxParamName);
351			$queryBuilder->andWhere(sprintf('%s IN (:%s)', $selects[$idName], $idxParamName));
352			$queryBuilder->setParameter($idxParamName, $idx);
353			$queryBuilder->setMaxResults(null);
354			$queryBuilder->setFirstResult(null);
355			}
356			}
357
358			return $queryBuilder;
359			}
360
361			private function addOrderedColumns(QueryBuilder $queryBuilder)
362			{
363			/* For each ORDER BY clause defined directly in the DQL parts of the query,
364			we add an entry in the SELECT clause. */
365			$dqlParts = $queryBuilder->getDqlParts();
366			if ($dqlParts['orderBy'] && count($dqlParts['orderBy'])) {
367			foreach ($dqlParts['orderBy'] as $part) {
368			foreach ($part->getParts() as $orderBy) {
369			$queryBuilder->addSelect(preg_replace("/\s+(ASC\|DESC)$/i", '', $orderBy));
370			}
371			}
372			}
373			}
374			}
375

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — 3.x (#728)

ProxyQuery::getFixedQueryBuilder() F

Complexity

Size

Duplication

Importance

How to fix Long Method Complexity

Long Method

Duplication Side-by-Side

Filter issues like