ProxyQuery::execute() - 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:22 UTC

ProxyQuery::execute() B

↳ Parent: ProxyQuery

Complexity

Conditions	4
Paths	6

Size

Total Lines	39
Code Lines	20

Duplication

Lines	0
Ratio	0 %

Importance

Changes	5
Bugs	2	Features	1

Metric	Value
c	5
b	2
f	1
dl	0
loc	39
rs	8.5806
cc	4
eloc	20
nc	6
nop	2

<?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 = current($this->queryBuilder->getRootAliases());

        $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 = current($this->queryBuilder->getRootAliases());
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::execute() B

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like