Active Record: count_all_results() problem

[eluser]Unknown[/eluser]
I'm trying to get pagination working. I'm querying my products table with a join and a where clause. I am sticking to active record style - but need to get the total results of my query - so I can pass it to the $config['total_rows'] of the pagination class.

so in my model:

Code:

$this->db->select('products.name, products.image, products.slug, products.price');
        $this->db->from('products');
        $this->db->join('categories', 'products.cat_id = categories.id');
        $this->db->where('categories.slug', $slug);
        $this->productsCount = $this->db->count_all_results() //this is where it's crapping out
        $this->db->limit($limit, $start);        
        $query = $this->db->get();        
        
       return $query->result_array();

I get a database error:

Error Number: 1096
No tables used
SELECT * LIMIT 9, 3
Filename: C:\xampp\htdocs\restaurant\system\database\DB_driver.php
Line Number: 330

if I simply echo the $this->db->count_all_results() and exit - I get the correct # of rows. If I move it below the limit clause - I get incorrect number of rows.

So what am I doing wrong? I want to stick to active record as much as possible.

thanks in advance...

[eluser]Otemu[/eluser]
Hi,

I don't think you can use count_all_results() when using an offset, test without using an offset, maybe use a separate query to get the total rows and then pass this amount to your current model function.

Hope that helps

[eluser]Aken[/eluser]
count_all_results() runs the query and then resets active record. You'll need to specify all of your from/join/where stuff again to run the actual results portion of your query.

[eluser]GDmac - expocom[/eluser]
Edit: any get or count resets Active Record (e.g. your select), however...
Active record allows you to "cache" your ellaborate query construction :-)
http://ellislab.com/codeigniter/user-gui...ml#caching

this should work:

Code:

$this->db->start_cache();

$this->db
   ->select('products.name, products.image, products.slug, products.price')
   ->from('products')
   ->join('categories', 'products.cat_id = categories.id')
   ->where('categories.slug', $slug);

$this->db->stop_cache();

$this->productsCount = $this->db->count_all_results();

// new query stuff will be merged with the cached ones :-)
$this->db->limit($limit, $start);        
$query = $this->db->get();        

// when done, don't forget to flush the cache, else
// your query stuff will be merged with next queries ...
$this->db->flush_cache();

[eluser]TheFuzzy0ne[/eluser]
That would be fantastic if it did work, but it won't. The caching mechanism only caches the actual results, not the query itself. The result is stored in a text file, and named using the MD5 hash of the query that created it.

[eluser]GDmac - expocom[/eluser]
@theFuzzyOne Have you tried it? Yes, this does work.

This is Active record caching $this->db->start_cache()
...enables you to save (or "cache") certain parts of your queries for reuse...
http://ellislab.com/codeigniter/user-gui...ml#caching

This is not Database Caching $this->db->cache_on()
...the query result object will be serialized and stored in a text file...
http://ellislab.com/codeigniter/user-gui...ching.html

[eluser]TheFuzzy0ne[/eluser]
Sorry about that! I didn't even realise there were two different ways to do database caching.

Thanks for clearing that up.