So erhalten Sie die unbegrenzte Zeilenanzahl sowie die begrenzten Ergebnisse für die Paginierung mit dem Abfrage-Builder ⇐ Php

[Anonymous]

Ich habe zwei Tabellen.
order_log

Code: Select all

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

und Anbietertabelle

Code: Select all

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

Hier ist mein Code zum Abrufen von Daten aus der Tabelle

Code: Select all

$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();

und das Ergebnis perfekt erhalten
aber wenn ich versuche, die Paginierungsanzahl zu ermitteln

Code: Select all

$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();

es gibt 1 zurück
und wenn ich „group by“ verwende, gibt es 3 zurück
aber es sind 84 Zeilen
Wie kann ich dieses Problem lösen?
Ich kann/will nicht num_rows() verwenden.