Codeigniter Active Record - Count Total Found Rows with Limit (MySQL)

Question

I'm using a complex SQL query in a Codeigniter model with a limit applied. I'd like to count the total number of rows that would have been found if the limit and offset had not been applied.

I'd like to return the count, along with the result array, to my controller - how do I do this? Where do I put SQL_CALC_FOUND_ROWS if this is the right solution?

Here's the query (which I didn't build myself originally):

$this->db
  ->select('table1.*
      ,table2.*
      ,table3.*
      ,table4.*
      ,table5.*
      ,table6.*
      ,table7.*
      ,table8.*
      ,table9.*
      ,(SELECT GROUP_CONCAT(field1) FROM table10 WHERE table10.field3 = table9.field2) as categories
      ,(SELECT GROUP_CONCAT(field1) FROM table5 WHERE table5.field11 = table4.field12 AND table4.field21 = 0 AND table5.field1 != 0) as categories2
      ,(SELECT AVG(table11.field4) FROM table11 WHERE table11.field6 = table9.field2) as rating
      ,(SELECT COUNT(table12.field5) FROM table12 WHERE table12.field7 = table9.field2) as rated_times')
  ->from('table9')  
  ->join('table10', 'table10.field3 = table9.field2')
  ->join('categories', 'categories.field1 = table10.field1')
  ->join('table3', 'table3.field8 = table9.field2')
  ->join('table1', 'table1.id = table9.field2')
  ->join('table2', 'table2.field9 = table9.field2 AND table2.field19 = 1', 'left')
  ->join('table4', 'table4.field10 = table9.field2 AND table4.field21 = 0', 'left')
  ->join('table5', 'table5.field11 = table4.field12 AND table5.field1 != 0', 'left')
  ->join('table6', 'table6.field13 = table9.field2 AND table6.field22 BETWEEN SYSDATE() - INTERVAL 90 DAY AND SYSDATE()', 'left')
  ->join('table7', 'table7.field14 = table9.field2', 'left')
  ->join('table8', 'table8.field15 = table9.field2', 'left')
  ->where('table1.field16', NULL)
  ->where($where_clause_1, null, FALSE)
  ->where('table9.field17', $searchArray['search_country'])
  ->or_where($or_where_clause_2, null, FALSE)
  ->or_where($or_where_clause_3, null, FALSE)
  ->or_where($or_where_clause_4, null, FALSE)
  ->or_where($or_where_clause_5, null, FALSE)
  ->or_where($or_where_clause_6, null, FALSE)
  ->or_where($or_where_clause_7, null, FALSE)
  ->like('table9.field17', $searchArray['search_country'])
  ->order_by('table3.field18', 'ASC')
  ->order_by('table2.field19', 'DESC')
  ->order_by('table1.field20', 'DESC')
  ->group_by('table9.field2')
  ->limit($limit, $offset);    


  $data = $this->db->get();

return $data->result_array();

Really appreciate any help!

Answer 1

I've previously had the exact same requirement for pagination, and I was able to make it work using CodeIgniter Active Record.

First, set the option SQL_CALC_FOUND_ROWSas a pseudo column in your select statement and set escape query to false:

$this->db->select('SQL_CALC_FOUND_ROWS null as rows, other columns ...',FALSE);

Then, after you execute your query with the limit and offset in place assign the result set to a return array:

$data = $this->db->get();
$return['results'] = $data->result();
// Do something with the results

Finally, run a second query to get the found rows and also assign that to the return array. I'm using method chaining here to do it all in one step.

$return['rows'] = $this->db->query('SELECT FOUND_ROWS() count;')->row()->count;

And return the result and row count array.

return $return;