ActiveRecord - print query before run (CI 2.0.2)

[eluser]Unknown[/eluser]
Hi everyone !

I need to get query string from active object before I run query,
I need it before run query to own cache library to check if cache file for this query exists and/or is non expired (cache file name == sha1($query_string) ).
It's necessary because in some models in my application some methods set active record parameters for other methods, so only way to create unique cache file name is get query before run it and check if

Code:

sha1($query)

file exists.

is it possible ?


only way I found is

Code:

$this->db->last_query()

but is little to late

Example problem

Code:

$model->setParameter($param1);
$data = $model->getResult();
$model->setParameter($param2);
$data = $model->getResult();

Now if I set cache in

Code:

getResult()

I don't have foothold to set unique cache file name.

Thanks in advance and sorry for my English

[eluser]toopay[/eluser]
The other way to see what the queries which exist in DB object is using

Code:

// Here you can see what queries has been loaded
var_dump($this->db->queries);
$this->db->select('*');
$this->db->from('post');
$res = $this->db->get();
// Here you should see your new queries has been loaded
var_dump($this->db->queries);

But for your task you asking, you need to extend the database class, since internally CI database class has its own cache mechanism (check out Database Caching Class)

[eluser]Unknown[/eluser]
@toopay - thank you for your reply.
Standard CI database caching have two problems , cache is non expire and caching is controller oriented
(cache file == controller+method) ,
when for example two other controller methods use same model then two cache files will be created ( with same content ), other problem is when you want clean cache you have to remember about all methods in all controllers with use these method in model and other if you start using routes for example for friendly url -s then you have to change all clean cache calls.

that's why I'm trying to create own library which meet these requirements.

I found temporary solution , in DB_active_rec add method

Code:

function get_query_string()
{
    $sql = $this->_compile_select();
    return $sql;
}

example usage

Code:

$this->db->from('table');
$this->db->join('other_table', 'other_table.table_id = table.id');
$this->db->select('some_filed, other_field');

$query = $this->db->get_query_string();
$cache_file = $query; // cache file name as sh1($query)
$cache_prefix = 'file_prefix'; // to easy find files associated with method

if($result = $this->cachelib->get($cache_file, $cache_prefix))
{
return $result;
}

$query = $this->db->get();
if($query->num_rows())
{
      $this->cachelib->cache($cache_file, $cache_prefix , $query->result());
    // cachelib will add ttl and save with serialized query result to file
    return $query->result();
}

This solution is not without flaws, so if anyone has an idea, please write suggestion

[eluser]toopay[/eluser]
[quote author="jacek.s" date="1313123738"]when for example two other controller methods use same model then two cache files will be created ( with same content )[/quote] Really? Using same table, yes maybe. But using same/exact query ?I wonder why someone need to output same content using different controller then.
[quote author="jacek.s" date="1313123738"]other problem is when you want clean cache you have to remember about all methods in all controllers with use these method in model and other if you start using routes for example for friendly url -s then you have to change all clean cache calls.[/quote] For these either we can extend the native cache method and add some procedure to check the cached file using filemtime and set the expiration time, or easier : set a cron job to delete all cached data periodically.