ProxyQuery::execute() - Code Metrics - Inspection of "Allow to set Query hint in createQuery" - sonata-project/SonataDoctrineORMAdminBundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#730)

unknown

created 2017-09-26 23:30 UTC

ProxyQuery::execute() B

↳ Parent: ProxyQuery

Complexity

Conditions	8
Paths	54

Size

Total Lines	54
Code Lines	29

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	54
rs	7.4119
c	0
b	0
f	0
cc	8
eloc	29
nc	54
nop	2

How to fix Long Method

<?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;

    /**
     * The map of query hints.
     *
     * @var array
     */
    protected $hints = array();

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

        $query = $this->getFixedQueryBuilder($queryBuilder)->getQuery();
        foreach ($this->hints as $name => $value) {
            $query->setHint($name, $value);
        }
        return $query->execute($params, $hydrationMode);
    }

    /**
     * Sets a query hint. If the hint name is not recognized, it is silently ignored.
     *
     * @param string $name  The name of the hint.
     * @param mixed  $value The value of the hint.
     *
     * @return ProxyQueryInterface
     */
    public function setHint($name, $value)
    {
        $this->hints[$name] = $value;

        return $this;
    }

    /**
     * {@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->select($idxSelect);
        $queryBuilderId->distinct();

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

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — 3.x (#730)

ProxyQuery::execute() B

Complexity

Size

Duplication

Importance

How to fix Long Method

Long Method

Duplication Side-by-Side

Filter issues like