ProxyQuery::getQueryBuilder() - Code Metrics - Inspection of "Always sort" - sonata-project/SonataDoctrineORMAdminBundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — master (#517)

by Grégoire

created 2016-01-05 19:07 UTC

ProxyQuery::getQueryBuilder() A

↳ Parent: ProxyQuery

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Metric	Value
dl	0
loc	4
rs	10
cc	1
eloc	2
nc	1
nop	0

<?php

/*
 * This file is part of the symfony package.
 * (c) Fabien Potencier <[email protected]>
 * (c) Jonathan H. Wage <[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\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 execute(array $params = array(), $hydrationMode = null)
    {
        // always clone the original queryBuilder
        $queryBuilder = clone $this->queryBuilder;

        // todo : check how doctrine behave, potential SQL injection here ...
        if ($this->getSortBy()) {
            $sortBy = $this->getSortBy();
            if (strpos($sortBy, '.') === false) { // add the current alias
                $sortBy = $queryBuilder->getRootAlias().'.'.$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();

        foreach ($identifierFields as $identifierField) {
            $queryBuilder->addOrderBy(
                $queryBuilder->getRootAlias().'.'.$identifierField,

                $this->getSortOrder() // reusing the sort order is the most natural way to go
            );
        }

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

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

        // 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', $queryBuilderId->getRootAlias(), $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 = $queryBuilderId->getRootAlias().'.'.$sortBy;

            }
            $sortBy .= ' AS __order_by';
            $queryBuilderId->addSelect($sortBy);
        }

        $results    = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
        $idxMatrix  = array();
        foreach ($results as $id) {
            foreach ($idNames as $idName) {
                $idxMatrix[$idName][] = $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;
    }

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

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

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


1			<?php
2
3			/*
4			* This file is part of the symfony package.
5			* (c) Fabien Potencier <[email protected]>
6			* (c) Jonathan H. Wage <[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\ORM\Query;
15			use Doctrine\ORM\QueryBuilder;
16			use Sonata\AdminBundle\Datagrid\ProxyQueryInterface;
17
18			/**
19			* This class try to unify the query usage with Doctrine.
20			*/
21			class ProxyQuery implements ProxyQueryInterface
22			{
23			/**
24			* @var QueryBuilder
25			*/
26			protected $queryBuilder;
27
28			/**
29			* @var string
30			*/
31			protected $sortBy;
32
33			/**
34			* @var mixed
35			*/
36			protected $sortOrder;
37
38			/**
39			* @var int
40			*/
41			protected $uniqueParameterId;
42
43			/**
44			* @var string[]
45			*/
46			protected $entityJoinAliases;
47
48			/**
49			* @param QueryBuilder $queryBuilder
50			*/
51			public function __construct($queryBuilder)
52			{
53			$this->queryBuilder = $queryBuilder;
54			$this->uniqueParameterId = 0;
55			$this->entityJoinAliases = array();
56			}
57
58			/**
59			* {@inheritdoc}
60			*/
61			public function execute(array $params = array(), $hydrationMode = null)
62			{
63			// always clone the original queryBuilder
64			$queryBuilder = clone $this->queryBuilder;
65
66			// todo : check how doctrine behave, potential SQL injection here ...
67			if ($this->getSortBy()) {
68			$sortBy = $this->getSortBy();
69			if (strpos($sortBy, '.') === false) { // add the current alias
70			$sortBy = $queryBuilder->getRootAlias().'.'.$sortBy;
			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...
71			}
72			$queryBuilder->addOrderBy($sortBy, $this->getSortOrder());
73			} else {
74			$queryBuilder->resetDQLPart('orderBy');
75			}
76
77			/* By default, always add a sort on the identifier fields of the first
78			* used entity in the query, because RDBMS do not guarantee a
79			* particular order when no ORDER BY clause is specified, or when
80			* the field used for sorting is not unique.
81			*/
82
83			$identifierFields = $queryBuilder
84			->getEntityManager()
85			->getMetadataFactory()
86			->getMetadataFor(current($queryBuilder->getRootEntities()))
87			->getIdentifierFieldNames();
88
89			foreach ($identifierFields as $identifierField) {
90			$queryBuilder->addOrderBy(
91			$queryBuilder->getRootAlias().'.'.$identifierField,
			0 ignored issues – show Deprecated Code introduced 2015-12-26 14:40 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...
92			$this->getSortOrder() // reusing the sort order is the most natural way to go
93			);
94			}
95
96			return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
97			}
98
99			/**
100			* This method alters the query to return a clean set of object with a working
101			* set of Object.
102			*
103			* @param QueryBuilder $queryBuilder
104			*
105			* @return QueryBuilder
106			*/
107			private function getFixedQueryBuilder(QueryBuilder $queryBuilder)
108			{
109			$queryBuilderId = clone $queryBuilder;
110
111			// step 1 : retrieve the targeted class
112			$from = $queryBuilderId->getDQLPart('from');
113			$class = $from[0]->getFrom();
114			$metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);
115
116			// step 2 : retrieve identifier columns
117			$idNames = $metadata->getIdentifierFieldNames();
118
119			// step 3 : retrieve the different subjects ids
120			$selects = array();
121			$idxSelect = '';
122			foreach ($idNames as $idName) {
123			$select = sprintf('%s.%s', $queryBuilderId->getRootAlias(), $idName);
			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...
124			// Put the ID select on this array to use it on results QB
125			$selects[$idName] = $select;
126			// Use IDENTITY if id is a relation too. See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
127			// Should work only with doctrine/orm: ~2.2
128			$idSelect = $select;
129			if ($metadata->hasAssociation($idName)) {
130			$idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);
131			}
132			$idxSelect .= ($idxSelect !== '' ? ', ' : '').$idSelect;
133			}
134			$queryBuilderId->resetDQLPart('select');
135			$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...
136
137			// for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list
138			/* Consider
139			SELECT DISTINCT x FROM tab ORDER BY y;
140			For any particular x-value in the table there might be many different y
141			values. Which one will you use to sort that x-value in the output?
142			*/
143			// todo : check how doctrine behave, potential SQL injection here ...
144			if ($this->getSortBy()) {
145			$sortBy = $this->getSortBy();
146			if (strpos($sortBy, '.') === false) { // add the current alias
147			$sortBy = $queryBuilderId->getRootAlias().'.'.$sortBy;
			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...
148			}
149			$sortBy .= ' AS __order_by';
150			$queryBuilderId->addSelect($sortBy);
151			}
152
153			$results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
154			$idxMatrix = array();
155			foreach ($results as $id) {
156			foreach ($idNames as $idName) {
157			$idxMatrix[$idName][] = $id[$idName];
158			}
159			}
160
161			// step 4 : alter the query to match the targeted ids
162			foreach ($idxMatrix as $idName => $idx) {
163			if (count($idx) > 0) {
164			$idxParamName = sprintf('%s_idx', $idName);
165			$idxParamName = preg_replace('/[^\w]+/', '_', $idxParamName);
166			$queryBuilder->andWhere(sprintf('%s IN (:%s)', $selects[$idName], $idxParamName));
167			$queryBuilder->setParameter($idxParamName, $idx);
168			$queryBuilder->setMaxResults(null);
169			$queryBuilder->setFirstResult(null);
170			}
171			}
172
173			return $queryBuilder;
174			}
175
176			/**
177			* {@inheritdoc}
178			*/
179			public function __call($name, $args)
180			{
181			return call_user_func_array(array($this->queryBuilder, $name), $args);
182			}
183
184			/**
185			* {@inheritdoc}
186			*/
187			public function __get($name)
188			{
189			return $this->queryBuilder->$name;
190			}
191
192			/**
193			* {@inheritdoc}
194			*/
195			public function setSortBy($parentAssociationMappings, $fieldMapping)
196			{
197			$alias = $this->entityJoin($parentAssociationMappings);
198			$this->sortBy = $alias.'.'.$fieldMapping['fieldName'];
199
200			return $this;
201			}
202
203			/**
204			* {@inheritdoc}
205			*/
206			public function getSortBy()
207			{
208			return $this->sortBy;
209			}
210
211			/**
212			* {@inheritdoc}
213			*/
214			public function setSortOrder($sortOrder)
215			{
216			$this->sortOrder = $sortOrder;
217
218			return $this;
219			}
220
221			/**
222			* {@inheritdoc}
223			*/
224			public function getSortOrder()
225			{
226			return $this->sortOrder;
227			}
228
229			/**
230			* {@inheritdoc}
231			*/
232			public function getSingleScalarResult()
233			{
234			$query = $this->queryBuilder->getQuery();
235
236			return $query->getSingleScalarResult();
237			}
238
239			/**
240			* {@inheritdoc}
241			*/
242			public function __clone()
243			{
244			$this->queryBuilder = clone $this->queryBuilder;
245			}
246
247			/**
248			* @return mixed
249			*/
250			public function getQueryBuilder()
251			{
252			return $this->queryBuilder;
253			}
254
255			/**
256			* {@inheritdoc}
257			*/
258			public function setFirstResult($firstResult)
259			{
260			$this->queryBuilder->setFirstResult($firstResult);
261
262			return $this;
263			}
264
265			/**
266			* {@inheritdoc}
267			*/
268			public function getFirstResult()
269			{
270			return $this->queryBuilder->getFirstResult();
271			}
272
273			/**
274			* {@inheritdoc}
275			*/
276			public function setMaxResults($maxResults)
277			{
278			$this->queryBuilder->setMaxResults($maxResults);
279
280			return $this;
281			}
282
283			/**
284			* {@inheritdoc}
285			*/
286			public function getMaxResults()
287			{
288			return $this->queryBuilder->getMaxResults();
289			}
290
291			/**
292			* {@inheritdoc}
293			*/
294			public function getUniqueParameterId()
295			{
296			return $this->uniqueParameterId++;
297			}
298
299			/**
300			* {@inheritdoc}
301			*/
302			public function entityJoin(array $associationMappings)
303			{
304			$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...
305
306			$newAlias = 's';
307
308			$joinedEntities = $this->queryBuilder->getDQLPart('join');
309
310			foreach ($associationMappings as $associationMapping) {
311
312			// Do not add left join to already joined entities with custom query
313			foreach ($joinedEntities as $joinExprList) {
314			foreach ($joinExprList as $joinExpr) {
315			$newAliasTmp = $joinExpr->getAlias();
316
317			if (sprintf('%s.%s', $alias, $associationMapping['fieldName']) === $joinExpr->getJoin()) {
318			$this->entityJoinAliases[] = $newAliasTmp;
319			$alias = $newAliasTmp;
320
321			continue 3;
322			}
323			}
324			}
325
326			$newAlias .= '_'.$associationMapping['fieldName'];
327			if (!in_array($newAlias, $this->entityJoinAliases)) {
328			$this->entityJoinAliases[] = $newAlias;
329			$this->queryBuilder->leftJoin(sprintf('%s.%s', $alias, $associationMapping['fieldName']), $newAlias);
330			}
331
332			$alias = $newAlias;
333			}
334
335			return $alias;
336			}
337			}
338

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — master (#517)

ProxyQuery::getQueryBuilder() A

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like