/** * Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT * * @param SelectStatement $AST * @return string */ public function walkSelectStatement(SelectStatement $AST) { $innerSql = parent::walkSelectStatement($AST); // Find out the SQL alias of the identifier column of the root entity // It may be possible to make this work with multiple root entities but that // would probably require issuing multiple queries or doing a UNION SELECT // so for now, It's not supported. // Get the root entity and alias from the AST fromClause $from = $AST->fromClause->identificationVariableDeclarations; if (count($from) !== 1) { throw new \RuntimeException("Cannot count query which selects two FROM components, cannot make distinction"); } $rootAlias = $from[0]->rangeVariableDeclaration->aliasIdentificationVariable; $rootClass = $this->queryComponents[$rootAlias]['metadata']; $rootIdentifier = $rootClass->identifier; // For every identifier, find out the SQL alias by combing through the ResultSetMapping $sqlIdentifier = array(); foreach ($rootIdentifier as $property) { if (isset($rootClass->fieldMappings[$property])) { foreach (array_keys($this->rsm->fieldMappings, $property) as $alias) { if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) { $sqlIdentifier[$property] = $alias; } } } if (isset($rootClass->associationMappings[$property])) { $joinColumn = $rootClass->associationMappings[$property]['joinColumns'][0]['name']; foreach (array_keys($this->rsm->metaMappings, $joinColumn) as $alias) { if ($this->rsm->columnOwnerMap[$alias] == $rootAlias) { $sqlIdentifier[$property] = $alias; } } } } if (count($rootIdentifier) != count($sqlIdentifier)) { throw new \RuntimeException(sprintf('Not all identifier properties can be found in the ResultSetMapping: %s', implode(', ', array_diff($rootIdentifier, array_keys($sqlIdentifier))))); } // Build the counter query $sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql); if ($this->platform instanceof PostgreSqlPlatform) { //http://www.doctrine-project.org/jira/browse/DDC-1958 $this->getPostgresqlSql($AST, $sqlIdentifier, $innerSql, $sql); } // Apply the limit and offset $sql = $this->platform->modifyLimitQuery($sql, $this->maxResults, $this->firstResult); // Add the columns to the ResultSetMapping. It's not really nice but // it works. Preferably I'd clear the RSM or simply create a new one // but that is not possible from inside the output walker, so we dirty // up the one we have. foreach ($sqlIdentifier as $property => $alias) { $this->rsm->addScalarResult($alias, $property); } return $sql; }
/** * Gets the SELECT SQL to select one or more entities by a set of field criteria. * * @param array $criteria * @param AssociationMapping $assoc * @param string $orderBy * @param int $lockMode * @return string * @todo Refactor: _getSelectSQL(...) */ protected function _getSelectEntitiesSQL(array $criteria, $assoc = null, $lockMode = 0, $limit = null, $offset = null) { $joinSql = $assoc != null && $assoc['type'] == ClassMetadata::MANY_TO_MANY ? $this->_getSelectManyToManyJoinSQL($assoc) : ''; $conditionSql = $this->_getSelectConditionSQL($criteria, $assoc); $orderBySql = $assoc !== null && isset($assoc['orderBy']) ? $this->_getCollectionOrderBySQL($assoc['orderBy'], $this->_getSQLTableAlias($this->_class->name)) : ''; $lockSql = ''; if ($lockMode == LockMode::PESSIMISTIC_READ) { $lockSql = ' ' . $this->_platform->getReadLockSql(); } else { if ($lockMode == LockMode::PESSIMISTIC_WRITE) { $lockSql = ' ' . $this->_platform->getWriteLockSql(); } } return $this->_platform->modifyLimitQuery('SELECT ' . $this->_getSelectColumnListSQL() . $this->_platform->appendLockHint(' FROM ' . $this->_class->getQuotedTableName($this->_platform) . ' ' . $this->_getSQLTableAlias($this->_class->name), $lockMode) . $this->_selectJoinSql . $joinSql . ($conditionSql ? ' WHERE ' . $conditionSql : '') . $orderBySql, $limit, $offset) . $lockSql; }
/** * Queries the database with limit and offset added to the query and returns * a Statement object. * * @param string $query * @param integer $limit * @param integer $offset * @return Statement */ public function select($query, $limit = 0, $offset = 0) { if ($limit > 0 || $offset > 0) { $query = $this->_platform->modifyLimitQuery($query, $limit, $offset); } return $this->execute($query); }