You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Since my PR on codeigniter4/CodeIgniter4 didn't responded yet, you need to touch the core class to make the limit working with SQL Server 2011 or earlier.
File need changes vendor/codeigniter4/framework/system/Database/SQLSRV/Builder.php
Find the _limit() protected method and override with following code:
/**
* Local implementation of limit
*/
protected function _limit(string $sql, bool $offsetIgnore = false): string
{
if (empty($this->QBOrderBy))
{
$this->QBOrderBy = ' ORDER BY (SELECT NULL) ';
$sql .= $this->QBOrderBy;
}
if (version_compare($this->db->getVersion(), '11', '>='))
{
if ($offsetIgnore)
{
$sql .= ' OFFSET 0 ';
}
else
{
$sql .= is_int($this->QBOffset) ? ' OFFSET ' . $this->QBOffset : ' OFFSET 0 ';
}
return $sql .= ' ROWS FETCH NEXT ' . $this->QBLimit . ' ROWS ONLY ';
}
$limit = $this->QBOffset + $this->QBLimit;
// An ORDER BY clause is required for ROW_NUMBER() to work
if ($this->QBOffset && ! empty($this->QBOrderBy))
{
$orderBy = $this->compileOrderBy();
// 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->QBSelect) === 0 OR strpos(implode(',', $this->QBSelect), '*') !== FALSE)
{
$select = '*'; // Inevitable
}
else
{
// Use only field names and their aliases, everything else is out of our scope.
$select = [];
$fieldRegexp = ($this->_quoted_identifier ? '("[^\"]+")' : '(\[[^\]]+\])');
foreach ($this->QBSelect as $i => $singleQBSelect) {
$select[] = (preg_match('/(?:\s|\.)' . $fieldRegexp . '$/i', $singleQBSelect, $m) ? $m[1] : $singleQBSelect);
}
$select = implode(', ', $select);
}
return 'SELECT ' . $select . " FROM (\n\n" . preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . trim($orderBy) . ') AS ' . $this->db->escapeIdentifiers('CI_rownum') . ', ', $sql) . "\n\n) " . $this->db->escapeIdentifiers('CI_subquery') . "\nWHERE " . $this->db->escapeIdentifiers('CI_rownum').' BETWEEN ' . ($this->QBOffset + 1) . ' AND ' . $limit;
}
return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP ' . $limit . ' ', $sql);
}
The text was updated successfully, but these errors were encountered:
Since my PR on codeigniter4/CodeIgniter4 didn't responded yet, you need to touch the core class to make the limit working with SQL Server 2011 or earlier.
File need changes
vendor/codeigniter4/framework/system/Database/SQLSRV/Builder.php
Find the
_limit()
protected method and override with following code:The text was updated successfully, but these errors were encountered: