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

[eluser]Arasoi[/eluser]
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.
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, $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.


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

[eluser]Unknown[/eluser]
hi Arasoi, thanks for the fix. Smile

I think we may need to change it like this:
Code:
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:
Code:
$this->db->limit($limit, $offset);
#3

[eluser]Arasoi[/eluser]
Ahh yes that is a possable changeSmile 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 Big Grin
#4

[eluser]gigas10[/eluser]
Does not work with MSSQL 2000, row_number is not a recognized function name
#5

[eluser]tedroche[/eluser]
Works great with SQL Server 2008!
#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');
#7

[eluser]Flemming[/eluser]
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

[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;
  }
}
#9

[eluser]Beertastic[/eluser]
Can I ask a stupid question?
Why is this not in a CI release?
Seems ideal for all of us.

I'll shut up now...
#10

[eluser]Unknown[/eluser]
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:

Code:
// 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.

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

/* 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: http://ellislab.com/forums/viewthread/160626/

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.




Theme © iAndrew 2016 - Forum software by © MyBB