[eluser]gusa[/eluser]
hi everybody!
tired of implementing an ad-hoc solution every time i want to paginate under mssql i've decided to overwrite codeigniter's mssql driver. with this solution it's possible to use such an impossible thing as active record plus pagination with mssql server.
the steps are:
1) edit database/drivers/mssql_driver.php and add these methods:
Code:
function _get_pkeys($table) {
$stmt = @mssql_init('SP_PKEYS', $this->conn_id);
@mssql_bind($stmt, '@table_name', $table, SQLVARCHAR, false);
$rs = @mssql_execute($stmt);
$n = @mssql_num_rows($rs);
if ($n == 0) {
return $this->display_error('db_unsuported_feature');
}
$result = array();
while ($p = mssql_fetch_assoc($rs)) {
$result[] = $p['COLUMN_NAME'];
}
return $result;
}
function _compile_select()
{
if (is_numeric($this->ar_limit)) {
$limit = $this->ar_limit;
$offset = $this->ar_offset;
$pkeys = $this->_get_pkeys($this->ar_from[0]);
$keys = implode(', ', $pkeys);
if (count($pkeys) > 1) {
// convert many keys into a unique pk
foreach ($pkeys as &$pk) {
$pk = 'convert(varchar, '.$pk.')';
}
$key = implode("+':'+", $pkeys);
} else {
$key = $pkeys[0];
}
// select clause
$select = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
$select .= $key.', ';
$select .= (count($this->ar_select) == 0) ? '*' : implode(', ', $this->ar_select);
// from clause
if (count($this->ar_from) > 0)
{
$from = "FROM ".implode(', ', $this->ar_from);
}
// join clause
if (count($this->ar_join) > 0)
{
$join = implode("\n", $this->ar_join);
}
// where clause
if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
{
$where = "WHERE ";
} else {
$where = "WHERE 1=1 ";
}
$where .= implode("\n", $this->ar_where);
if (count($this->ar_like) > 0)
{
if (count($this->ar_where) > 0)
{
$where .= " AND ";
}
$where .= implode("\n", $this->ar_like);
}
// order by clause
if (count($this->ar_orderby) > 0)
{
$orderby = "ORDER BY ".implode(', ', $this->ar_orderby);
if ($this->ar_order !== FALSE)
{
$orderby .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC';
}
} else {
$orderby = 'ORDER BY '.$keys.' ASC';
}
// the hack
$offset--;
$top = $limit + $offset;
$sql = "
$select
$from
$join
WHERE $key IN (
SELECT TOP $limit $key
$from
$join
$where AND $key NOT IN (
SELECT TOP $offset $key
$from
$join
$where
ORDER BY $keys
)
ORDER BY $keys ASC
)
$orderby";
} else {
$sql = parent::_compile_select();
}
return $sql;
}
ready! now you can make your active record query as always (with some limitations):
Code:
$this->db->select('blah');
$this->db->from('yara');
...
$this->db->limit(10, 20);
limitations:
1) the first table in the
from clause must have a primary key defined.
2) group by clause and having clause are discarded, so if you want pagination with group by and having go back to the traditional way.
good luck!