ProxyQuery::setHint() - 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:32 UTC

ProxyQuery::setHint() A

↳ Parent: ProxyQuery

Complexity

Conditions	1
Paths	1

Size

Total Lines	6
Code Lines	3

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	6
rs	9.4285
c	0
b	0
f	0
cc	1
eloc	3
nc	1
nop	2

<?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
147			return $query->execute($params, $hydrationMode);
148			}
149
150			/**
151			* Sets a query hint. If the hint name is not recognized, it is silently ignored.
152			*
153			* @param string $name the name of the hint
154			* @param mixed $value the value of the hint
155			*
156			* @return ProxyQueryInterface
157			*/
158			public function setHint($name, $value)
159			{
160			$this->hints[$name] = $value;
161
162			return $this;
163			}
164
165			/**
166			* {@inheritdoc}
167			*/
168			public function setSortBy($parentAssociationMappings, $fieldMapping)
169			{
170			$alias = $this->entityJoin($parentAssociationMappings);
171			$this->sortBy = $alias.'.'.$fieldMapping['fieldName'];
172
173			return $this;
174			}
175
176			/**
177			* {@inheritdoc}
178			*/
179			public function getSortBy()
180			{
181			return $this->sortBy;
182			}
183
184			/**
185			* {@inheritdoc}
186			*/
187			public function setSortOrder($sortOrder)
188			{
189			$this->sortOrder = $sortOrder;
190
191			return $this;
192			}
193
194			/**
195			* {@inheritdoc}
196			*/
197			public function getSortOrder()
198			{
199			return $this->sortOrder;
200			}
201
202			/**
203			* {@inheritdoc}
204			*/
205			public function getSingleScalarResult()
206			{
207			$query = $this->queryBuilder->getQuery();
208
209			return $query->getSingleScalarResult();
210			}
211
212			/**
213			* @return mixed
214			*/
215			public function getQueryBuilder()
216			{
217			return $this->queryBuilder;
218			}
219
220			/**
221			* {@inheritdoc}
222			*/
223			public function setFirstResult($firstResult)
224			{
225			$this->queryBuilder->setFirstResult($firstResult);
226
227			return $this;
228			}
229
230			/**
231			* {@inheritdoc}
232			*/
233			public function getFirstResult()
234			{
235			return $this->queryBuilder->getFirstResult();
236			}
237
238			/**
239			* {@inheritdoc}
240			*/
241			public function setMaxResults($maxResults)
242			{
243			$this->queryBuilder->setMaxResults($maxResults);
244
245			return $this;
246			}
247
248			/**
249			* {@inheritdoc}
250			*/
251			public function getMaxResults()
252			{
253			return $this->queryBuilder->getMaxResults();
254			}
255
256			/**
257			* {@inheritdoc}
258			*/
259			public function getUniqueParameterId()
260			{
261			return $this->uniqueParameterId++;
262			}
263
264			/**
265			* {@inheritdoc}
266			*/
267			public function entityJoin(array $associationMappings)
268			{
269			$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...
270
271			$newAlias = 's';
272
273			$joinedEntities = $this->queryBuilder->getDQLPart('join');
274
275			foreach ($associationMappings as $associationMapping) {
276			// Do not add left join to already joined entities with custom query
277			foreach ($joinedEntities as $joinExprList) {
278			foreach ($joinExprList as $joinExpr) {
279			$newAliasTmp = $joinExpr->getAlias();
280
281			if (sprintf('%s.%s', $alias, $associationMapping['fieldName']) === $joinExpr->getJoin()) {
282			$this->entityJoinAliases[] = $newAliasTmp;
283			$alias = $newAliasTmp;
284
285			continue 3;
286			}
287			}
288			}
289
290			$newAlias .= '_'.$associationMapping['fieldName'];
291			if (!in_array($newAlias, $this->entityJoinAliases)) {
292			$this->entityJoinAliases[] = $newAlias;
293			$this->queryBuilder->leftJoin(sprintf('%s.%s', $alias, $associationMapping['fieldName']), $newAlias);
294			}
295
296			$alias = $newAlias;
297			}
298
299			return $alias;
300			}
301
302			/**
303			* This method alters the query to return a clean set of object with a working
304			* set of Object.
305			*
306			* @param QueryBuilder $queryBuilder
307			*
308			* @return QueryBuilder
309			*/
310			protected function getFixedQueryBuilder(QueryBuilder $queryBuilder)
311			{
312			$queryBuilderId = clone $queryBuilder;
313			$rootAlias = current($queryBuilderId->getRootAliases());
314
315			// step 1 : retrieve the targeted class
316			$from = $queryBuilderId->getDQLPart('from');
317			$class = $from[0]->getFrom();
318			$metadata = $queryBuilderId->getEntityManager()->getMetadataFactory()->getMetadataFor($class);
319
320			// step 2 : retrieve identifier columns
321			$idNames = $metadata->getIdentifierFieldNames();
322
323			// step 3 : retrieve the different subjects ids
324			$selects = array();
325			$idxSelect = '';
326			foreach ($idNames as $idName) {
327			$select = sprintf('%s.%s', $rootAlias, $idName);
328			// Put the ID select on this array to use it on results QB
329			$selects[$idName] = $select;
330			// Use IDENTITY if id is a relation too.
331			// See: http://doctrine-orm.readthedocs.org/en/latest/reference/dql-doctrine-query-language.html
332			// Should work only with doctrine/orm: ~2.2
333			$idSelect = $select;
334			if ($metadata->hasAssociation($idName)) {
335			$idSelect = sprintf('IDENTITY(%s) as %s', $idSelect, $idName);
336			}
337			$idxSelect .= ($idxSelect !== '' ? ', ' : '').$idSelect;
338			}
339			$queryBuilderId->select($idxSelect);
340			$queryBuilderId->distinct();
341
342			// for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list
343			/* Consider
344			SELECT DISTINCT x FROM tab ORDER BY y;
345			For any particular x-value in the table there might be many different y
346			values. Which one will you use to sort that x-value in the output?
347			*/
348			$this->addOrderedColumns($queryBuilderId);
349
350			$results = $queryBuilderId->getQuery()->execute(array(), Query::HYDRATE_ARRAY);
351			$platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
352			$idxMatrix = array();
353			foreach ($results as $id) {
354			foreach ($idNames as $idName) {
355			// Convert ids to database value in case of custom type, if provided.
356			$fieldType = $metadata->getTypeOfField($idName);
357			$idxMatrix[$idName][] = $fieldType && Type::hasType($fieldType)
358			? Type::getType($fieldType)->convertToDatabaseValue($id[$idName], $platform)
359			: $id[$idName];
360			}
361			}
362
363			// step 4 : alter the query to match the targeted ids
364			foreach ($idxMatrix as $idName => $idx) {
365			if (count($idx) > 0) {
366			$idxParamName = sprintf('%s_idx', $idName);
367			$idxParamName = preg_replace('/[^\w]+/', '_', $idxParamName);
368			$queryBuilder->andWhere(sprintf('%s IN (:%s)', $selects[$idName], $idxParamName));
369			$queryBuilder->setParameter($idxParamName, $idx);
370			$queryBuilder->setMaxResults(null);
371			$queryBuilder->setFirstResult(null);
372			}
373			}
374
375			return $queryBuilder;
376			}
377
378			private function addOrderedColumns(QueryBuilder $queryBuilder)
379			{
380			/* For each ORDER BY clause defined directly in the DQL parts of the query,
381			we add an entry in the SELECT clause. */
382			foreach ((array) $queryBuilder->getDqlPart('orderBy') as $part) {
383			foreach ($part->getParts() as $orderBy) {
384			$queryBuilder->addSelect(preg_replace("/\s+(ASC\|DESC)$/i", '', $orderBy));
385			}
386			}
387			}
388			}
389

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — 3.x (#730)

ProxyQuery::setHint() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like