Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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!

share|improve this question

2 Answers

up vote 3 down vote accepted

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 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:

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

Finally, run a second query to get the found rows. 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;
share|improve this answer
Thankyou excellent solution, really helped :-) – whispersan May 15 at 0:07
up vote 0 down vote

One way I can think is to have variable $count when you call your function, and it will go 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 function two time - one for count and the other for limit query:

$count = count($this->your_function(TRUE));
$data['query] = $this->your_function();
share|improve this answer
Thankyou, but I'm trying to avoid calling the function twice - it would involve running the query twice, which would be inefficient – whispersan May 14 at 23:10
I managed to find question and solution similar to yours: link – Sasha May 14 at 23:15
I've seen that one but struggling to use SQL_CALC_FOUND_ROWS, possibly due to the complexity of the query I'm using – whispersan May 14 at 23:18
Hmmm, maybe you should go with classic query, instead of CI active record. I guess that way you could manage to insert SQL_CALC_FOUND_ROWS. – Sasha May 14 at 23:20
I converted the classic query to active record because I needed to stop CI applying backticks to the query, which it does anyway even if written as a classic query but you can prevent it by using the optional parameter in active record – whispersan May 14 at 23:57
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.