[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.
