如何重用CodeIgniter Active Record Query

Question

I'm trying to...

1. Set some query parameters and filters.
2. Get the total number of matching rows.
3. Set limit and offset.
4. Get the results

Here's what I initially tried:

$this->db->select(<aggregation, subqueries>);
$this->db->from('users');
$this->db->where(<complex filters>);

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

$this->db->limit($limit, $offset);

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

But calling count_all_results calls _reset_select internally meaning I have to do the first step again - not very DRY.

How would you achieve this in a simple, clean way?

Answer 1

Just use Query Builder Caching

$this->db->start_cache();

$this->db->select(<aggregation, subqueries>);
$this->db->from('users');
$this->db->where(<complex filters>);

$this->db->stop_cache();

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

$this->db->limit($limit, $offset);

$query = $this->db->get();
$this->db->flush_cache();

$result = $query->result();

https://codeigniter-30-dev.readthedocs.io/zh_TW/latest/database/query_builder.html#this-db-count-all-results