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-17 18: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;

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

        foreach ($identifierFields as $identifierField) {
            $queryBuilder->addOrderBy(
                $rootAlias.'.'.$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;
        $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);
        }

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

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — master (#517)

ProxyQuery::getQueryBuilder() A

Complexity

Size

Duplication

Duplication Side-by-Side

Filter issues like