count all results pagination problem

[eluser]Gilles_T[/eluser]
Hi,

I struggled a while on this last year with a 1.7 CI version, found a fix and forgot about it. I just upgraded to 2.02 yesterday and came back on the point since my fix in the DB_active_rec has gone with the upgrade. I looked again since I thought the new version would come with a solution for it. I fear it's not the case, so after some search, I have no other way than putting back my fix in the DB_active_rec.

My proposition is the following : mimic the SQL_CALC_FOUND_ROWS() with integrating a

Code:

$this->db->SQL_CALC_FOUND_ROWS();

called before the ->select() and so on

and then after the ->get(), call a :

Code:

$count = $this->db->query('SELECT FOUND_ROWS() as cnt');

as we would do with a standard PHP SQL request.

Fix goes in 3 parts of the DB_actice_rec :
1/ In the var definition as :

Code:

var $ar_SQL_CALC_FOUND_ROWS            = FALSE;

2/ In the _compile_select function :

Code:

function _compile_select($select_override = FALSE)
    {
        // Combine any cached components with the current statements
        $this->_merge_cache();

        // ----------------------------------------------------------------
        
        // Write the "select" portion of the query

        if ($select_override !== FALSE)
        {
            $sql = $select_override;
        }
        else
        {
            $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
            //------- Patch #GT# --------
            $sql = ( ! $this->ar_SQL_CALC_FOUND_ROWS) ? 'SELECT ' : 'SELECT SQL_CALC_FOUND_ROWS ';        
            
            if (count($this->ar_select) == 0)
            {
                $sql .= '*';        
            }
            else
            {                
                // Cycle through the "select" portion of the query and prep each column name.
                // The reason we protect identifiers here rather then in the select() function
                // is because until the user calls the from() function we don't know if there are aliases
                foreach ($this->ar_select as $key => $val)
                {
                    $this->ar_select[$key] = $this->_protect_identifiers($val);
                }
                
                $sql .= implode(', ', $this->ar_select);
            }
        }

3/ The new SQL_CALC_FOUND_ROWS function for the DB_active_rec class

Code:

function SQL_CALC_FOUND_ROWS($val = TRUE)
    {
        $this->ar_SQL_CALC_FOUND_ROWS = (is_bool($val)) ? $val : TRUE;
        return $this;
    }

I hope this can help someone.

A message to the CI development team to integrate this in a next release, or may be to better explain the usage of the count_all_result function that sounds like doing such a thing. The previous answer to the topic is a clever one as well and could be part of a next release.

Have a good day