ProxyQuery::getFirstResult() - Code Metrics - Inspection of "fix #565 A column has been specified more than onc..." - sonata-project/SonataDoctrineORMAdminBundle - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Pull Request — 3.x (#566)

unknown

created 2016-05-25 10:24 UTC

ProxyQuery::getFirstResult() A

↳ Parent: ProxyQuery

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes	1
Bugs	0	Features	0

Metric	Value
c	1
b	0
f	0
dl	0
loc	4
rs	10
cc	1
eloc	2
nc	1
nop	0

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

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

        $existsOrder = [];
        /** @var Query\Expr\OrderBy $order */
        foreach ($queryBuilder->getDQLPart('orderBy') as $order){
            $existsOrder = array_merge($existsOrder, $order->getParts());
        }

        foreach ($identifierFields as $identifierField) {
            $order = $rootAlias.'.'.$identifierField;
            if(!in_array(($order.' '.$this->getSortOrder()), $existsOrder)){
                $queryBuilder->addOrderBy(
                    $order,
                    $this->getSortOrder() // reusing the sort order is the most natural way to go
                );
            }
        }

        return $this->getFixedQueryBuilder($queryBuilder)->getQuery()->execute($params, $hydrationMode);
    }

    /**
     * {@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->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);
        $platform = $queryBuilderId->getEntityManager()->getConnection()->getDatabasePlatform();
        $idxMatrix = array();
        foreach ($results as $id) {
            foreach ($idNames as $idName) {
                $phpValue = $id[$idName];
                // Convert ids to database value in case of custom type
                $fieldType = $metadata->getTypeOfField($idName);
                $type = Type::getType($fieldType);
                $dbValue = $type->convertToDatabaseValue($phpValue, $platform);

                $idxMatrix[$idName][] = $dbValue;
            }
        }

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

sonata-project / SonataDoctrineORMAdminBundle

Pull Request — 3.x (#566)

ProxyQuery::getFirstResult() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like