用户
 找回密码
 入住 CI 中国社区
搜索
CodeIgniter 中国开发者社区»论坛 › CodeIgniter 开发 › 代码共享与下载 › MSSQL limit 修正
返回列表 发新帖
查看: 5923|回复: 5
收起左侧

[修补 Patch] MSSQL limit 修正

[复制链接]
发表于 2012-11-2 13:31:39 | 显示全部楼层 |阅读模式
CI  2.0版本的limit在MSSQL或Sqlsrv下不起作用，看了CI 3.0 在github上develop分支对limit的修正也是存在问题跑不起来的，不知为何错误的mssql语句也会push上去，这里分享一下我修改后的函数，在CI 3 上测试通过

覆盖database/drivers/mssql/mssql_driver.php 以及 database/drivers/sqlsrv/sqlsrv _driver.php 里的_limit函数。

PHP复制代码
protected function _limit($sql, $limit, $offset)        {
                if ( ! $offset)
                {
                        return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$limit.' ', $sql);
                }
 
                // As of SQL Server 2012 (11.0.*) OFFSET is supported
                if (version_compare($this->version(), '11', '>='))
                {
                        return $sql.' OFFSET '.(int) $offset.' ROWS FETCH NEXT '.(int) $limit.' ROWS ONLY';
                }
 
                // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
                if (version_compare($this->version(), '9', '>='))
                {
                        $orderby = 'ORDER BY ' . (empty($this->qb_orderby) ? 'getdate()' : implode(', ', $this->qb_orderby));
                        $sql = empty($this->qb_orderby) ? $sql : trim(substr($sql, 0, strrpos($sql, $orderby)));
 
                        return 'SELECT '.(count($this->qb_select) === 0 ? '*' : implode(', ', $this->qb_select))." FROM (\n"
                                .preg_replace('/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER('.$orderby.') AS '.$this->escape_identifiers('CI_rownum').', ', $sql)
                                ."\n) AS ".$this->escape_identifiers('CI_subquery')
                                ."\nWHERE ".$this->escape_identifiers('CI_rownum').' BETWEEN '.((int) $offset + 1).' AND '. ($offset + $limit);
                }
 
                // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
                return $sql;
        }
复制代码

修正

相关帖子
回复

使用道具 举报

发表于 2012-11-27 03:21:03 | 显示全部楼层
您这个方法在CI 2.1.3下没法用，qb_orderby这个属性不存在。。
回复 支持 反对

使用道具 举报

发表于 2012-11-27 14:39:05 | 显示全部楼层
本帖最后由 CurveSoft 于 2012-11-27 14:40 编辑

哈哈，感谢楼主，我稍微修改了一下，2.1.3可以用了！太感谢了。

以下是我修改的代码，修改的地方主要是把$this->qb_orderby改为$this->ar_orderby，$this->escape_identifiers改为$this->_escape_identifiers，就O了，哈哈！

PHP复制代码
 
    function _limit($sql, $limit, $offset) {
        // $i = $limit + $offset;
 
        // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
        if (! $offset) {
            return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql );
        }
 
        // As of SQL Server 2012 (11.0.*) OFFSET is supported
        if (version_compare ( $this->version (), '11', '>=' )) {
            return $sql . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
        }
 
        // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
        if (version_compare ( $this->version (), '9', '>=' )) {
            $orderby = 'ORDER BY ' . (empty ( $this->ar_orderby ) ? 'getdate()' : implode ( ', ', $this->ar_orderby ));
            $sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, strrpos ( $sql, $orderby ) ) );
 
            return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
        }
 
        // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
        return $sql;
    }        
 
复制代码




回复 支持 反对

使用道具 举报

发表于 2012-11-27 16:10:26 | 显示全部楼层
本帖最后由 CurveSoft 于 2012-11-27 16:11 编辑

又修改了一下，楼主原来代码中
PHP复制代码
$sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, strrpos ( $sql, $orderby ) ) );
复制代码

这段似乎本意是$sql中原来有ORDER BY的话就移除，因为MSSQL 2005不允许子查询中有ORDER BY，但是如果$sql里面原来没有ORDER BY子句的话，这样做整个SQL就会被清空了
于是我改了一下，如下：
PHP复制代码
 
    function _limit($sql, $limit, $offset) {
        // $i = $limit + $offset;
 
        // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
        if (! $offset) {
            return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql );
        }
 
        // As of SQL Server 2012 (11.0.*) OFFSET is supported
        if (version_compare ( $this->version (), '11', '>=' )) {
            return $sql . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
        }
 
        // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
        if (version_compare ( $this->version (), '9', '>=' )) {
            $orderby = 'ORDER BY ' . (empty ( $this->ar_orderby ) ? 'getdate()' : implode ( ', ', $this->ar_orderby ));
            $p = strrpos ( $sql, 'order by' );
            if ($p) {
                $sql = empty ( $this->ar_orderby ) ? $sql : trim ( substr ( $sql, 0, $p ) );
            }
 
            return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
        }
 
        // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
        return $sql;
    }
 
复制代码

不知道这样对不对，至少在我的应用里面不会有问题，因为我的查询SQL里面不会在第一步SQL里写ORDER BY的。
回复 支持 反对

使用道具 举报

发表于 2012-11-27 16:47:11 | 显示全部楼层
好吧，最终版本来了

PHP复制代码
 
    function _limit($sql, $limit, $offset) {
        // $i = $limit + $offset;
 
        // return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $i . ' ', $sql );
        $orderby = ' ORDER BY getdate()';
        if (! empty ( $this->ar_orderby )) {
            $p = strrpos ( $sql, 'order by' );
            if ($p) {
                $sql = trim ( substr ( $sql, 0, $p ) );
            }
            $orderby = ' ORDER BY ' . implode ( ', ', $this->ar_orderby );
        }
 
        if (! $offset) {
            return preg_replace ( '/(^\SELECT (DISTINCT)?)/i', '\\1 TOP ' . $limit . ' ', $sql . $orderby );
        }
 
        // As of SQL Server 2012 (11.0.*) OFFSET is supported
        if (version_compare ( $this->version (), '11', '>=' )) {
            return $sql . $orderby . ' OFFSET ' . ( int ) $offset . ' ROWS FETCH NEXT ' . ( int ) $limit . ' ROWS ONLY';
        }
 
        // As of SQL Server 2005 (9.0.*) ROW_NUMBER is supported
        if (version_compare ( $this->version (), '9', '>=' )) {
 
            return 'SELECT ' . (count ( $this->ar_select ) === 0 ? '*' : implode ( ', ', $this->ar_select )) . " FROM (\n" . preg_replace ( '/^(SELECT( DISTINCT)?)/i', '\\1 ROW_NUMBER() OVER(' . $orderby . ') AS ' . $this->_escape_identifiers ( 'CI_rownum' ) . ', ', $sql ) . "\n) AS " . $this->_escape_identifiers ( 'CI_subquery' ) . "\nWHERE " . $this->_escape_identifiers ( 'CI_rownum' ) . ' BETWEEN ' . (( int ) $offset + 1) . ' AND ' . ($offset + $limit);
        }
 
        // TODO: Lower than 9.0 version (SQL Server 2000) need you to write
        return $sql;
    }
 
 
 
复制代码

回复 支持 反对

使用道具 举报

zeek
发表于 2014-1-22 15:07:28 | 显示全部楼层
本帖最后由 zeek 于 2014-1-22 15:08 编辑

修改成这样吧，可以完美运行
PHP复制代码
function _limit($sql, $limit, $offset)    {
        if (count($this->ar_orderby) > 0)
        {
            $OrderBy  = "ORDER BY ";
            $OrderBy .= implode(', ', $this->ar_orderby);
 
            if ($this->ar_order !== FALSE)
            {
                $OrderBy .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC';
            }
        }
 
        $sql = preg_replace('/(\\'. $OrderBy .'\n?)/i','', $sql);
        $sql = preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 row_number() OVER ('.$OrderBy.') AS rownum, ', $sql);
 
        $NewSQL = "SELECT * \nFROM (\n" . $sql . ") AS A \nWHERE A.rownum BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")";
 
        return     $NewSQL;
    }
复制代码
回复 支持 反对

使用道具 举报

返回列表 发新帖

本版积分规则