select after count

[eluser]Unknown[/eluser]
Hi everyone!
I have the following problem, which i could solve by a simple hack , but i don't want to do it this way.
I'm building results for a complex search form, so after each active record where, in the end i am in the situation in which:
1) i need the count for all the results
2) i want to select only the current set (limit)
The problem is that, after $this->db->count_all_results(); the db object is reseted, so i would have to rebuild the query, which i don't like. (after that i add the limit and use $this->db->get() for the results).
Any suggestions?

Thanks!

[eluser]xwero[/eluser]
from version 1.6.1 there is a cache functionality for the AR library to remember previously set variables that build the sql statement

[eluser]Armchair Samurai[/eluser]
Just to give a quick example of what xwero's talking about:

Code:

$data = new stdClass;

$this->db->start_cache();
    $this->db->from('foo x');
    $this->db->join('bar y', 'x.id = y.id');
    $this->db->where('x.column', $var);
$this->db->stop_cache();
$data->total = $this->db->count_all_results();

$this->db->select('x.ralph, x.wiggum, y.clancy');
$this->db->order_by('x.id', 'asc');
$this->db->limit($limit, $offset);
$query = $this->db->get();

$data->result = $query->result();

$this->db->flush_cache();
return $data;

Just be sure to use flush_cache() after you finish or the system will continue to use the cached query elements on other functions... I found this out the hard way.

[eluser]Unknown[/eluser]
Ok, it worked quite nice this way.
Thanks everyone!

[eluser]kjackson7_93[/eluser]
If you want the count of the number of rows that would have been returned if you didn't have the limit clause, you can do:

Code:

MODEL...
function getNotes($offset=0,$row_count=10){
    $this->db->select("SQL_CALC_FOUND_ROWS board_notes.*");
    $board_notes = $this->db->get('board_notes');

    $this->db->select("FOUND_ROWS() as PostNum");
    $rRes2 = $this->db->get();
    $iPostNum = $rRes2->row_array();
    $this->_setFoundRows($iPostNum['PostNum']);

    return $board_notes->result();
}
    
function _setFoundRows($found_rows){
    $this->FoundRows = $found_rows;
}
    
function getFoundRows(){
    return $this->FoundRows;
}

CONTROLLER...
    $notes['offset'] = (int) $this->uri->segment(3, 0);
    $notes['notes_list'] = $this->Notes_model->getNotes($notes['offset'],$paging['per_page']);
    $notes['found_rows'] = $this->Notes_model->getFoundRows();

Look at the mysql docs for SQL_CALC_FOUND_ROWS.

[eluser]louis w[/eluser]
[quote author="Armchair Samurai" date="1204664601"]Just to give a quick example of what xwero's talking about:

Code:

$data = new stdClass;

$this->db->start_cache();
    $this->db->from('foo x');
    $this->db->join('bar y', 'x.id = y.id');
    $this->db->where('x.column', $var);
$this->db->stop_cache();
$data->total = $this->db->count_all_results();

$this->db->select('x.ralph, x.wiggum, y.clancy');
$this->db->order_by('x.id', 'asc');
$this->db->limit($limit, $offset);
$query = $this->db->get();

$data->result = $query->result();

$this->db->flush_cache();
return $data;

[/quote]

Does the second db->select inherit the from and where from the first one?

Not 100% sure how this works, looks very cool thou.

[eluser]BobbyB[/eluser]
[quote author="Armchair Samurai" date="1204664601"]Just to give a quick example of what xwero's talking about:

Code:

$data = new stdClass;

$this->db->start_cache();
    $this->db->from('foo x');
    $this->db->join('bar y', 'x.id = y.id');
    $this->db->where('x.column', $var);
$this->db->stop_cache();
$data->total = $this->db->count_all_results();

$this->db->select('x.ralph, x.wiggum, y.clancy');
$this->db->order_by('x.id', 'asc');
$this->db->limit($limit, $offset);
$query = $this->db->get();

$data->result = $query->result();

$this->db->flush_cache();
return $data;

Just be sure to use flush_cache() after you finish or the system will continue to use the cached query elements on other functions... I found this out the hard way.[/quote]



You are a genius! :-)