SOLVED! Active Records - Better way to get record count??

[eluser]sudesh[/eluser]
Hi all,

Can we some how again load the last executed query in db object, and change few parameters

below is what i exactly mean,

$this->db->select('field1, field2');
$this->db->where('field1', 'something');
$this->db->where('field2', 'something');
$this->db->limit($offset, $row_count);

Now what i want is to change the select of last query to

$this->db->select('count(*) as cnt');

and remove limit.

so that i can get total row count.

i know i can get it by using last_query, but my table has 1000's of records loading them just for getting count is waste(inefficient), trying to find some other alternative.

[eluser]Maglok[/eluser]
I have worked with a system for monthly payment that had tables with millions of rows, are you sure doing a count is going to take so much time? Did you run the profiler to see what the difference is between just this query and then another quick count, I do think count is pretty cheap, especially if you have proper indexes.

[eluser]sudesh[/eluser]
[quote author="Maglok" date="1248266961"]I have worked with a system for monthly payment that had tables with millions of rows, are you sure doing a count is going to take so much time? Did you run the profiler to see what the difference is between just this query and then another quick count, I do think count is pretty cheap, especially if you have proper indexes.[/quote]

Do you mean to say running a query like below

select field1, field2 from table

and then using count function ($result->num_rows()) is not gona tax much compared to

select count(*) from table

I believe it should tax, never checked with profiler though. But i believe in later php buffers all the rows in memory for no use.

what you think........

[eluser]Maglok[/eluser]
I say run the profiler and try it out. I am pretty sure count is cheaper since it only counts amount of rows and doesnt care what the value of each field is.

[eluser]sudesh[/eluser]
[quote author="Maglok" date="1248279034"]I say run the profiler and try it out. I am pretty sure count is cheaper since it only counts amount of rows and doesnt care what the value of each field is.[/quote]

even i am saying 'select count(*) from table' is less taxing (my english might be not that good to make that clear),

but i needed a solution how to do it with active records without code redundancy in my scenario.

[eluser]David Johansson[/eluser]
why is it so troublesome to rewrite the query?
if you are using

Code:

$this->db->select(‘field1, field2’);
$this->db->where(‘field1’, ‘something’);
$this->db->where(‘field2’, ‘something’);
$this->db->limit($offset, $row_count);

it's not so much code just writing:

Code:

$this->db->select(‘count(*)’);
$this->db->where(‘field1’, ‘something’);
$this->db->where(‘field2’, ‘something’);

[eluser]David Johansson[/eluser]
i think you want to do active record caching...

Code:

$this->db->start_cache();
$this->db->where('field1', 'something');
$this->db->where('field2', 'something');
$this->db->stop_cache();

$this->db->select('field1, field2');
$this->db->limit($offset, $row_count);
$this->db->get('table');

$this->db->select('count(*)');
$this->db->get('table');

$this->db->flush_chache();

[eluser]sudesh[/eluser]
[quote author="David Johansson" date="1248286674"]i think you want to do active record caching...

Code:

$this->db->start_cache();
$this->db->where('field1', 'something');
$this->db->where('field2', 'something');
$this->db->stop_cache();

$this->db->select('field1, field2');
$this->db->limit($offset, $row_count);
$this->db->get('table');

$this->db->select('count(*)');
$this->db->get('table');

$this->db->flush_chache();

[/quote]

exactly wht i was looking for (should have seen the doc earlier)

thx david