7 * This source file is subject to the new BSD license that is bundled
8 * with this package in the file LICENSE.txt.
9 * If you did not receive a copy of the license and are unable to
10 * obtain it through the world-wide-web, please send an email
11 * to kontakt@beberlei.de so I can send you a copy immediately.
14 namespace Doctrine\ORM\Tools\Pagination;
16 use Doctrine\ORM\Query\SqlWalker,
17 Doctrine\ORM\Query\AST\SelectStatement;
20 * Wrap the query in order to select root entity IDs for pagination
22 * Given a DQL like `SELECT u FROM User u` it will generate an SQL query like:
23 * SELECT DISTINCT <id> FROM (<original SQL>) LIMIT x OFFSET y
25 * Works with composite keys but cannot deal with queries that have multiple
26 * root entities (e.g. `SELECT f, b from Foo, Bar`)
28 * @author Sander Marechal <s.marechal@jejik.com>
30 class LimitSubqueryOutputWalker extends SqlWalker
33 * @var Doctrine\DBAL\Platforms\AbstractPlatform
38 * @var Doctrine\ORM\Query\ResultSetMapping
45 private $queryComponents;
58 * Constructor. Stores various parameters that are otherwise unavailable
59 * because Doctrine\ORM\Query\SqlWalker keeps everything private without
62 * @param Doctrine\ORM\Query $query
63 * @param Doctrine\ORM\Query\ParserResult $parserResult
64 * @param array $queryComponents
66 public function __construct($query, $parserResult, array $queryComponents)
68 $this->platform = $query->getEntityManager()->getConnection()->getDatabasePlatform();
69 $this->rsm = $parserResult->getResultSetMapping();
70 $this->queryComponents = $queryComponents;
72 // Reset limit and offset
73 $this->firstResult = $query->getFirstResult();
74 $this->maxResults = $query->getMaxResults();
75 $query->setFirstResult(null)->setMaxResults(null);
77 parent::__construct($query, $parserResult, $queryComponents);
81 * Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT
83 * @param SelectStatement $AST
86 public function walkSelectStatement(SelectStatement $AST)
88 $sql = parent::walkSelectStatement($AST);
90 // Find out the SQL alias of the identifier column of the root entity
91 // It may be possible to make this work with multiple root entities but that
92 // would probably require issuing multiple queries or doing a UNION SELECT
93 // so for now, It's not supported.
95 // Get the root entity and alias from the AST fromClause
96 $from = $AST->fromClause->identificationVariableDeclarations;
97 if (count($from) !== 1) {
98 throw new \RuntimeException("Cannot count query which selects two FROM components, cannot make distinction");
101 $rootAlias = $from[0]->rangeVariableDeclaration->aliasIdentificationVariable;
102 $rootClass = $this->queryComponents[$rootAlias]['metadata'];
103 $rootIdentifier = $rootClass->identifier;
105 // For every identifier, find out the SQL alias by combing through the ResultSetMapping
106 $sqlIdentifier = array();
107 foreach ($rootIdentifier as $property) {
108 if (isset($rootClass->fieldMappings[$property])) {
109 foreach (array_keys($this->rsm->fieldMappings, $property) as $alias) {
110 if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
111 $sqlIdentifier[$property] = $alias;
116 if (isset($rootClass->associationMappings[$property])) {
117 $joinColumn = $rootClass->associationMappings[$property]['joinColumns'][0]['name'];
119 foreach (array_keys($this->rsm->metaMappings, $joinColumn) as $alias) {
120 if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) {
121 $sqlIdentifier[$property] = $alias;
127 if (count($rootIdentifier) != count($sqlIdentifier)) {
128 throw new \RuntimeException(sprintf(
129 'Not all identifier properties can be found in the ResultSetMapping: %s',
130 implode(', ', array_diff($rootIdentifier, array_keys($sqlIdentifier)))
134 // Build the counter query
135 $sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result',
136 implode(', ', $sqlIdentifier), $sql);
138 // Apply the limit and offset
139 $sql = $this->platform->modifyLimitQuery(
140 $sql, $this->maxResults, $this->firstResult
143 // Add the columns to the ResultSetMapping. It's not really nice but
144 // it works. Preferably I'd clear the RSM or simply create a new one
145 // but that is not possible from inside the output walker, so we dirty
146 // up the one we have.
147 foreach ($sqlIdentifier as $property => $alias) {
148 $this->rsm->addScalarResult($alias, $property);