pagination incorrect in oci8 driver oracle - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5) +--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11) +--- Thread: pagination incorrect in oci8 driver oracle (/showthread.php?tid=71944) |
pagination incorrect in oci8 driver oracle - Miko - 10-15-2018 Hello, I use codeigniter since few week and i just see a mistake... with oracle < 12.1 the pagination is incorrect : In system/database/driver/oci8/oci8_driver.php you have to modify function _limit: /** * LIMIT * * Generates a platform-specific LIMIT clause * * @param string $sql SQL Query * @return string */ protected function _limit($sql) { if (version_compare($this->version(), '12.1', '>=')) { // OFFSET-FETCH can be used only with the ORDER BY clause empty($this->qb_orderby) && $sql .= ' ORDER BY 1'; return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; } $this->limit_used = TRUE; return 'SELECT * FROM (SELECT inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.($this->qb_offset + $this->qb_limit + 1).')' .($this->qb_offset ? ' WHERE rnum >= '.($this->qb_offset + 1) : ''); } Calcul of limit is not exact in this case. With this code it's ok : /** * LIMIT * * Generates a platform-specific LIMIT clause * * @param string $sql SQL Query * @return string */ protected function _limit($sql) { if (version_compare($this->version(), '12.1', '>=')) { // OFFSET-FETCH can be used only with the ORDER BY clause empty($this->qb_orderby) && $sql .= ' ORDER BY 1'; return $sql.' OFFSET '.(int) $this->qb_offset.' ROWS FETCH NEXT '.$this->qb_limit.' ROWS ONLY'; } if($this->qb_offset=="") $this->qb_offset=1; $this->limit_used = TRUE; return 'SELECT * FROM (SELECT /*+ FIRST_ROWS(n) */ inner_query.*, rownum rnum FROM ('.$sql.') inner_query WHERE rownum < '.(($this->qb_offset * $this->qb_limit)+ 1).')' .($this->qb_offset ? ' WHERE rnum >= '.(($this->qb_offset - 1)*$this->qb_limit+1) : ''); } Have fun & code Nico (Miko) |