Welcome Guest, Not a member yet? Register   Sign In
MSSQL Limit - A working "Fix" .
#6

[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');


Messages In This Thread
MSSQL Limit - A working "Fix" . - by El Forum - 07-13-2010, 08:03 AM
MSSQL Limit - A working "Fix" . - by El Forum - 07-14-2010, 08:55 AM
MSSQL Limit - A working "Fix" . - by El Forum - 07-14-2010, 10:43 AM
MSSQL Limit - A working "Fix" . - by El Forum - 07-15-2010, 01:34 PM
MSSQL Limit - A working "Fix" . - by El Forum - 02-18-2011, 02:38 PM
MSSQL Limit - A working "Fix" . - by El Forum - 02-28-2011, 08:01 PM
MSSQL Limit - A working "Fix" . - by El Forum - 08-10-2011, 05:46 AM
MSSQL Limit - A working "Fix" . - by El Forum - 03-07-2012, 02:10 PM
MSSQL Limit - A working "Fix" . - by El Forum - 09-11-2012, 06:22 PM
MSSQL Limit - A working "Fix" . - by El Forum - 06-12-2013, 08:45 AM



Theme © iAndrew 2016 - Forum software by © MyBB