如何使用groupby和count_all_results-Coddinitor从单个表创建分页

我有两个表1.

order_log
order_id  order_status provider_id more
  1              2            2     ...
  2              2            1     ...
  3              0            1     ...
  4              3            2     ...
  5              1            3     ...
  6              1            1     ...
  7              4            2     ...
...

和另一个表

provider table
   provider_id   provider_name
    1               vodafone
    2                   xyz
    3                   abc
    4                   njk
....

这是我从表中获取数据的代码

$this->load->model('ordermodel');
    $order_status=$this->ordermodel->getOrderStatus();

    $this->db->select($this->orderLogTable.'.operator_id,'.$this->orderLogTable.'.order_status,COUNT('.$this->db->dbprefix($this->orderLogTable).'.order_status) AS numofstatus, SUM('.$this->db->dbprefix($this->orderLogTable).'.order_customer_amount) AS totalamount,'.$this->operatorTable.'.operator_name, SUM('.$this->db->dbprefix($this->orderLogTable).'.order_retailer_discount_amount) AS totaldiscountamount');
    $this->db->from($this->operatorTable);
    $this->db->join($this->orderLogTable,$this->orderLogTable.'.operator_id ='.$this->operatorTable.'.operator_id AND '.'date(' . $this->db->dbprefix($this->orderLogTable) . '.order_complete_date) BETWEEN "' . date('Y-m-d', strtotime($fromdate)) . '" and"' . date('Y-m-d', strtotime($todate)) . '" AND '.$this->orderLogTable.'.order_status IN ('.$order_status['cancel'].','.$order_status['success'].','.$order_status['pending'].')','left');
    $this->db->group_by(array($this->orderLogTable.'.order_status',$this->operatorTable.'.operator_id'));
    $this->db->order_by($this->operatorTable.'.operator_id');
    if ($limit !== NULL && $offset !== NULL) {
        $this->db->limit($limit, $offset);
     }
    $query=$this->db->get();
    return $query->result_array();

并得到完美的结果

但是当我试图获得分页计数时

$this->load->model('ordermodel');
        $order_status=$this->ordermodel->getOrderStatus();
        $this->db->select($this->operatorTable.'.operator_id,'.$this->orderLogTable.'.order_status');
        $this->db->from($this->operatorTable);
        $this->db->join($this->orderLogTable,$this->orderLogTable.'.operator_id ='.$this->operatorTable.'.operator_id AND '.'date(' . $this->db->dbprefix($this->orderLogTable) . '.order_complete_date) BETWEEN "' . date('Y-m-d', strtotime($fromdate)) . '" and"' . date('Y-m-d', strtotime($todate)) . '" AND '.$this->orderLogTable.'.order_status IN ('.$order_status['cancel'].','.$order_status['success'].','.$order_status['pending'].')','left');
        //$this->db->group_by(array($this->orderLogTable.'.order_status',$this->operatorTable.'.operator_id'));
    /*here group by is off for count_all_results()*/
        //$query = $this->db->get();
        return $this->db->count_all_results();

它返回1当我使用分组时，它返回3

但是有84行

我该如何解决这个问题？请不要使用num_rows((

$this->load->model('ordermodel');
    //for count all records 
    $count_all = $this->db->count_all('your table');
    $order_status=$this->ordermodel->getOrderStatus();
    $this->db->select($this->operatorTable.'.operator_id,'.$this->orderLogTable.'.order_status');
    $this->db->from($this->operatorTable);
    $this->db->join($this->orderLogTable,$this->orderLogTable.'.operator_id ='.$this->operatorTable.'.operator_id AND '.'date(' . $this->db->dbprefix($this->orderLogTable) . '.order_complete_date) BETWEEN "' . date('Y-m-d', strtotime($fromdate)) . '" and"' . date('Y-m-d', strtotime($todate)) . '" AND '.$this->orderLogTable.'.order_status IN ('.$order_status['cancel'].','.$order_status['success'].','.$order_status['pending'].')','left');
    //$this->db->group_by(array($this->orderLogTable.'.order_status',$this->operatorTable.'.operator_id'));
/*here group by is off for count_all_results()*/
    //$query = $this->db->get();
    return $this->db->count_all_results();

可以使用$this->db->count_all('your table');对所有记录进行计数。

相关内容

最新更新

热门标签：