CodeIgniter Forums

Full Version: pagination incorrect in oci8 driver oracle
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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 Wink
Nico (Miko)