MSSQL Limit - A working "Fix" .

[eluser]Kyle Johnson[/eluser]
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.

Code:

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