MSSQL Limit - A working "Fix" . | Libraries & Helpers

#1 / Jul 13, 2010 11:03am

Arasoi

2 posts

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.

/**
     * 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.

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

#2 / Jul 14, 2010 11:55am

yangh

1 posts

hi Arasoi, thanks for the fix. 😊

I think we may need to change it like this:

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;
    }

coz we often call db->limit with limit and offset params:

$this->db->limit($limit, $offset);

#3 / Jul 14, 2010 1:43pm

Arasoi
2 posts

Ahh yes that is a possable change:) I was doing that math before hand so I could adjust the offset as needed for each case. A good example is I have one client that likes to see the last item on the previous page as the first item on the next, but adjust as you need :D
#4 / Jul 15, 2010 4:34pm

gigas10
88 posts

Does not work with MSSQL 2000, row_number is not a recognized function name
#5 / Feb 18, 2011 4:38pm

tedroche
8 posts

Works great with SQL Server 2008!

#6 / Feb 28, 2011 10:01pm

Kyle Johnson

83 posts

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.

/**
     * 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:

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

With offset:

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

#7 / Aug 10, 2011 8:46am

Flemming
270 posts

Very relieved to have found this, thanks for writing it!!!!!! I can confirm that this works with CI 2.0 with MSSQL 2008. Fantastic! 😊

#8 / Dec 06, 2011 3:58pm

Kyle Johnson

83 posts

Update for the MSSQL statement.

It now runs just a little bit smoother, and includes Denali’s offset/pagination feature.

Also, I included the version check for 9.0.00 (MSSQL 2005) of SQL because before that, there is no good way of paginating results, or at least not one I feel like finding 😛

As far as I can tell this works in every scenario I’ve thrown at it with one stipulation. You MUST have an “ORDER BY” to have an offset. If you only have a single limit, then it works just fine.

/**
  * 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)
 {
  if($offset === FALSE || version_compare($this->version(), "9.0.00", "<=")) {
   // do simple limit if no offset, or version is earlier than 2005
   $i = $limit + $offset;

   return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
  } else {
   if(version_compare($this->version(), "11.0.00", ">=")) {
    // for Denali's new feature
    return $sql . "OFFSET " . $offset . " ROWS FETCH NEXT " . $limit . " ROWS ONLY";
   } else {
    {
     $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 CI_offset_row_number, ', $sql);
      
     $newSQL = "SELECT * \nFROM (\n" . $sql . ") AS A \nWHERE A.CI_offset_row_number BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")";
     return     $newSQL;
    }
   }
  }
 }

#9 / Dec 06, 2011 11:40pm

CroNiX
4713 posts

Are you guys submitting these fixes to github? If not, I seriously doubt they will find this.
#10 / Dec 07, 2011 12:17pm

Kyle Johnson
83 posts

I did submit it as a comment to the person who revamped the drivers for sqlsrv in the last iteration. But now when I go back and look, I don’t see the comment… https://bitbucket.org/ellislab/codeigniter-reactor/pull-request/11/revising-the-sqlsrv-driver-for-windows

Frustrating.
#11 / Dec 07, 2011 12:33pm

CroNiX
4713 posts

They moved to Github so I don’t know if they are actively looking at bitbucket.
https://github.com/EllisLab/CodeIgniter/issues?sort=created&direction=desc&state=open&page=1
#12 / Dec 07, 2011 10:00pm

Kyle Johnson
83 posts

This is a new change I’m guessing?? Or is the bitbucket just some obscure side repository? Because that has commits on it from this year as well.

Either way, I will find a place to post it on github as well.
#13 / Dec 07, 2011 10:12pm

CroNiX
4713 posts

They switched in August.
#14 / Dec 07, 2011 10:19pm

Kyle Johnson
83 posts

I’ve been out of the loop here for a while.

Thank you. 😊

#15 / Mar 07, 2012 4:10pm

Kyle Johnson

83 posts

New update with this.

Couldn’t get it to “work” with the Datatables plugin, but it was working, just the SELECT * was adding an additional column that the DataTables was using.

I am now specifically passing the columns back to the user that they are using with active record.

/**
  * Limit string
  *
  * Generates a platform-specific LIMIT clause
  *
  * @param string the sql query string
  * @param integer the number of rows to limit the query to
  * @param integer the offset value
  * @return string
  */
 protected function _limit($sql, $limit, $offset)
 {
  //return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.($limit + $offset).' ', $sql);
  if($offset === FALSE) {
   // do simple limit if no offset
   $i = $limit + $offset;
   return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
  } else {
   $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 CI_offset_row_number, ', $sql);
  
   $columns = implode(',',$this->ar_select);
   $newSQL = "SELECT " . $columns . " \nFROM (\n" . $sql . ") AS A \nWHERE A.CI_offset_row_number BETWEEN (" .($offset + 1) . ") AND (".($offset + $limit).")";
   return     $newSQL;
  }
 }

Thread