MSSQL Limit - A working "Fix" .

[eluser]Arasoi[/eluser]
Having reached a point of frustration here to fore unknown in the world of man. I decided to "fix" the Limit function in the MSSQL driver so it would function as it does in MySql. This was to help me deal with most ECMA script grids which tend to rely on pagination rather heavily. The data shuffle is handled server side so it does not return massive result sets, though it has one caveat, it requires an "ORDER BY" be set. Other then that it does it's job. Feel free to make any corrections or tweaks to performance/proper use.

Simply replace the Limit function in mssql_driver.php starts at line 630.

Code:

/**
     * Limit string
     *
     * Generates a platform-specific LIMIT clause
     *
     * @access    public
     * @param    string    the sql query string
     * @param    integer    the number of rows to limit the query to
     * @param    integer    the offset value
     * @return    string
     */
    function _limit($sql, $offset, $limit)
    {
        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 . ") AND (".$limit.")";
            
        return     $NewSQL;
    }

from here use as you would with any other active record query.

Code:

$this->db->limit($Start, $End);
    $this->db->where('DeptID',$DeptID);
    $this->db->order_by('LastName','asc');
    $query = $this->db->get('taku_User_view');