ProxyQuery::addOrderedColumns() - Code Metrics - Inspection of "Replace string operation on OrderBy by AST walker" - sonata-project/SonataDoctrineORMAdminBundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — 3.x ( fd994c...df5a7a )

by Jordi Sala

created 2018-01-02 11:07 UTC

ProxyQuery::addOrderedColumns() A

↳ Parent: Project

Complexity

Conditions	4
Paths	4

Size

Total Lines	14
Code Lines	6

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	14
rs	9.2
c	0
b	0
f	0
cc	4
eloc	6
nc	4
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<string,mixed>
     */
    private $hints = [];

    /**
     * @param QueryBuilder $queryBuilder
     */
    public function __construct($queryBuilder)
    {
        $this->queryBuilder = $queryBuilder;
        $this->uniqueParameterId = 0;
        $this->entityJoinAliases = [];
    }

    public function __call($name, $args)
    {
        return call_user_func_array([$this->queryBuilder, $name], $args);
    }

    public function __get($name)
    {
        return $this->queryBuilder->$name;
    }

    public function __clone()
    {
        $this->queryBuilder = clone $this->queryBuilder;
    }

    public function execute(array $params = [], $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 (false === strpos($sortBy, '.')) { // 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 = [];
        /** @var Query\Expr\OrderBy $order */
        foreach ($queryBuilder->getDQLPart('orderBy') as $order) {
            foreach ($order->getParts() as $part) {
                $existingOrders[] = trim(str_replace([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);
    }

    public function setSortBy($parentAssociationMappings, $fieldMapping)
    {
        $alias = $this->entityJoin($parentAssociationMappings);
        $this->sortBy = $alias.'.'.$fieldMapping['fieldName'];

        return $this;
    }

    public function getSortBy()
    {
        return $this->sortBy;
    }

    public function setSortOrder($sortOrder)
    {
        $this->sortOrder = $sortOrder;

        return $this;
    }

    public function getSortOrder()
    {
        return $this->sortOrder;
    }

    public function getSingleScalarResult()
    {
        $query = $this->queryBuilder->getQuery();

        return $query->getSingleScalarResult();
    }

    /**
     * @return mixed
     */
    public function getQueryBuilder()
    {
        return $this->queryBuilder;
    }

    public function setFirstResult($firstResult)
    {
        $this->queryBuilder->setFirstResult($firstResult);

        return $this;
    }

    public function getFirstResult()
    {
        return $this->queryBuilder->getFirstResult();
    }

    public function setMaxResults($maxResults)
    {
        $this->queryBuilder->setMaxResults($maxResults);

        return $this;
    }

    public function getMaxResults()
    {
        return $this->queryBuilder->getMaxResults();
    }

    public function getUniqueParameterId()
    {
        return $this->uniqueParameterId++;
    }

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

    /**
     * Sets a {@see \Doctrine\ORM\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
     *
     * @see \Doctrine\ORM\Query::setHint
     * @see \Doctrine\ORM\Query::HINT_CUSTOM_OUTPUT_WALKER
     */
    final public function setHint($name, $value)
    {
        $this->hints[$name] = $value;

        return $this;
    }

    /**
     * This method alters the query to return a clean set of object with a working
     * set of Object.
     *
     * @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 = [];
        $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?
        */
        $queryId = $queryBuilderId->getQuery();
        $queryId->setHint(Query::HINT_CUSTOM_TREE_WALKERS, [OrderByToSelectWalker::class]);
        $results = $queryId->execute([], Query::HYDRATE_ARRAY);
        $platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
        $idxMatrix = [];
        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;
    }
}


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

sonata-project / SonataDoctrineORMAdminBundle

Push — 3.x ( fd994c...df5a7a )

ProxyQuery::addOrderedColumns() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like