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

i'm using complex sql query in codeigniter model limit applied. i'd count total number of rows have been found if limit , offset had not been applied.

i'd return count, along result array, controller - how do this? put sql_calc_found_rows if right solution?

here's query (which didn't build myself originally):

$this->db   ->select('table1.*       ,table2.*       ,table3.*       ,table4.*       ,table5.*       ,table6.*       ,table7.*       ,table8.*       ,table9.*       ,(select group_concat(field1) table10 table10.field3 = table9.field2) categories       ,(select group_concat(field1) table5 table5.field11 = table4.field12 , table4.field21 = 0 , table5.field1 != 0) categories2       ,(select avg(table11.field4) table11 table11.field6 = table9.field2) rating       ,(select count(table12.field5) table12 table12.field7 = table9.field2) 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 , table2.field19 = 1', 'left')   ->join('table4', 'table4.field10 = table9.field2 , table4.field21 = 0', 'left')   ->join('table5', 'table5.field11 = table4.field12 , table5.field1 != 0', 'left')   ->join('table6', 'table6.field13 = table9.field2 , table6.field22 between sysdate() - interval 90 day , 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 help!

i've had exact same requirement pagination, , able make work using codeigniter active record.

first, set option sql_calc_found_rowsas pseudo column in select statement , set escape query false:

$this->db->select('sql_calc_found_rows null rows, other columns ...',false); 

then, after execute query limit , offset in place assign result set return array:

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

finally, run second query found rows , assign return array. i'm using method chaining here in 1 step.

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

and return result , row count array.

return $return;