- Timestamp:
- 2014/04/29 17:33:46 (10 years ago)
- Location:
- branches/version-2_13-dev/data/class
- Files:
-
- 6 edited
Legend:
- Unmodified
- Added
- Removed
-
branches/version-2_13-dev/data/class/SC_Product.php
r23230 r23378 82 82 $o_table = $this->arrOrderData['table']; 83 83 $o_order = $this->arrOrderData['order']; 84 $order = <<< __EOS__ 85 ( 86 SELECT $o_col 87 FROM 88 $o_table as T2 89 WHERE T2.product_id = alldtl.product_id 90 ORDER BY T2.$o_col $o_order 91 LIMIT 1 92 ) $o_order, product_id 93 __EOS__; 94 $objQuery->setOrder($order); 84 $objQuery->setOrder("T2.$o_col $o_order"); 85 $objQuery->setLimit(1); 86 $sub_sql = $objQuery->getSqlWithLimitOffset($o_col, "$o_table AS T2", 'T2.product_id = alldtl.product_id'); 87 88 $objQuery->setOrder("($sub_sql) $o_order, product_id"); 95 89 } 96 90 $arrReturn = $objQuery->getCol('alldtl.product_id', $table, '', $arrVal); -
branches/version-2_13-dev/data/class/SC_Query.php
r23373 r23378 1216 1216 } 1217 1217 } 1218 1219 /** 1220 * 構築した SELECT 文を LIMIT OFFSET も含め取得する. 1221 * 1222 * @param string SELECT 文に含めるカラム名 1223 * @param string SELECT 文に含めるテーブル名 1224 * @param string SELECT 文に含める WHERE 句 1225 * @return string 構築済みの SELECT 文 1226 */ 1227 function getSqlWithLimitOffset($cols, $from = '', $where = '') 1228 { 1229 $sql = $this->getSql($cols, $from, $where); 1230 $offset = $this->conn->offset; 1231 $limit = $this->conn->limit; 1232 $this->setLimitOffset(0, 0); 1233 1234 return $this->dbFactory->addLimitOffset($sql, $limit, $offset); 1235 } 1218 1236 } -
branches/version-2_13-dev/data/class/api/operations/ItemSearch.php
r23124 r23378 114 114 protected function getProductsList($searchCondition, $disp_number, $startno, $linemax, &$objProduct) 115 115 { 116 $objQuery =& SC_Query_Ex::getSingletonInstance(); 117 116 118 $arrOrderVal = array(); 117 119 118 $objQuery =& SC_Query_Ex::getSingletonInstance();119 120 // 表示順序 120 121 switch ($searchCondition['orderby']) { … … 147 148 $dtb_product_categories = 'dtb_product_categories'; 148 149 } 149 $order = <<< __EOS__ 150 ( 151 SELECT 152 T3.rank * 2147483648 + T2.rank 153 FROM 154 $dtb_product_categories T2 155 JOIN dtb_category T3 156 ON T2.category_id = T3.category_id 157 WHERE T2.product_id = alldtl.product_id 158 ORDER BY T3.rank DESC, T2.rank DESC 159 LIMIT 1 160 ) DESC 161 ,product_id DESC 162 __EOS__; 163 $objQuery->setOrder($order); 150 $col = 'T3.rank * 2147483648 + T2.rank'; 151 $from = "$dtb_product_categories T2 JOIN dtb_category T3 ON T2.category_id = T3.category_id"; 152 $where = 'T2.product_id = alldtl.product_id'; 153 $objQuery->setOrder('T3.rank DESC, T2.rank DESC'); 154 $objQuery->setLimit(1); 155 $sub_sql = $objQuery->getSqlWithLimitOffset($col, $from, $where); 156 157 $objQuery->setOrder("($sub_sql) DESC ,product_id DESC"); 164 158 break; 165 159 } -
branches/version-2_13-dev/data/class/db/SC_DB_DBFactory.php
r23124 r23378 195 195 return $objManager->listTables(); 196 196 } 197 198 /** 199 * SQL 文に OFFSET, LIMIT を付加する。 200 * 201 * @param string 元の SQL 文 202 * @param integer LIMIT 203 * @param integer OFFSET 204 * @return string 付加後の SQL 文 205 */ 206 function addLimitOffset($sql, $limit = 0, $offset = 0) 207 { 208 if ($limit != 0) { 209 $sql .= " LIMIT $limit"; 210 } 211 if (strlen($offset) === 0) { 212 $offset = 0; 213 } 214 $sql .= " OFFSET $offset"; 215 216 return $sql; 217 } 197 218 } -
branches/version-2_13-dev/data/class/pages/admin/LC_Page_Admin_Home.php
r23124 r23378 251 251 $objQuery =& SC_Query_Ex::getSingletonInstance(); 252 252 253 $sql = <<< __EOS__ 254 SELECT 255 ord.order_id, 256 ord.customer_id, 257 ord.order_name01 AS name01, 258 ord.order_name02 AS name02, 259 ord.total, 260 ord.create_date, 261 (SELECT 262 det.product_name 263 FROM 264 dtb_order_detail AS det 265 WHERE 266 ord.order_id = det.order_id 267 ORDER BY det.order_detail_id 268 LIMIT 1 269 ) AS product_name, 270 (SELECT 271 pay.payment_method 272 FROM 273 dtb_payment AS pay 274 WHERE 275 ord.payment_id = pay.payment_id 276 ) AS payment_method 277 FROM ( 278 SELECT 279 order_id, 280 customer_id, 281 order_name01, 282 order_name02, 283 total, 284 create_date, 285 payment_id 286 FROM 287 dtb_order AS ord 288 WHERE 289 del_flg = 0 AND status <> ? 290 ORDER BY 291 create_date DESC LIMIT 10 OFFSET 0 292 ) AS ord 253 $objQuery->setOrder('order_detail_id'); 254 $objQuery->setLimit(1); 255 $sql_product_name = $objQuery->getSqlWithLimitOffset('product_name', 'dtb_order_detail', 'order_id = dtb_order.order_id'); 256 257 $cols = <<< __EOS__ 258 dtb_order.order_id, 259 dtb_order.customer_id, 260 dtb_order.order_name01 AS name01, 261 dtb_order.order_name02 AS name02, 262 dtb_order.total, 263 dtb_order.create_date, 264 ($sql_product_name) AS product_name, 265 (SELECT 266 pay.payment_method 267 FROM 268 dtb_payment AS pay 269 WHERE 270 dtb_order.payment_id = pay.payment_id 271 ) AS payment_method 293 272 __EOS__; 294 $arrNewOrder = $objQuery->getAll($sql, ORDER_CANCEL); 273 $from = 'dtb_order'; 274 $where = 'del_flg = 0 AND status <> ?'; 275 $objQuery->setOrder('create_date DESC'); 276 $objQuery->setLimit(10); 277 $arrNewOrder = $objQuery->select($cols, $from, $where, ORDER_CANCEL); 278 295 279 foreach ($arrNewOrder as $key => $val) { 296 280 $arrNewOrder[$key]['create_date'] = str_replace('-', '/', substr($val['create_date'], 0,19)); -
branches/version-2_13-dev/data/class/pages/products/LC_Page_Products_List.php
r23256 r23378 213 213 public function lfGetProductsList($searchCondition, $disp_number, $startno, &$objProduct) 214 214 { 215 $objQuery =& SC_Query_Ex::getSingletonInstance(); 216 215 217 $arrOrderVal = array(); 216 218 217 $objQuery =& SC_Query_Ex::getSingletonInstance();218 219 // 表示順序 219 220 switch ($this->orderby) { … … 235 236 $dtb_product_categories = 'dtb_product_categories'; 236 237 } 237 $order = <<< __EOS__ 238 ( 239 SELECT 240 T3.rank * 2147483648 + T2.rank 241 FROM 242 $dtb_product_categories T2 243 JOIN dtb_category T3 244 ON T2.category_id = T3.category_id 245 WHERE T2.product_id = alldtl.product_id 246 ORDER BY T3.rank DESC, T2.rank DESC 247 LIMIT 1 248 ) DESC 249 ,product_id DESC 250 __EOS__; 251 $objQuery->setOrder($order); 238 $col = 'T3.rank * 2147483648 + T2.rank'; 239 $from = "$dtb_product_categories T2 JOIN dtb_category T3 ON T2.category_id = T3.category_id"; 240 $where = 'T2.product_id = alldtl.product_id'; 241 $objQuery->setOrder('T3.rank DESC, T2.rank DESC'); 242 $objQuery->setLimit(1); 243 $sub_sql = $objQuery->getSqlWithLimitOffset($col, $from, $where); 244 245 $objQuery->setOrder("($sub_sql) DESC ,product_id DESC"); 252 246 break; 253 247 }
Note: See TracChangeset
for help on using the changeset viewer.