Changeset 23422 for branches/version-2_13-dev/data/class/db
- Timestamp:
- 2014/05/15 12:50:43 (10 years ago)
- Location:
- branches/version-2_13-dev/data/class/db
- Files:
-
- 2 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/version-2_13-dev/data/class/db/SC_DB_DBFactory.php
r23378 r23422 216 216 return $sql; 217 217 } 218 219 /** 220 * 商品詳細の SQL を取得する. 221 * 222 * @param string $where_products_class 商品規格情報の WHERE 句 223 * @return string 商品詳細の SQL 224 */ 225 public function alldtlSQL($where_products_class = '') 226 { 227 if (!SC_Utils_Ex::isBlank($where_products_class)) { 228 $where_products_class = 'AND (' . $where_products_class . ')'; 229 } 230 /* 231 * point_rate, deliv_fee は商品規格(dtb_products_class)ごとに保持しているが, 232 * 商品(dtb_products)ごとの設定なので MAX のみを取得する. 233 */ 234 $sql = <<< __EOS__ 235 ( 236 SELECT 237 dtb_products.* 238 ,T4.product_code_min 239 ,T4.product_code_max 240 ,T4.price01_min 241 ,T4.price01_max 242 ,T4.price02_min 243 ,T4.price02_max 244 ,T4.stock_min 245 ,T4.stock_max 246 ,T4.stock_unlimited_min 247 ,T4.stock_unlimited_max 248 ,T4.point_rate 249 ,T4.deliv_fee 250 ,T4.class_count 251 ,dtb_maker.name AS maker_name 252 FROM dtb_products 253 INNER JOIN ( 254 SELECT product_id, 255 MIN(product_code) AS product_code_min, 256 MAX(product_code) AS product_code_max, 257 MIN(price01) AS price01_min, 258 MAX(price01) AS price01_max, 259 MIN(price02) AS price02_min, 260 MAX(price02) AS price02_max, 261 MIN(stock) AS stock_min, 262 MAX(stock) AS stock_max, 263 MIN(stock_unlimited) AS stock_unlimited_min, 264 MAX(stock_unlimited) AS stock_unlimited_max, 265 MAX(point_rate) AS point_rate, 266 MAX(deliv_fee) AS deliv_fee, 267 COUNT(*) as class_count 268 FROM dtb_products_class 269 WHERE del_flg = 0 $where_products_class 270 GROUP BY product_id 271 ) AS T4 272 ON dtb_products.product_id = T4.product_id 273 LEFT JOIN dtb_maker 274 ON dtb_products.maker_id = dtb_maker.maker_id 275 ) AS alldtl 276 __EOS__; 277 278 return $sql; 279 } 218 280 } -
branches/version-2_13-dev/data/class/db/dbfactory/SC_DB_DBFactory_PGSQL.php
r23295 r23422 283 283 return $objQuery->getCol($col, $from, $where); 284 284 } 285 286 /** 287 * 商品詳細の SQL を取得する. 288 * 289 * PostgreSQL 用にチューニング。 290 * @param string $where_products_class 商品規格情報の WHERE 句 291 * @return string 商品詳細の SQL 292 */ 293 public function alldtlSQL($where_products_class = '') 294 { 295 if (!SC_Utils_Ex::isBlank($where_products_class)) { 296 $where_products_class = 'AND (' . $where_products_class . ')'; 297 } 298 /* 299 * point_rate, deliv_fee は商品規格(dtb_products_class)ごとに保持しているが, 300 * 商品(dtb_products)ごとの設定なので MAX のみを取得する. 301 */ 302 $sub_base = "FROM dtb_products_class WHERE del_flg = 0 AND product_id = dtb_products.product_id $where_products_class"; 303 $sql = <<< __EOS__ 304 ( 305 SELECT 306 dtb_products.* 307 ,dtb_maker.name AS maker_name 308 ,(SELECT MIN(product_code) $sub_base) AS product_code_min 309 ,(SELECT MAX(product_code) $sub_base) AS product_code_max 310 ,(SELECT MIN(price01) $sub_base) AS price01_min 311 ,(SELECT MAX(price01) $sub_base) AS price01_max 312 ,(SELECT MIN(price02) $sub_base) AS price02_min 313 ,(SELECT MAX(price02) $sub_base) AS price02_max 314 ,(SELECT MIN(stock) $sub_base) AS stock_min 315 ,(SELECT MAX(stock) $sub_base) AS stock_max 316 ,(SELECT MIN(stock_unlimited) $sub_base) AS stock_unlimited_min 317 ,(SELECT MAX(stock_unlimited) $sub_base) AS stock_unlimited_max 318 ,(SELECT MAX(point_rate) $sub_base) AS point_rate 319 ,(SELECT MAX(deliv_fee) $sub_base) AS deliv_fee 320 ,(SELECT COUNT(*) $sub_base) AS class_count 321 FROM dtb_products 322 LEFT JOIN dtb_maker 323 ON dtb_products.maker_id = dtb_maker.maker_id 324 WHERE EXISTS(SELECT * $sub_base) 325 ) AS alldtl 326 __EOS__; 327 328 return $sql; 329 } 285 330 }
Note: See TracChangeset
for help on using the changeset viewer.