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

kalen

1 posts

Hey Gang Thanks for this thread.

I made a few mods to the function above by Kyle to accommodate the following:

1. No OrderBy column name in the select query
2. No specific ColumnName in the Select Query, i.e. a wildcard ‘Select *’

This should basically let you browse rows with a statement like the following:

// Active Record Query
$this->db->limit($limit, $offset);
$query = $this->db->get($table);

Where the limit and offset can be set without an accompanying orderBy statement. Its kinda hacky so feel free sure modify or disregard.

/**
 * 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 
 */
 
/* ORIGINAL FUNCTION COMMENTED OUT
function _limit($sql, $limit, $offset)
{
 $i = $limit + $offset;
 return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
}
*/
 
// Modified as per this thread: <a href="http://ellislab.com/forums/viewthread/160626/">http://ellislab.com/forums/viewthread/160626/</a>

protected function _limit($sql, $limit, $offset)
{
 if($offset === FALSE) 
 {
  // Do simple limit if no offset
  $i = $limit + $offset;
  return preg_replace('/(^\SELECT (DISTINCT)?)/i','\\1 TOP '.$i.' ', $sql);
 }
 else
 {
  if(!$this->ar_orderby)
  {
   // We do not have an orderBy column set and do not have the tableName
   // here, so grab the table name from the $sql statement by regex and 
   // then grab the first column of the tableName in the $sql statement
   // from the schema and let that be the orderBy column. Phew.
   $match_pattern = '/(.*FROM )/s';
   $match_replacement = '';
   $table = preg_replace($match_pattern, $match_replacement, $sql);
   $orderBy  = "ORDER BY (SELECT [COLUMN_NAME] FROM [information_schema].[columns] WHERE [TABLE_NAME] = '".$table."' AND [ORDINAL_POSITION] = 1)";
  }
  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);

  if(!$this->ar_orderby)
  {
   // No ColumnName so do a wildcard   
   $columns = '*';
  }
  else
  {
   $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;
 }
} 
// --------------------------------------------------------------------

Haven’t tested this in production yet but is working for me at the moment.

Thanks again for the thread.

Thread