【管理画面＞売上集計】SQL文修正

[kotani]

商品別集計のSQL文において、WHERE句のINをEXISTSに変えることで高速化。

1系：/html/admin/total/index.php
2系：/data/class/pages/admin/total/index.php

    /** 商品別集計 **/
    function lfGetOrderProducts($type, $sdate, $edate, &$objPage, $graph = true, $mode = "") {
        list($where, $arrval) = $this->lfGetWhereMember('create_date', $sdate, $edate, $type);

        $where .= " and del_flg=0 and status <> " . ORDER_CANCEL;

        $sql = "SELECT T1.product_id, T1.product_code, T1.product_name, T1.products_count, T1.order_count, T1.price, T1.total ";
        $sql.= "FROM ( ";
        $sql.= "SELECT product_id, product_name, product_code, price, ";
        $sql.= "COUNT(*) AS order_count, ";
        $sql.= "SUM(quantity) AS products_count, ";
        $sql.= "(price * sum(quantity)) AS total ";
⇒      $sql.= "FROM dtb_order_detail WHERE order_id IN (SELECT order_id FROM dtb_order WHERE $where ) ";
        $sql.= "GROUP BY product_id, product_name, product_code, price ";
        $sql.= ") AS T1 ";
        $sql.= "ORDER BY T1.total DESC ";

           　↓↓↓

	$sql = "SELECT T1.product_id, T1.product_code, T1.product_name as name, T1.products_count, T1.order_count, T1.price, T1.total ";
	$sql.= "FROM ( ";
	$sql.= "SELECT product_id, product_name, product_code, price, ";
	$sql.= "COUNT(*) AS order_count, ";
	$sql.= "SUM(quantity) AS products_count, ";
	$sql.= "(price * sum(quantity)) AS total ";
⇒      $sql.= "FROM dtb_order_detail AS T2 WHERE EXISTS (SELECT 1 FROM dtb_order AS T3 WHERE $where ) ";
	$sql.= "GROUP BY product_id, product_name, product_code, price ";
	$sql.= ") AS T1 ";
	$sql.= "ORDER BY T1.total DESC ";

SELECT 1 FROM dtb_order AS T3 WHERE $where ) 
↓ 
SELECT 1 FROM dtb_order AS T3 WHERE T2.order_id = T3.order_id AND $where ) 
をにして改修

r17776　にて　EC-CUBE 2.4.0RC-1 に反映済

コミュニティ r18040