Codeigniter Active Record - number of rows found with a limit (MySQL)

Question

I use a complex SQL query in the Codeigniter model with the limit applied. I would like to calculate the total number of rows that would be found if the limit and offset were not applied.

I would like to return the counter along with an array of results to my controller - how do I do this? Where can I put SQL_CALC_FOUND_ROWS if this is the right solution?

Here's the query (which I did not create myself):

$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

Previously, I had the same requirement for pagination, and I was able to get it working with the active CodeIgniter record.

First set the SQL_CALC_FOUND_ROWS option as a pseudo-column in the select statement and set the escape query to false:

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

Then, after executing the query with constraint and offset, assign the result set to the returned array:

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

Finally, run the second query to get the rows found, and also assign this to the returned array. I use the chaining method here to do it all in one step.

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

And return an array of results and rows.

 return $return; 

Answer 2

One way I can think of is to have the variable $ count when calling your function, and it will look something like this:

Function your_function ($ count = FALSE) {

 $this->db->select(...) //before limit you do this: if($count != FALSE): return $this->db->get->result_array() else : return $this->db->limit($limit, $offset)->get()->result_array(); endif; } 

This way you can call the function twice - one for count and another for requesting the limit:

 $count = count($this->your_function(TRUE)); $data['query] = $this->your_function();