title: Codeigniter Active Record - Count Total Found Rows with Limit (MySQL)
body: <p>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.</p>
  
  <p>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?</p>
  
  <p>Here's the query (which I didn't build myself originally):</p>
  
  <pre><code>$this-&gt;db
    -&gt;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')
    -&gt;from('table9')  
    -&gt;join('table10', 'table10.field3 = table9.field2')
    -&gt;join('categories', 'categories.field1 = table10.field1')
    -&gt;join('table3', 'table3.field8 = table9.field2')
    -&gt;join('table1', 'table1.id = table9.field2')
    -&gt;join('table2', 'table2.field9 = table9.field2 AND table2.field19 = 1', 'left')
    -&gt;join('table4', 'table4.field10 = table9.field2 AND table4.field21 = 0', 'left')
    -&gt;join('table5', 'table5.field11 = table4.field12 AND table5.field1 != 0', 'left')
    -&gt;join('table6', 'table6.field13 = table9.field2 AND table6.field22 BETWEEN SYSDATE() - INTERVAL 90 DAY AND SYSDATE()', 'left')
    -&gt;join('table7', 'table7.field14 = table9.field2', 'left')
    -&gt;join('table8', 'table8.field15 = table9.field2', 'left')
    -&gt;where('table1.field16', NULL)
    -&gt;where($where_clause_1, null, FALSE)
    -&gt;where('table9.field17', $searchArray['search_country'])
    -&gt;or_where($or_where_clause_2, null, FALSE)
    -&gt;or_where($or_where_clause_3, null, FALSE)
    -&gt;or_where($or_where_clause_4, null, FALSE)
    -&gt;or_where($or_where_clause_5, null, FALSE)
    -&gt;or_where($or_where_clause_6, null, FALSE)
    -&gt;or_where($or_where_clause_7, null, FALSE)
    -&gt;like('table9.field17', $searchArray['search_country'])
    -&gt;order_by('table3.field18', 'ASC')
    -&gt;order_by('table2.field19', 'DESC')
    -&gt;order_by('table1.field20', 'DESC')
    -&gt;group_by('table9.field2')
    -&gt;limit($limit, $offset);    
  
  
    $data = $this-&gt;db-&gt;get();
  
  return $data-&gt;result_array();
  </code></pre>
  
  <p>Really appreciate any help!</p>