model

public function get_customers() { $data = array(); if(is_array($this->config->get('finance_customer_group'))){ $customer_group_id = implode(",", $this->config->get('finance_customer_group')); $query = $this->db->query("SELECT *, CONCAT(firstname, ' ', lastname) AS name FROM " . DB_PREFIX . "customer WHERE customer_group_id IN (" . $customer_group_id . ") ORDER BY name ASC"); $data = $query->rows; } return $data; }

I pass in Controller

  $customers = $this->model_module_finance->get_customers(); $this->data['customers'] = array(); foreach($customers as $customer){ $costumer_id = $this->model_module_finance->getIdCustomer($customer['customer_id']); Передаю в model if($costumer_id['customer_id'] == $customer['customer_id']) { $this->data['customers'][] = array( 'name' => $customer['name'], 'orders' => $costumer_id['orders'], 'href' => $this->url->link('module/finance/customer_info', 'customer_id=' . $customer['customer_id'] . '&token=' . $this->session->data['token'], 'SSL') ); }else{ $this->data['customers'][] = array( 'name' => $customer['name'], 'orders' => 0, 'href' => $this->url->link('module/finance/customer_info', 'customer_id=' . $customer['customer_id'] . '&token=' . $this->session->data['token'], 'SSL') ); } } public function getIdCustomer($customer_id){ Передаю данные в Controller $query = $this->db->query("SELECT oc_order.customer_id, COUNT(oc_order.order_id) as orders FROM oc_order WHERE oc_order.customer_id = '".$customer_id."' AND oc_order.order_id NOT IN (SELECT order_id FROM oc_finance_order WHERE oc_finance_order.customer_id = '".$customer_id."' ) AND oc_order.order_status_id > 0 AND oc_order.order_status_id !=7 GROUP BY oc_order.firstname ORDER BY oc_order.firstname ASC"); return $query->row; }

Long loaded, records in the database in order more than 15000 Can I speed up the download?

  • $ this-> db is a PDO connection? 15000 records, and choose how much at a time? also all 15,000? Are there any indexes in the table? And in the second too. - Alexander Belinsky
  • This is the standard opencart 1.5 method I don't know - Sender1050
  • No takes c first tab. customer only have those entries that have WHERE customer_group_id IN (" . $customer_group_id . ") and repeating the order selects those entries for which `WHERE oc_order.customer_id = '". $ customer_id. "" ` - Sender1050
  • I think I need to optimize not the code, but the database itself) - Manitikyl

1 answer 1

About this kind of queries (when you need to take something from one table and exclude records on a condition tied to another table) read here .

I suggest changing the query you use in the getIdCustomer function

Option One:

 SELECT oc_order.customer_id, COUNT(oc_order.order_id) AS orders FROM oc_order LEFT JOIN oc_finance_order ocf ON oc_order.order_id = ocf.order_id AND ocf.customer_id = '".$customer_id."' WHERE ISNULL(ocf.order_id) /*любое поле, которое не может быть null из таблицы oc_finance_order*/ AND oc_order.customer_id = :$customer_id AND oc_order.order_status_id > 0 AND oc_order.order_status_id != 7 GROUP BY oc_order.firstname ORDER BY oc_order.firstname ASC;

Option two:

 SELECT oc_order.customer_id, COUNT(oc_order.order_id) AS orders FROM oc_order WHERE NOT EXISTS ( SELECT null FROM oc_finance_order ocf WHERE oc_order.order_id = ocf.order_id AND ocf.customer_id = :$customer_id ) AND oc_order.customer_id = '".$customer_id."' AND oc_order.order_status_id > 0 AND oc_order.order_status_id != 7 GROUP BY oc_order.firstname ORDER BY oc_order.firstname ASC;