コード例 #1
0
 /**
  * @param PlatformInterface $platform
  * @param DriverInterface $driver
  * @param ParameterContainer $parameterContainer
  * @param $sqls
  * @param $parameters
  * @return null
  */
 protected function processLimitOffset(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null, &$sqls, &$parameters)
 {
     if ($this->limit === null && $this->offset === null) {
         return;
     }
     $selectParameters = $parameters[self::SELECT];
     /** if this is a DISTINCT query then real SELECT part goes to second element in array **/
     $parameterIndex = 0;
     if ($selectParameters[0] === 'DISTINCT') {
         unset($selectParameters[0]);
         $selectParameters = array_values($selectParameters);
         $parameterIndex = 1;
     }
     $starSuffix = $platform->getIdentifierSeparator() . self::SQL_STAR;
     foreach ($selectParameters[0] as $i => $columnParameters) {
         if ($columnParameters[0] == self::SQL_STAR || isset($columnParameters[1]) && $columnParameters[1] == self::SQL_STAR || strpos($columnParameters[0], $starSuffix)) {
             $selectParameters[0] = [[self::SQL_STAR]];
             break;
         }
         if (isset($columnParameters[1])) {
             array_shift($columnParameters);
             $selectParameters[0][$i] = $columnParameters;
         }
     }
     // first, produce column list without compound names (using the AS portion only)
     array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(['SELECT %1$s FROM (' => current($this->specifications[self::SELECT])], $selectParameters));
     if ($parameterContainer) {
         // create bottom part of query, with offset and limit using row_number
         $limitParamName = $driver->formatParameterName('limit');
         $offsetParamName = $driver->formatParameterName('offset');
         $offsetForSumParamName = $driver->formatParameterName('offsetForSum');
         array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ' . $offsetParamName . '+1 AND ' . $limitParamName . '+' . $offsetForSumParamName);
         $parameterContainer->offsetSet('offset', $this->offset);
         $parameterContainer->offsetSet('limit', $this->limit);
         $parameterContainer->offsetSetReference('offsetForSum', 'offset');
     } else {
         array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ' . (int) $this->offset . '+1 AND ' . (int) $this->limit . '+' . (int) $this->offset);
     }
     if (isset($sqls[self::ORDER])) {
         $orderBy = $sqls[self::ORDER];
         unset($sqls[self::ORDER]);
     } else {
         $orderBy = 'ORDER BY (SELECT 1)';
     }
     // add a column for row_number() using the order specification
     $parameters[self::SELECT][$parameterIndex][] = ['ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]'];
     $sqls[self::SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SELECT], $parameters[self::SELECT]);
 }
コード例 #2
0
ファイル: SelectDecorator.php プロジェクト: rikaix/zf2
 /**
  * @param PlatformInterface $platform
  * @param Adapter $adapter
  * @param ParameterContainer $parameterContainer
  * @param $sqls
  * @param $parameters
  * @return null
  */
 protected function processLimitOffset(PlatformInterface $platform, Adapter $adapter = null, ParameterContainer $parameterContainer = null, &$sqls, &$parameters)
 {
     if ($this->limit === null && $this->offset === null) {
         return null;
     }
     $selectParameters = $parameters[self::SPECIFICATION_SELECT];
     $starSuffix = $platform->getIdentifierSeparator() . self::SQL_STAR;
     foreach ($selectParameters[0] as $i => $columnParameters) {
         if ($columnParameters[0] == self::SQL_STAR || isset($columnParameters[1]) && $columnParameters[1] == self::SQL_STAR || strpos($columnParameters[0], $starSuffix)) {
             $selectParameters[0] = array(array(self::SQL_STAR));
             break;
         }
         if (isset($columnParameters[1])) {
             array_shift($columnParameters);
             $selectParameters[0][$i] = $columnParameters;
         }
     }
     // first, produce column list without compound names (using the AS portion only)
     array_unshift($sqls, $this->createSqlFromSpecificationAndParameters(array('SELECT %1$s FROM (' => current($this->specifications[self::SPECIFICATION_SELECT])), $selectParameters));
     if ($parameterContainer) {
         // create bottom part of query, with offset and limit using row_number
         array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ?+1 AND ?+?');
         $parameterContainer->offsetSet('offset', $this->offset);
         $parameterContainer->offsetSet('limit', $this->limit);
         $parameterContainer->offsetSetReference('offsetForSum', 'offset');
     } else {
         array_push($sqls, ') AS [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION] WHERE [ZEND_SQL_SERVER_LIMIT_OFFSET_EMULATION].[__ZEND_ROW_NUMBER] BETWEEN ' . (int) $this->offset . '+1 AND ' . (int) $this->limit . '+' . (int) $this->offset);
     }
     if (isset($sqls[self::SPECIFICATION_ORDER])) {
         $orderBy = $sqls[self::SPECIFICATION_ORDER];
         unset($sqls[self::SPECIFICATION_ORDER]);
     } else {
         $orderBy = 'SELECT 1';
     }
     // add a column for row_number() using the order specification
     $parameters[self::SPECIFICATION_SELECT][0][] = array('ROW_NUMBER() OVER (' . $orderBy . ')', '[__ZEND_ROW_NUMBER]');
     $sqls[self::SPECIFICATION_SELECT] = $this->createSqlFromSpecificationAndParameters($this->specifications[self::SPECIFICATION_SELECT], $parameters[self::SPECIFICATION_SELECT]);
 }