Changeset 23422 for branches/version-2_13-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php

Timestamp:

2014/05/15 12:50:43 (10 years ago)

Author:

Seasoft

Message:

#2538 (商品一覧取得のDB処理で商品規格を全件処理している)

• MySQL で速度低下するパターンがあるため、PostgreSQL のみ適用するよう組み直した。

File:

• branches/version-2_13-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php (modified) (1 diff)

branches/version-2_13-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php

@@ -283 +283 @@
         return $objQuery->getCol($col, $from, $where);
     }
+
+    /**
+     * 商品詳細の SQL を取得する.
+     *
+     * PostgreSQL 用にチューニング。
+     * @param  string $where_products_class 商品規格情報の WHERE 句
+     * @return string 商品詳細の SQL
+     */
+    public function alldtlSQL($where_products_class = '')
+    {
+        if (!SC_Utils_Ex::isBlank($where_products_class)) {
+            $where_products_class = 'AND (' . $where_products_class . ')';
+        }
+        /*
+         * point_rate, deliv_fee は商品規格(dtb_products_class)ごとに保持しているが,
+         * 商品(dtb_products)ごとの設定なので MAX のみを取得する.
+         */
+        $sub_base = "FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id $where_products_class";
+        $sql = <<< __EOS__
+            (
+                SELECT
+                     dtb_products.*
+                    ,dtb_maker.name AS maker_name
+                    ,(SELECT MIN(product_code) $sub_base) AS product_code_min
+                    ,(SELECT MAX(product_code) $sub_base) AS product_code_max
+                    ,(SELECT MIN(price01) $sub_base) AS price01_min
+                    ,(SELECT MAX(price01) $sub_base) AS price01_max
+                    ,(SELECT MIN(price02) $sub_base) AS price02_min
+                    ,(SELECT MAX(price02) $sub_base) AS price02_max
+                    ,(SELECT MIN(stock) $sub_base) AS stock_min
+                    ,(SELECT MAX(stock) $sub_base) AS stock_max
+                    ,(SELECT MIN(stock_unlimited) $sub_base) AS stock_unlimited_min
+                    ,(SELECT MAX(stock_unlimited) $sub_base) AS stock_unlimited_max
+                    ,(SELECT MAX(point_rate) $sub_base) AS point_rate
+                    ,(SELECT MAX(deliv_fee) $sub_base) AS deliv_fee
+                    ,(SELECT COUNT(*) $sub_base) AS class_count
+                FROM dtb_products
+                    LEFT JOIN dtb_maker
+                        ON dtb_products.maker_id = dtb_maker.maker_id
+                WHERE EXISTS(SELECT * $sub_base)
+            ) AS alldtl
+__EOS__;
+
+        return $sql;
+    }
 }