MSSQL Limit - A working "Fix" .

[eluser]Kyle Johnson[/eluser]
I modified the script slightly as well as it did not allow for simple ->limit(1) chains without having an order_by clause as well.

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, $limit, $offset)
    {
            /* Original
             *
             * $i = $limit + $offset;
             * return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
             *
             */

            if ($offset === FALSE) { // If called as $this->db->limit(100);
                $i = $limit;
                return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
            }
            
            if (count($this->ar_orderby) > 0) // If called as $this->db->limit(100,200);
            {
                $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;
            } else {
                echo 'Query must have an order_by clause in order to be offset.';
            }
        }

Now we can use simple limits in addition to offsets.

Simple limit:

Code:

$limit = 10;
return $this->db->select('[Customer],[CustomerTracking]')
                ->where('[Customer]', 'Test Customer')
                ->order_by('[CustomerTracking]', 'ASC')
                ->limit($limit)
                ->get('vRequests');

With offset:

Code:

$limit = 10;
$offset = 200;
return $this->db->select('[Customer],[CustomerTracking]')
                ->where('[Customer]', 'Test Customer')
                ->order_by('[CustomerTracking]', 'ASC')
                ->limit($limit, $offset)
                ->get('vRequests');