LIMIT

Generates a platform-specific LIMIT clause

@param	string	$sql	SQL Query
@return	string
193370
protected function _limit($sql)
	{
		$limit = $this->qb_offset + $this->qb_limit;

		// As of SQL Server 2005 (9.0.*) ROW_NUMBER() is supported,
		// however an ORDER BY clause is required for it to work
		if (version_compare($this->version(), '9', '>=') && $this->qb_offset && ! empty($this->qb_orderby))
		{
			$orderby = $this->_compile_order_by();

			// We have to strip the ORDER BY clause
			$sql = trim(substr($sql, 0, strrpos($sql, $orderby)));

			// Get the fields to select from our subquery, so that we can avoid CI_rownum appearing in the actual results
			if (count($this->qb_select) === 0)
			{
				$select = '*'; // Inevitable
			}
			else
			{
				// Use only field names and their aliases, everything else is out of our scope.
				$select = array();
				$field_regexp = ($this->_quoted_identifier)
					? '("[^\"]+")' : '(\[[^\]]+\])';
				for ($i = 0, $c = count($this->qb_select); $i < $c; $i++)
				{
					$select[] = preg_match('/(?:\s|\.)'.$field_regexp.'$/i', $this->qb_select[$i], $m)
						? $m[1] : $this->qb_select[$i];
				}
				$select = implode(', ', $select);
			}

			return 'SELECT '.$select." FROM (\n\n"
				.preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.trim($orderby).') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
				."\n\n) ".$this->escape_identifiers('CI_subquery')
				."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.($this->qb_offset + 1).' AND '.$limit;
		}

		return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
	}
211269
private static function getOperation(string $sql): string
    {
        // remove annotion and blank
        $sql = preg_replace('/\/\*[^\/]+\*\//', '', $sql);
        $sql = trim($sql);
        $sql = strtoupper($sql);

        if (strpos($sql, 'INSERT') === 0) {
            return self::RETURN_INSERTID;
        }

        if (strpos($sql, 'UPDATE') === 0 || strpos($sql, 'DELETE') === 0 || strpos($sql, 'REPLACE') === 0) {
            return self::RETURN_ROWS;
        }

        return self::RETURN_FETCH;
    }
1030536
public function realQuery($sql) {
		BLog::addToLog($this->logsuffix . ' Query: ' . $sql);
		$this->queries_count++;
		$r = $this->mysqli->real_query($sql);
		if ((DEBUG_MODE) && (empty($r))) {
			BLog::addToLog($this->logsuffix . ' query failed!', LL_ERROR);
			BLog::addToLog($this->logsuffix . ' query="' . $sql . '";', LL_ERROR);
			BLog::addToLog($this->logsuffix . ' query error=' . $this->lastError(), LL_ERROR);
		}
		return $r;
	}
577788
public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /**
             * @see Zend_Db_Adapter_Db2_Exception
             */
            // require_once 'Zend/Db/Adapter/Db2/Exception.php';
            throw new Zend_Db_Adapter_Db2_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /**
             * @see Zend_Db_Adapter_Db2_Exception
             */
            // require_once 'Zend/Db/Adapter/Db2/Exception.php';
            throw new Zend_Db_Adapter_Db2_Exception("LIMIT argument offset=$offset is not valid");
        }

        if ($offset == 0) {
            $limit_sql = $sql . " FETCH FIRST $count ROWS ONLY";
            return $limit_sql;
        }

        /**
         * DB2 does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*",
         * this puts an extra column into the query result set.
         */
        $limit_sql = "SELECT z2.*
            FROM (
                SELECT ROW_NUMBER() OVER() AS \"ZEND_DB_ROWNUM\", z1.*
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.zend_db_rownum BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql;
    }
1353343
public function applyLimit(&$sql, $offset, $limit)
    {
        $offset = (int) $offset;
        $limit = (int) $limit;

        if ($limit >= 0) {
            $sql .= ' LIMIT ' . ($offset > 0 ? $offset . ', ' : '') . $limit;
        } elseif ($offset > 0) {
            $sql .= ' LIMIT ' . $offset . ', 18446744073709551615';
        }
    }
1009585
private function getLimitSql(Criteria $criteria)
    {
        $limit  = $criteria->getMaxResults();
        $offset = $criteria->getFirstResult();
        if ($limit !== null || $offset !== null) {
            return $this->platform->modifyLimitQuery('', $limit, $offset ?? 0);
        }

        return '';
    }
112427
protected function getLimitString(): string
    {
        $statement = '';
        $limit     = $this->builder->getLimit();
        if (!$limit) {
            return $statement;
        }

        $isUpdateOrDelete = $this->isDelete() || $this->isUpdate();
        if ($isUpdateOrDelete && $limit['limit']) {
            return sprintf('LIMIT %d', $limit['limit']);
        }

        $size      = $limit['limit'];
        $offset    = $limit['offset'];
        $statement = sprintf('LIMIT %d,%d', $offset, $size);

        return $statement;
    }
527235
public function format($sql)
    {
        $this->reset();
        $tab = "\t";

        $originalTokens = $this->tokenizer->tokenize((string) $sql);
        $tokens = WhiteSpace::removeTokenWhitespace($originalTokens);

        foreach ($tokens as $i => $token) {
            $queryValue = $token[Tokenizer::TOKEN_VALUE];
            $this->indentation->increaseSpecialIndent()->increaseBlockIndent();
            $addedNewline = $this->newLine->addNewLineBreak($tab);

            if ($this->comment->stringHasCommentToken($token)) {
                $this->formattedSql = $this->comment->writeCommentBlock($token, $tab, $queryValue);
                continue;
            }

            if ($this->parentheses->getInlineParentheses()) {
                if ($this->parentheses->stringIsClosingParentheses($token)) {
                    $this->parentheses->writeInlineParenthesesBlock($tab, $queryValue);
                    continue;
                }
                $this->newLine->writeNewLineForLongCommaInlineValues($token);
                $this->inlineCount += \strlen($token[Tokenizer::TOKEN_VALUE]);
            }

            switch ($token) {
                case $this->parentheses->stringIsOpeningParentheses($token):
                    $tokens = $this->formatOpeningParenthesis($token, $i, $tokens, $originalTokens);
                    break;

                case $this->parentheses->stringIsClosingParentheses($token):
                    $this->indentation->decreaseIndentLevelUntilIndentTypeIsSpecial($this);
                    $this->newLine->addNewLineBeforeToken($addedNewline, $tab);
                    break;

                case $this->stringIsEndOfLimitClause($token):
                    $this->clauseLimit = false;
                    break;

                case $token[Tokenizer::TOKEN_VALUE] === ',' && false === $this->parentheses->getInlineParentheses():
                    $this->newLine->writeNewLineBecauseOfComma();
                    break;

                case Token::isTokenTypeReservedTopLevel($token):
                    $queryValue = $this->formatTokenTypeReservedTopLevel($addedNewline, $tab, $token, $queryValue);
                    break;

                case $this->newLine->isTokenTypeReservedNewLine($token):
                    $this->newLine->addNewLineBeforeToken($addedNewline, $tab);

                    if (WhiteSpace::tokenHasExtraWhiteSpaces($token)) {
                        $queryValue = \preg_replace('/\s+/', ' ', $queryValue);
                    }
                    break;
            }

            $this->formatBoundaryCharacterToken($token, $i, $tokens, $originalTokens);
            $this->formatWhiteSpaceToken($token, $queryValue);
            $this->formatDashToken($token, $i, $tokens);
        }

        return \trim(\str_replace(["\t", " \n"], [$this->tab, "\n"], $this->formattedSql))."\n";
    }
1121939
public function limit($sql, $start, $qty = null)
    {
        if (is_null($qty)) {
            $qty = 50;
        }

        if (stripos($sql, ' LIMIT ') === false) {
            $sql = $sql . " LIMIT x OFFSET y";
        }

        return preg_replace(
            '~(\s[Ll][Ii][Mm][Ii][Tt])\s.*?\s([Oo][Ff][Ff][Ss][Ee][Tt])\s.*~',
            '$1 ' . $qty .' $2 ' .$start,
            $sql
        );
    }
456368
public function select(SQLStatement $select): string
    {
        $sql = $select->getDistinct() ? 'SELECT DISTINCT ' : 'SELECT ';
        $sql .= $this->handleColumns($select->getColumns());
        $sql .= $this->handleInto($select->getIntoTable(), $select->getIntoDatabase());
        $sql .= ' FROM ';
        $sql .= $this->handleTables($select->getTables());
        $sql .= $this->handleJoins($select->getJoins());
        $sql .= $this->handleWheres($select->getWheres());
        $sql .= $this->handleGroupings($select->getGroupBy());
        $sql .= $this->handleOrderings($select->getOrder());
        $sql .= $this->handleHavings($select->getHaving());
        $sql .= $this->handleOffset($select->getOffset(), $select->getLimit());
        $sql .= $this->handleLimit($select->getLimit(), $select->getOffset());

        return $sql;
    }
1361597
public function sqlq($sql) {
    // $args = ['--db-prefix', '"' . $sql . '"'];
    $sql = strtr($sql, [
      "{" => '',
       "}" => ''
     ]);
    $output = trim($this->__call('sqlq', ['"' . $sql . '"']));
    $output = $this->cleanOutput($output);
    return $output;
  }
812558
public function buildLimitFragment(SQLSelect $query, array &$parameters)
    {
        $nl = $this->getSeparator();

        // Ensure limit is given
        $limit = $query->getLimit();
        if (empty($limit)) {
            return '';
        }

        // For literal values return this as the limit SQL
        if (!is_array($limit)) {
            return "{$nl}LIMIT $limit";
        }

        // Assert that the array version provides the 'limit' key
        if (!isset($limit['limit']) || !is_numeric($limit['limit'])) {
            throw new InvalidArgumentException(
                'DBQueryBuilder::buildLimitSQL(): Wrong format for $limit: ' . var_export($limit, true)
            );
        }

        // Format the array limit, given an optional start key
        $clause = "{$nl}LIMIT {$limit['limit']}";
        if (isset($limit['start']) && is_numeric($limit['start']) && $limit['start'] !== 0) {
            $clause .= " OFFSET {$limit['start']}";
        }
        return $clause;
    }
535773
public function limit($sql, $count, $offset = 0)
    {
        $count = intval($count);
        if ($count <= 0) {
            /**
             * @see Zend_Db_Adapter_Oracle_Exception
             */
            // require_once 'Zend/Db/Adapter/Oracle/Exception.php';
            throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument count=$count is not valid");
        }

        $offset = intval($offset);
        if ($offset < 0) {
            /**
             * @see Zend_Db_Adapter_Oracle_Exception
             */
            // require_once 'Zend/Db/Adapter/Oracle/Exception.php';
            throw new Zend_Db_Adapter_Oracle_Exception("LIMIT argument offset=$offset is not valid");
        }

        /**
         * Oracle does not implement the LIMIT clause as some RDBMS do.
         * We have to simulate it with subqueries and ROWNUM.
         * Unfortunately because we use the column wildcard "*",
         * this puts an extra column into the query result set.
         */
        $limit_sql = "SELECT z2.*
            FROM (
                SELECT z1.*, ROWNUM AS \"zend_db_rownum\"
                FROM (
                    " . $sql . "
                ) z1
            ) z2
            WHERE z2.\"zend_db_rownum\" BETWEEN " . ($offset+1) . " AND " . ($offset+$count);
        return $limit_sql;
    }
111465
protected function buildLimit() {
		if(!$this->limit && !$this->offset)
			return '';

		$driver = $this->db->getPDO()->getAttribute(\PDO::ATTR_DRIVER_NAME);
		if($driver === 'pgsql') {
			$limit = '';
			if($this->limit)
				$limit .= "\n".'LIMIT '.$this->limit;
			if($this->offset)
				$limit .= "\n".'OFFSET '.$this->offset;
		}
		else {
			$limit = "\n".'LIMIT ';
			if($this->offset) {
				$limit .= $this->offset;
				if($this->limit)
					$limit .= ', '.$this->limit;
				else
					$limit .= ', '.PHP_INT_MAX;
			}
			else
				$limit .= $this->limit;
		}

		return $limit;
	}
984751
public function executeQuery($query, $limit = 10)
    {
        $query = trim($query);

        if (strtoupper(substr($query, 0, 6)) !== "SELECT") {
            throw new SqlException(
                $this->translator->trans("Invalid SQL query. It must start with SELECT", [], IndexEngine::MESSAGE_DOMAIN)
            );
        }

        if (substr($query, -1) !== ";") {
            $query .= ";";
        }

        if (substr_count($query, ";") > 2) {
            throw new SqlException(
                $this->translator->trans("Invalid SQL query. It must only contain one query", [], IndexEngine::MESSAGE_DOMAIN)
            );
        }

        try {
            $stmt = $this->con->query($query);
            $stmt->execute();

            $data = $stmt->fetchAll(\PDO::FETCH_NAMED);

            if (count($data) > $limit) {
                $data = array_slice($data, 0, $limit);
            }

            return $data;
        } catch (\PDOException $e) {
            throw new SqlException(
                $this->translator->trans(
                    "Invalid SQL query: %exception",
                    ["%exception" => $e->getMessage()],
                    IndexEngine::MESSAGE_DOMAIN
                )
            );
        }
    }
280397
private function getFinalQuery(string $sql, array $params = []) : string
    {
        if (sizeof($params) == 0 || is_numeric(array_keys($params)[0])) {
            return $sql;
        }

        $split = preg_split('`([\\:\\!][A-Za-z0-9]+)`', $sql, -1, PREG_SPLIT_DELIM_CAPTURE);

        foreach ($split as $index => $item) {
            if (substr($item, 0, 1) == ':') {
                // ! is for unquote values
                $name = substr($item, 1);
                if (!array_key_exists($name, $params)) {
                    throw new QueryException(
                        $this,
                        $sql,
                        sprintf("Missing parameters '%s'", $name)
                    );
                }

                $split[$index] = $this->escape($params[$name]);
            } elseif (substr($item, 0, 1) == '!') {
                // ! is for unquote values
                $name = substr($item, 1);
                if (!array_key_exists($item, $params)) {
                    throw new QueryException(
                        $this,
                        $sql,
                        sprintf("Missing parameters '%s'", $name)
                    );
                }

                $split[$index] = $params[$item];
            }
        }

        return implode('', $split);
    }
395623
protected function applyLimit($sql){
		list($limit, $offset) = $this->stmt->getLimit();
		if((int) $limit < 1 && (int) $offset < 1)
			return $sql;

		switch($this->stmt->getConnection()->getDriver()->getDriverName()){
			case 'mysql':
			case 'pgsql':
			case 'sqlite':
			case 'sqlite2':
				if((int) $limit > 0){
					$sql .= "\nLIMIT " . (int) $limit;
					if((int) $offset > 0)
						$sql .= ' OFFSET ' . (int) $offset;
				}
				return $sql;

			case 'odbc':
			case 'mssql':
				if($offset < 1)
					return 'SELECT TOP ' . (int) $limit . " *\nFROM (\n\t"
						 . implode("\n\t", explode("\n", $sql)) . "\n)";

			default:
				throw new DriverException('PDO or selected driver does not allow apllying limit or offset.');
		}
	}