CI 分页双重查询

Question

我在CI框架中工作。对于分页，我使用两个查询，一个用于使用 limit 的所需输出，另一个用于计算总行数。有没有其他方法可以从单个查询中完成这两种方法？我的查询是：

{
$searchtxt = $this->input->post('searchtxt');
        $datefrom = $this->input->post('fromdate');
        $todate = $this->input->post('todate');
        $status= $this->input->post('order_status');
        $sc_id = $this->input->post('sc_id');

    if($sc_id){
        $this->db->where('('.'o.requested_sc_id = '.$sc_id.' OR o.requesting_sc_id = '.$sc_id.')');

        //$this->db->where('o.requesting_sc_id =',$sc_id);
        }
    if ($status){
        $this->db->where('o.order_status =',$status);
        }
        if ($status != '' && $status == 0 ){
        $this->db->where('o.order_status = 0');
        }
    if($searchtxt){
        $this->db->like('o.order_number',$searchtxt);
    }
    if($datefrom)
    {           
        $this->db->where('o.order_dt >=', date("Y-m-d",date_to_timestamp($datefrom)));  
    }
    if($todate)
    {           
        $this->db->where('o.order_dt <=', date("Y-m-d",date_to_timestamp($todate)));    
    }
    if ($this->session->userdata('usergroup_id')!=1 && $this->session->userdata('usergroup_id')!= 2  && $this->session->userdata('usergroup_id')!=6 ){      
        $this->db->where('('.'o.requested_sc_id = '.$this->session->userdata('sc_id').' OR o.requesting_sc_id = '.$this->session->userdata('sc_id').')');
        //$this->db->where('o.requesting_sc_id = '.$this->session->userdata('sc_id'));
    }
    $this->db->select('o.order_id,o.order_number,o.order_dt,o.call_id,c.call_uid,o.order_status,o.order_dt,sc.sc_name,e.engineer_name');
    $this->db->from($this->table_name.' AS o');
    $this->db->join($this->mdl_callcenter->table_name.' AS c','c.call_id=o.call_id','left');
    $this->db->join($this->mdl_servicecenters->table_name.' AS sc' ,'sc.sc_id=o.requested_sc_id','left');
    $this->db->join($this->mdl_engineers->table_name.' AS e', 'e.engineer_id = o.engineer_id','left');
    $this->db->order_by('o.order_status ASC,o.order_dt DESC,o.order_created_ts DESC');
    if(isset($page['limit'])){
        $this->db->limit((int)$page['limit'],(int)$page['start']);
    }
    $result = $this->db->get();
        //echo $this->db->last_query(); 
    if ($status){
        $this->db->where('o.order_status =',$status);
        }
    if ($status != '' && $status == 0 ){
        $this->db->where('o.order_status = 0');
        }
    if($sc_id){
        $this->db->where('o.requesting_sc_id =',$sc_id);
        }
    if($searchtxt){
        $this->db->like('o.order_number',$searchtxt);
    }
    if($datefrom)
    {           
        $this->db->where('o.order_dt >=', date("Y-m-d",date_to_timestamp($datefrom)));  
    }
    if($todate)
    {           
        $this->db->where('o.order_dt <=', date("Y-m-d",date_to_timestamp($todate)));    
    }
    //if ($this->session->userdata('global_admin')!=1){     
    if ($this->session->userdata('usergroup_id')!=1 && $this->session->userdata('usergroup_id')!= 2  && $this->session->userdata('usergroup_id')!=6 ){  
        $this->db->where('o.requested_sc_id = '.$this->session->userdata('sc_id').' or o.requesting_sc_id = '.$this->session->userdata('sc_id'));
    }
    $this->db->select('o.order_id');
    $this->db->from($this->table_name.' AS o');
    $this->db->join($this->mdl_callcenter->table_name.' AS c','c.call_id=o.call_id','left');
    $this->db->join($this->mdl_servicecenters->table_name.' AS sc' ,'sc.sc_id=o.requested_sc_id','left');
    $this->db->join($this->mdl_engineers->table_name.' AS e', 'e.engineer_id = o.engineer_id','left');
    $result_total = $this->db->get();
    $orders['list'] = $result->result();
    $orders['total'] = $result_total->num_rows();
    return $orders;
}

Answer 1

这里有一些解释。

function($limit,$offset){
    $this->db
            ->select("SQL_CALC_FOUND_ROWS emp", FALSE)
            ->select("*")
            ->from('tbl1')
            ->limit($limit, $offset);
    $data["query_result"] = $this->db->get()->result_array();
    $query = $this->db->query('SELECT FOUND_ROWS() AS `Count`');
    $data["total_rows"] = $query->row()->Count;
    return $data;
}

在这里，您会看到您被迫使用另一个查询获取总数。
当然这不是你想要的，但使用 mysql 进行单个查询是不可能的。
但这里有一种 php 方法。

function($limit,$offset){
    $this->db
            ->select("*")
            ->from('tbl1');
    $data["query_result"] = $this->db->get()->result_array();
    $data["total_rows"] = array_slice($data["query_result"], $offset, $limit);
    return $data;
}

这可以通过单个查询来实现，但涉及 php 以获得所需的结果。