PHP AbstractPlatform::modifyLimitQuery方法代码示例

 /**
  * @param QueryObjectModel $qom
  *
  * @return string
  */
 public function walkQOMQuery(QueryObjectModel $qom)
 {
     $source = $qom->getSource();
     $selectors = $this->validateSource($source);
     $sourceSql = " " . $this->walkSource($source);
     $constraintSql = '';
     if ($constraint = $qom->getConstraint()) {
         $constraintSql = " AND " . $this->walkConstraint($constraint);
     }
     $orderingSql = '';
     if ($orderings = $qom->getOrderings()) {
         $orderingSql = " " . $this->walkOrderings($orderings);
     }
     $sql = "SELECT " . $this->getColumns($qom);
     $sql .= $sourceSql;
     $sql .= $constraintSql;
     $sql .= $orderingSql;
     $limit = $qom->getLimit();
     $offset = $qom->getOffset();
     if (null !== $offset && null == $limit && ($this->platform instanceof MySqlPlatform || $this->platform instanceof SqlitePlatform)) {
         $limit = PHP_INT_MAX;
     }
     $sql = $this->platform->modifyLimitQuery($sql, $limit, $offset);
     return array($selectors, $this->alias, $sql);
 }

 /**
  * Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT.
  * This method is for platforms which DO NOT support ROW_NUMBER.
  *
  * @param SelectStatement $AST
  * @param bool $addMissingItemsFromOrderByToSelect
  *
  * @return string
  *
  * @throws \RuntimeException
  */
 public function walkSelectStatementWithoutRowNumber(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true)
 {
     // We don't want to call this recursively!
     if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) {
         // In the case of ordering a query by columns from joined tables, we
         // must add those columns to the select clause of the query BEFORE
         // the SQL is generated.
         $this->addMissingItemsFromOrderByToSelect($AST);
     }
     // Remove order by clause from the inner query
     // It will be re-appended in the outer select generated by this method
     $orderByClause = $AST->orderByClause;
     $AST->orderByClause = null;
     $innerSql = $this->getInnerSQL($AST);
     $sqlIdentifier = $this->getSQLIdentifier($AST);
     // Build the counter query
     $sql = sprintf('SELECT DISTINCT %s FROM (%s) dctrn_result', implode(', ', $sqlIdentifier), $innerSql);
     // http://www.doctrine-project.org/jira/browse/DDC-1958
     $sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause);
     // 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);
     }
     // Restore orderByClause
     $AST->orderByClause = $orderByClause;
     return $sql;
 }

 /**
  * Return a list of all revisions.
  *
  * @param int $limit
  * @param int $offset
  * @return Revision[]
  */
 public function findRevisionHistory($limit = 20, $offset = 0)
 {
     $query = $this->platform->modifyLimitQuery("SELECT * FROM " . $this->config->getRevisionTableName() . " ORDER BY id DESC", $limit, $offset);
     $revisionsData = $this->em->getConnection()->fetchAll($query);
     $revisions = array();
     foreach ($revisionsData as $row) {
         $revisions[] = new Revision($row['id'], \DateTime::createFromFormat($this->platform->getDateTimeFormatString(), $row['timestamp']), $row['username']);
     }
     return $revisions;
 }

 /**
  * 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
  * @param int $limit
  * @param int $offset
  * @param array $orderBy
  * @return string
  * @todo Refactor: _getSelectSQL(...)
  */
 protected function _getSelectEntitiesSQL(array $criteria, $assoc = null, $lockMode = 0, $limit = null, $offset = null, array $orderBy = null)
 {
     $joinSql = $assoc != null && $assoc['type'] == ClassMetadata::MANY_TO_MANY ? $this->_getSelectManyToManyJoinSQL($assoc) : '';
     $conditionSql = $this->_getSelectConditionSQL($criteria, $assoc);
     $orderBy = $assoc !== null && isset($assoc['orderBy']) ? $assoc['orderBy'] : $orderBy;
     $orderBySql = $orderBy ? $this->_getOrderBySQL($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;
 }

 /**
  * {@inheritdoc}
  */
 public function walkSelectStatement(AST\SelectStatement $AST)
 {
     $sql = $this->walkSelectClause($AST->selectClause);
     $sql .= $this->walkFromClause($AST->fromClause);
     $sql .= $this->walkWhereClause($AST->whereClause);
     $sql .= $AST->groupByClause ? $this->walkGroupByClause($AST->groupByClause) : '';
     $sql .= $AST->havingClause ? $this->walkHavingClause($AST->havingClause) : '';
     if (($orderByClause = $AST->orderByClause) !== null) {
         $sql .= $AST->orderByClause ? $this->walkOrderByClause($AST->orderByClause) : '';
     } else {
         if (($orderBySql = $this->_generateOrderedCollectionOrderByItems()) !== '') {
             $sql .= ' ORDER BY ' . $orderBySql;
         }
     }
     $sql = $this->platform->modifyLimitQuery($sql, $this->query->getMaxResults(), $this->query->getFirstResult());
     if (($lockMode = $this->query->getHint(Query::HINT_LOCK_MODE)) !== false) {
         switch ($lockMode) {
             case LockMode::PESSIMISTIC_READ:
                 $sql .= ' ' . $this->platform->getReadLockSQL();
                 break;
             case LockMode::PESSIMISTIC_WRITE:
                 $sql .= ' ' . $this->platform->getWriteLockSQL();
                 break;
             case LockMode::OPTIMISTIC:
                 foreach ($this->selectedClasses as $selectedClass) {
                     if (!$selectedClass['class']->isVersioned) {
                         throw \Doctrine\ORM\OptimisticLockException::lockFailed($selectedClass['class']->name);
                     }
                 }
                 break;
             case LockMode::NONE:
                 break;
             default:
                 throw \Doctrine\ORM\Query\QueryException::invalidLockMode();
         }
     }
     return $sql;
 }

    /**
     * Gets the SELECT SQL to select one or more entities by a set of field criteria.
     *
     * @param array|\Doctrine\Common\Collections\Criteria $criteria
     * @param array|null                                  $assoc
     * @param int                                         $lockMode
     * @param int|null                                    $limit
     * @param int|null                                    $offset
     * @param array|null                                  $orderBy
     *
     * @return string
     */
    protected function getSelectSQL($criteria, $assoc = null, $lockMode = 0, $limit = null, $offset = null, array $orderBy = null)
    {
        $lockSql    = '';
        $joinSql    = '';
        $orderBySql = '';

        if ($assoc != null && $assoc['type'] == ClassMetadata::MANY_TO_MANY) {
            $joinSql = $this->getSelectManyToManyJoinSQL($assoc);
        }

        if (isset($assoc['orderBy'])) {
            $orderBy = $assoc['orderBy'];
        }

        if ($orderBy) {
            $orderBySql = $this->getOrderBySQL($orderBy, $this->getSQLTableAlias($this->class->name));
        }

        $conditionSql = ($criteria instanceof Criteria)
            ? $this->getSelectConditionCriteriaSQL($criteria)
            : $this->getSelectConditionSQL($criteria, $assoc);

        switch ($lockMode) {
            case LockMode::PESSIMISTIC_READ:
                $lockSql = ' ' . $this->platform->getReadLockSql();
                break;

            case LockMode::PESSIMISTIC_WRITE:
                $lockSql = ' ' . $this->platform->getWriteLockSql();
                break;
        }

        $columnList = $this->getSelectColumnsSQL();
        $tableAlias = $this->getSQLTableAlias($this->class->name);
        $filterSql  = $this->generateFilterConditionSQL($this->class, $tableAlias);
        $tableName  = $this->quoteStrategy->getTableName($this->class, $this->platform);

        if ('' !== $filterSql) {
            $conditionSql = $conditionSql 
                ? $conditionSql . ' AND ' . $filterSql
                : $filterSql;
        }

        $select = 'SELECT ' . $columnList;
        $from   = ' FROM ' . $tableName . ' '. $tableAlias;
        $join   = $this->selectJoinSql . $joinSql;
        $where  = ($conditionSql ? ' WHERE ' . $conditionSql : '');
        $lock   = $this->platform->appendLockHint($from, $lockMode);
        $query  = $select
            . $lock
            . $join
            . $where
            . $orderBySql;

        return $this->platform->modifyLimitQuery($query, $limit, $offset) . $lockSql;
    }

 /**
  * {@inheritdoc}
  */
 public function walkSelectStatement(AST\SelectStatement $AST)
 {
     $limit = $this->query->getMaxResults();
     $offset = $this->query->getFirstResult();
     $lockMode = $this->query->getHint(Query::HINT_LOCK_MODE);
     $sql = $this->walkSelectClause($AST->selectClause) . $this->walkFromClause($AST->fromClause) . $this->walkWhereClause($AST->whereClause);
     if ($AST->groupByClause) {
         $sql .= $this->walkGroupByClause($AST->groupByClause);
     }
     if ($AST->havingClause) {
         $sql .= $this->walkHavingClause($AST->havingClause);
     }
     if ($AST->orderByClause) {
         $sql .= $this->walkOrderByClause($AST->orderByClause);
     }
     if (!$AST->orderByClause && ($orderBySql = $this->_generateOrderedCollectionOrderByItems())) {
         $sql .= ' ORDER BY ' . $orderBySql;
     }
     if ($limit !== null || $offset !== null) {
         $sql = $this->platform->modifyLimitQuery($sql, $limit, $offset);
     }
     if ($lockMode === null || $lockMode === false || $lockMode === LockMode::NONE) {
         return $sql;
     }
     if ($lockMode === LockMode::PESSIMISTIC_READ) {
         return $sql . ' ' . $this->platform->getReadLockSQL();
     }
     if ($lockMode === LockMode::PESSIMISTIC_WRITE) {
         return $sql . ' ' . $this->platform->getWriteLockSQL();
     }
     if ($lockMode !== LockMode::OPTIMISTIC) {
         throw QueryException::invalidLockMode();
     }
     foreach ($this->selectedClasses as $selectedClass) {
         if (!$selectedClass['class']->isVersioned) {
             throw OptimisticLockException::lockFailed($selectedClass['class']->name);
         }
     }
     return $sql;
 }

 /**
  * Walks down a SelectStatement AST node, wrapping it in a SELECT DISTINCT.
  *
  * @param SelectStatement $AST
  * @param bool $addMissingItemsFromOrderByToSelect
  *
  * @return string
  *
  * @throws \RuntimeException
  */
 public function walkSelectStatement(SelectStatement $AST, $addMissingItemsFromOrderByToSelect = true)
 {
     // We don't want to call this recursively!
     if ($AST->orderByClause instanceof OrderByClause && $addMissingItemsFromOrderByToSelect) {
         // In the case of ordering a query by columns from joined tables, we
         // must add those columns to the select clause of the query BEFORE
         // the SQL is generated.
         $this->addMissingItemsFromOrderByToSelect($AST);
     }
     // Remove order by clause from the inner query
     // It will be re-appended in the outer select generated by this method
     $orderByClause = $AST->orderByClause;
     $AST->orderByClause = null;
     // Set every select expression as visible(hidden = false) to
     // make $AST have scalar mappings properly - this is relevant for referencing selected
     // fields from outside the subquery, for example in the ORDER BY segment
     $hiddens = array();
     foreach ($AST->selectClause->selectExpressions as $idx => $expr) {
         $hiddens[$idx] = $expr->hiddenAliasResultVariable;
         $expr->hiddenAliasResultVariable = false;
     }
     $innerSql = parent::walkSelectStatement($AST);
     // Restore orderByClause
     $AST->orderByClause = $orderByClause;
     // Restore hiddens
     foreach ($AST->selectClause->selectExpressions as $idx => $expr) {
         $expr->hiddenAliasResultVariable = $hiddens[$idx];
     }
     // 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");
     }
     $fromRoot = reset($from);
     $rootAlias = $fromRoot->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($sqlIdentifier) === 0) {
         throw new \RuntimeException('The Paginator does not support Queries which only yield ScalarResults.');
     }
     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);
     // http://www.doctrine-project.org/jira/browse/DDC-1958
     $sql = $this->preserveSqlOrdering($sqlIdentifier, $innerSql, $sql, $orderByClause);
     // 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;
 }