Codeigniter活动记录 - 计数具有限制的总发现行(MySQL) [英] Codeigniter Active Record - Count Total Found Rows with Limit (MySQL)
问题描述
我在Codeigniter模型中使用复杂的SQL查询,并应用了限制。
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.
我想返回计数,如果没有应用限制和偏移,以及结果数组,到我的控制器 - 我该怎么做?如果这是正确的解决方案,我在哪里放置SQL_CALC_FOUND_ROWS?
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();
非常感谢任何帮助!
推荐答案
我之前对分页有同样的要求,我可以使用CodeIgniter Active Record。
I've previously had the exact same requirement for pagination, and I was able to make it work using CodeIgniter Active Record.
,请在select语句中将选项 SQL_CALC_FOUND_ROWS
设置为伪列,并将escape查询设置为false:
First, set the option SQL_CALC_FOUND_ROWS
as 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);
然后,在执行查询并使用limit和offset之后,将结果集分配给return数组:
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;
这篇关于Codeigniter活动记录 - 计数具有限制的总发现行(MySQL)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!