Changeset 21544 for branches/version-2_12-dev/data/class/helper/SC_Helper_DB.php

Timestamp:

2012/02/22 01:27:53 (14 years ago)

Author:

Seasoft

Message:

#1656 (カテゴリ登録のロジックを高速化)
#1661 (共通ロジックを利用する)

• SC_Query#getCol

#1660 (SC_Helper_DB#sfCountCategory メモリ消費が激しい)
#1659 (SC_Helper_DB#sfCountCategory メモリリークが発生する)

File:

• branches/version-2_12-dev/data/class/helper/SC_Helper_DB.php (modified) (7 diffs)

branches/version-2_12-dev/data/class/helper/SC_Helper_DB.php

@@ -540 +540 @@
      */
     function addProductBeforCategories($category_id, $product_id) {
+        $objQuery =& SC_Query_Ex::getSingletonInstance();
 
         $sqlval = array('category_id' => $category_id,
                         'product_id' => $product_id);
 
-        $objQuery =& SC_Query_Ex::getSingletonInstance();
-
-        // 現在の商品カテゴリを取得
-        $arrCat = $objQuery->select('product_id, category_id, rank',
-                                    'dtb_product_categories',
-                                    'category_id = ?',
-                                    array($category_id));
-
-        $max = '0';
-        foreach ($arrCat as $val) {
-            // 同一商品が存在する場合は登録しない
-            if ($val['product_id'] == $product_id) {
-                return;
-            }
-            // 最上位ランクを取得
-            $max = ($max < $val['rank']) ? $val['rank'] : $max;
-        }
-        $sqlval['rank'] = $max + 1;
-        $objQuery->insert('dtb_product_categories', $sqlval);
+        $arrSql = array();
+        $arrSql['rank'] = '(SELECT COALESCE(MAX(rank), 0) FROM dtb_product_categories sub WHERE category_id = ?) + 1';
+
+        $from_and_where = $objQuery->dbFactory->getDummyFromClauseSql();
+        $from_and_where .= ' WHERE NOT EXISTS(SELECT * FROM dtb_product_categories WHERE category_id = ? AND product_id = ?)';
+        $objQuery->insert('dtb_product_categories', $sqlval, $arrSql, array($category_id), $from_and_where, array($category_id, $product_id));
     }
 
@@ -711 +699 @@
         }
 
+        unset($arrCategoryCountOld);
+        unset($arrCategoryCountNew);
+
         $arrDiffCategory_id = array();
         //新しいカテゴリ一覧から見て商品数が異なるデータが無いか確認
@@ -758 +749 @@
         }
 
+        unset($arrOld);
+        unset($arrNew);
+
         //差分があったIDとその親カテゴリIDのリストを取得する
         $arrTgtCategory_id = array();
@@ -763 +757 @@
             $arrTgtCategory_id[] = $parent_category_id;
             $arrParentID = $this->sfGetParents('dtb_category', 'parent_category_id', 'category_id', $parent_category_id);
-            $arrTgtCategory_id = array_merge($arrTgtCategory_id, $arrParentID);
-        }
-
-        //重複を取り除く
-        $arrTgtCategory_id = array_unique($arrTgtCategory_id);
+            $arrTgtCategory_id = array_unique(array_merge($arrTgtCategory_id, $arrParentID));
+        }
+
+        unset($arrDiffCategory_id);
 
         //dtb_category_total_count 集計処理開始
         //更新対象カテゴリIDだけ集計しなおす。
         $arrUpdateData = array();
+        $where_products_class = '';
+        if (NOSTOCK_HIDDEN) {
+            $where_products_class .= '(stock >= 1 OR stock_unlimited = 1)';
+        }
+        $from = $objProduct->alldtlSQL($where_products_class);
         foreach ($arrTgtCategory_id as $category_id) {
             $arrval = array();
@@ -777 +775 @@
             if ($tmp_where != '') {
                 $sql_where_product_ids = 'product_id IN (SELECT product_id FROM dtb_product_categories WHERE ' . $tmp_where . ')';
-                $arrval = array_merge((array)$tmp_arrval, (array)$tmp_arrval);
+                $arrval = $tmp_arrval;
             } else {
                 $sql_where_product_ids = '0<>0'; // 一致させない
@@ -783 +781 @@
             $where = "($sql_where) AND ($sql_where_product_ids)";
 
-            $where_products_class = '';
-            if (NOSTOCK_HIDDEN) {
-                $where_products_class .= '(stock >= 1 OR stock_unlimited = 1)';
-            }
-
-            $from = $objProduct->alldtlSQL($where_products_class);
-            $sql = "SELECT count(*) FROM $from WHERE $where ";
-            $arrUpdateData[ $category_id ] = $objQuery->getOne($sql, $arrval);
-        }
+            $arrUpdateData[$category_id] = $objQuery->count($from, $where, $arrval);
+        }
+
+        unset($arrTgtCategory_id);
+
         // 更新対象だけを更新。
         foreach ($arrUpdateData as $cid => $count) {
@@ -861 +855 @@
         $where = "$pid_name IN (" . implode(',', array_fill(0, count($arrPID), '?')) . ")";
 
-        $ret = $objQuery->select($id_name, $table, $where, $arrPID);
-
-        $arrChildren = array();
-        foreach ($ret as $val) {
-            $arrChildren[] = $val[$id_name];
-        }
-
-        return $arrChildren;
+        $return = $objQuery->getCol($id_name, $table, $where, $arrPID);
+
+        return $return;
     }