Welcome Guest, Not a member yet? Register   Sign In
active record query caching does not work like in the user guide
#1

Hi
i run with CI-2.2.1 on a 1and1 "unlimited" linux web hosting, everything is fine until now, i'm very happy with CI.
i wanted to try query caching to save some lines in my code but query caching creates a session_id field in my query :

my table is :
Code:
mysql> describe ap_client ;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| email    | varchar(128) | NO   |     | NULL    |                |
| civilite | varchar(5)   | NO   |     |         |                |
| nom      | varchar(128) | NO   |     | NULL    |                |
| prenom   | varchar(128) | NO   |     | NULL    |                |
| tel      | varchar(18)  | NO   |     | NULL    |                |
| rs       | varchar(255) | NO   |     | NULL    |                |
| idtarif  | int(11)      | NO   |     | 0       |                |
| deleted  | tinyint(4)   | NO   |     | 0       |                |
+----------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

the php is :
PHP Code:
$this->db->start_cache() ;
$this->db->select('*')->from('client')->order_by($this->admintab->order_by()) ; 
$this->db->stop_cache() ;
$count $this->db->count_all_results() ;

$paginConfig = array(
    
'base_url' => base_url('/clients/page/'),
    
'total_rows' => $count,
    
'per_page' => 20,
    
'num_links' => 5
) ;
$this->load->library('pagination') ;
$this->pagination->initialize($paginConfig) ;
        
if(
$this->uri->segment(3)) $this->db->limit($this->uri->segment(3),$paginConfig['per_page']) ;
else 
$this->db->limit($paginConfig['per_page']) ;

$query $this->db->get() ;
$clients $query->result() ;

$this->db->flush_cache() ; 

and this render an error like

Code:
A Database Error Occurred

Error Number: 1054

Unknown column 'session_id' in 'where clause'

SELECT COUNT(*) AS `numrows` FROM (`ap_client`) WHERE `session_id` = 'f78ce30a6bc8447dccb6877b9dead165' AND `session_id` = 'f78ce30a6bc8447dccb6877b9dead165' ORDER BY `nom` desc

Filename: /homepages/39/d561186525/htdocs/app-dev/application-2.2/controllers/clients.php

Line Number: 53

where

Line Number 53>
PHP Code:
$count $this->db->count_all_results() ; 

Did i do something wrong, or i miss some configuration somewhere ?
where does this session_id field come from ?
I'm a lazy boy lol i have a lot more 'wheres' to add in these querys, query caching would be nice if it works.
Reply
#2

PHP Code:
$this->admintab->order_by() 
Is this code doing something with the session? Since this is the only code not included in your example which is between the start_cache() and stop_cache() calls, it seems like the most likely place that something is happening to change the cached database query.
Reply
#3

(02-17-2015, 10:26 AM)mwhitney Wrote:
PHP Code:
$this->admintab->order_by() 
Is this code doing something with the session? Since this is the only code not included in your example which is between the start_cache() and stop_cache() calls, it seems like the most likely place that something is happening to change the cached database query.

OK yes this code works with the session,
Ok i didn't know the session work with cached database query.
Thank you very much, sorry for this newbie's Post.
mmmm .... i wonder if we could name some cache to avoid this and it would give something like
PHP Code:
$this->db->start_cache('foo') ;
$this->db->stop_cache('foo') ;
$this->db->flush_cache('foo') ; 
that could be useful.
Anyway thank you !
Reply
#4

i'm pretty stupid i can also execute the piece of code above, it solves my problem.... *sigh* sorry
Reply
#5

The main thing to remember is that any active record activity between the start_cache and stop_cache calls is going to be included in the cached query. In your case, it was just hiding in another method. All of us do something like that from time to time, and it's a good reminder to be careful of unintended side-effects in our code.

Generally, this is a good excuse to include a big comment mentioning that any database manipulation (including session handling) performed after start_cache() will be included in the cached query, so you (or someone else) don't accidentally create a similar problem later on.

Also, while it's nice to be specific and probably helpful in translating SQL to active record, $this->db->select('*') isn't necessary, since it will automatically add 'select *' to the query if no calls are made to select(). On some databases, select * is also slower than explicitly stating the selected fields (though being explicit does mean you would have to update the query if you changed the table(s))
Reply




Theme © iAndrew 2016 - Forum software by © MyBB