count_all_results() with complex WHERE clause

[eluser]Nicolas Connault[/eluser]
Hi everyone! I've been coding with CI for a number of months now, and so far I've always found the help I needed from the User Guide and the existing posts on the forums.

Now I'm stumped. I have a situation in which a data table is made up of a variety of fields from joined tables, including a custom field made up of a concatenation of fields from two different tables.

I want to be able to filter the table by the custom field (let's called it "custom_field").

However, because of pagination requirements, I must first obtain a total number of records returned by that query. Because of the level of abstraction in which I'm working (this is all setup in MY_Controller and used by a dozen controllers with different models and DB schemas), I have to use activerecord's count_all_results() to get that number.

Unfortunately, doing a count that way wipes out my custom field, since only COUNT(*) is selected. The other way to count would be to use $query->num_rows after running the full query, but doing this would be computationally expensive, and I only ever want to fetch 20 records at a time.

Has anyone come across that situation before? Any ideas?

[eluser]crikey[/eluser]
This is what I do (may or may not help):

For model functions that I use with pagination, I add an extra parameter (eg $count_only = FALSE). Then when I call the function, I do it twice, once passing TRUE for $count_only, in which case I ignore limits and offsets just to get the total results, and then again without a parameter (or passing it as FALSE if need be) to get the actual pagination page set - 20 records in your case.

My model function looks at the $count_only parameter and if it's TRUE, then use count_all_results(), ignoring limits and offsets, otherwise apply limits and offsets and use $this->db->get() - in your case the second call will also include the custom_field.

Hope that helps,
Grant

[eluser]Nicolas Connault[/eluser]
Thanks crikey, nice to to see a fellow Aussie on the forum

Your suggestion is fine, except that in addition to pagination limits, I also have optional WHERE conditions based on that blasted custom field, which cannot be built if I use count_all_results (because it only includes COUNT(*) in the SELECT clause).

I guess that one solution would be to eliminate the need to select the custom field, by saving its value in one of the tables. That would create duplication of data though, and I'm really not keen on that.