bkessler91 Posted May 8, 2014 Share Posted May 8, 2014 Trying to Sort items in my "Sale Items" category, and is not working. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 8, 2014 Share Posted May 8, 2014 Is the store on page two or higher of sale items, and won't sort on those pages, or won't sort on any page of sale items? Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 8, 2014 Share Posted May 8, 2014 Is this problem on a store running CC5.2.7, as your sig indicates? Or have you installed CC5.2.10? If CC5.2.10, would you please try this. In the file /classes/category.class.php, near line 1253, find (long lines abbreviated): if (($sale = $GLOBALS['db']->query($query)) !== false) { $q2 = sprintf("SELECT I.* FROM %1$sCubeCart_inventory ..... $count = $GLOBALS['db']->query($q2); $this->_category_count = (int)count($count); $this->_category_products = $sale; } On a new line below the closing brace, add: return true; Edit: Umm... I'm not seeing how that would make a difference. Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 10, 2014 Author Share Posted May 10, 2014 Is the store on page two or higher of sale items, and won't sort on those pages, or won't sort on any page of sale items? I am seeing that Sale items will not sort at all. Is this problem on a store running CC5.2.7, as your sig indicates? Or have you installed CC5.2.10? If CC5.2.10, would you please try this. In the file /classes/category.class.php, near line 1253, find (long lines abbreviated): if (($sale = $GLOBALS['db']->query($query)) !== false) { $q2 = sprintf("SELECT I.* FROM %1$sCubeCart_inventory ..... $count = $GLOBALS['db']->query($q2); $this->_category_count = (int)count($count); $this->_category_products = $sale; } On a new line below the closing brace, add: return true; Edit: Umm... I'm not seeing how that would make a difference. I am still running 5.2.7 (I have never done an upgrade myself before, still trying to get the guts to do the compare-merge-upload by myself) Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 14, 2014 Author Share Posted May 14, 2014 Is this problem on a store running CC5.2.7, as your sig indicates? Or have you installed CC5.2.10? If CC5.2.10, would you please try this. In the file /classes/category.class.php, near line 1253, find (long lines abbreviated): if (($sale = $GLOBALS['db']->query($query)) !== false) { $q2 = sprintf("SELECT I.* FROM %1$sCubeCart_inventory ..... $count = $GLOBALS['db']->query($q2); $this->_category_count = (int)count($count); $this->_category_products = $sale; } On a new line below the closing brace, add: return true; Edit: Umm... I'm not seeing how that would make a difference. I could not find anything like this code in 5.2.7 Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 14, 2014 Share Posted May 14, 2014 Sorry. /classes/catalogue.class.php, near line 1241. Enabling debug mode will confirm if the sort parameter is being used. In the debug block, you will see a query that looks like: SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND IF (G.sale_price IS NULL, I.sale_price, G.sale_price) > 0 ORDER BY date_added ASC LIMIT 10 OFFSET 0 where date_added is the choice of sort. Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 14, 2014 Author Share Posted May 14, 2014 I enabled debug mode and tried the drop down sort menu. I see 1111 lines in the MySQLi Queries Heres the first 29 lines MySQLiQueries (1111):[1] SELECT * FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = 'cc89b7199891eacebe8ab7a9e3f001b0' LIMIT 1; -- (0.000344038009644 sec)[2] SELECT COUNT(*) AS Count FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = 'cc89b7199891eacebe8ab7a9e3f001b0'; -- (0.000174045562744 sec) [CACHED][3] SELECT * FROM `CubeCart_lang_strings` WHERE CubeCart_lang_strings.language = 'en-US' ; -- (0.000248193740845 sec) [CACHED][4] SELECT * FROM `CubeCart_hooks` WHERE CubeCart_hooks.enabled = '1' ORDER BY priority ASC ; -- (0.000191926956177 sec) [CACHED][5] SELECT `php_code`, `unique_id`, `description`, `hook_trigger` FROM `CubeCart_code_snippet` WHERE CubeCart_code_snippet.enabled = '1' ORDER BY `priority` ASC ; -- (0.000124931335449 sec) [CACHED][6] SELECT `cat_id`, `cat_name`, `cat_parent_id` FROM `CubeCart_category` ORDER BY `cat_id` DESC ; -- (0.00542402267456 sec) [CACHED][7] SELECT COUNT(*) AS Count FROM `CubeCart_category` ; -- (0.000346183776855 sec) [CACHED][8] SELECT `cat_id`, `cat_name` FROM `CubeCart_category_language` WHERE CubeCart_category_language.language = 'en-US' ; -- (0.000255823135376 sec) [CACHED][9] SELECT `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos' LIMIT 1; -- (0.000304937362671 sec) [CACHED][10] SELECT COUNT(*) AS Count FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos'; -- (0.00017786026001 sec) [CACHED][11] SELECT `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'logos' LIMIT 1; -- (0.000180959701538 sec) [CACHED][12] SELECT `folder` FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1' ; -- (0.000227928161621 sec) [CACHED][13] SELECT COUNT(*) AS Count FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1'; -- (0.00020694732666 sec) [CACHED][14] SELECT `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'olark' LIMIT 1; -- (0.000192880630493 sec) [CACHED][15] SELECT COUNT(*) AS Count FROM `CubeCart_config` WHERE CubeCart_config.name = 'olark'; -- (0.000142097473145 sec) [CACHED][16] SELECT `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'olark' LIMIT 1; -- (0.000159978866577 sec) [CACHED][17] SELECT COUNT(*) AS Count FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'olark'; -- (0.000187873840332 sec) [CACHED][18] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'USD' ; -- (0.000313997268677 sec) [CACHED][19] SELECT COUNT(*) AS Count FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'USD'; -- (0.000169992446899 sec) [CACHED][20] SELECT `customer_id` FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = 'cc89b7199891eacebe8ab7a9e3f001b0' LIMIT 1; -- (0.000549077987671 sec)[21] SELECT COUNT(*) AS Count FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = 'cc89b7199891eacebe8ab7a9e3f001b0'; -- (0.000213861465454 sec) [CACHED][22] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND IF (G.sale_price IS NULL, I.sale_price, G.sale_price) > 0 LIMIT 35 OFFSET 0 -- (0.0111889839172 sec) [CACHED][23] SELECT I.* FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND IF (G.sale_price IS NULL, I.sale_price, G.sale_price) > 0 -- (0.101717233658 sec) [CACHED][24] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '15' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000486850738525 sec) [CACHED][25] SELECT MAX(stock_level) AS `stock_level` FROM `CubeCart_option_matrix` WHERE CubeCart_option_matrix.product_id = '15' AND CubeCart_option_matrix.status = '1' AND CubeCart_option_matrix.use_stock = '1' ; -- (0.000263214111328 sec) [CACHED][26] SELECT COUNT(*) AS Count FROM `CubeCart_option_matrix` WHERE CubeCart_option_matrix.product_id = '15' AND CubeCart_option_matrix.status = '1' AND CubeCart_option_matrix.use_stock = '1'; -- (0.00022292137146 sec) [CACHED][27] SELECT `stock_level` FROM `CubeCart_inventory` WHERE CubeCart_inventory.product_id = '15' LIMIT 1; -- (0.000234127044678 sec) [CACHED][28] SELECT COUNT(*) AS Count FROM `CubeCart_inventory` WHERE CubeCart_inventory.product_id = '15'; -- (0.000179052352905 sec) [CACHED][29] SELECT * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '15' ORDER BY `main_img` DESC LIMIT 1; -- (0.000268936157227 sec) [CACHED] I dont know if this will help you or not... Sorry. /classes/catalogue.class.php, near line 1241. I added return true; and the drop down sort menu still does not work. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 14, 2014 Share Posted May 14, 2014 It's query #22. And this bug has been fixed in CC5.2.10. In the file /classes/catalogue.class.php, near line 1238, edit: Was: $where2 = $this->outOfStockWhere(false, 'I', true); Now: if(is_array($order)) { $order_string = sprintf("ORDER BY %1$s %2$s ",key($order),current($order)); } $where2 = $this->outOfStockWhere(false, 'I', true); Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 14, 2014 Author Share Posted May 14, 2014 That did it. Thanks Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 15, 2014 Author Share Posted May 15, 2014 Here's another one... Sort by "Price" doesn't sort by sale price. But sorts by regular price. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 15, 2014 Share Posted May 15, 2014 Good point. We have a choice to override 'price' with 'sale_price', or add 'Sale Price" to the list of sortable parameters (for all categories, or only when viewing the saleitems category). Let's override. In /classes/catalogue.class.php, near line 1228, edit: Was: } else if (strtolower($search_data) == 'sale') { if (isset($_GET['sort']) && is_array($_GET['sort'])) { foreach ($_GET['sort'] as $field => $direction) { Now: } else if (strtolower($search_data) == 'sale') { if (isset($_GET['sort']) && is_array($_GET['sort'])) { foreach ($_GET['sort'] as $field => $direction) { $field = ($field == 'price') ? 'sale_price' : $field; Quote Link to comment Share on other sites More sharing options...
bkessler91 Posted May 15, 2014 Author Share Posted May 15, 2014 Good point. We have a choice to override 'price' with 'sale_price', or add 'Sale Price" to the list of sortable parameters (for all categories, or only when viewing the saleitems category). Let's override. In /classes/catalogue.class.php, near line 1228, edit: Was: } else if (strtolower($search_data) == 'sale') { if (isset($_GET['sort']) && is_array($_GET['sort'])) { foreach ($_GET['sort'] as $field => $direction) { Now: } else if (strtolower($search_data) == 'sale') { if (isset($_GET['sort']) && is_array($_GET['sort'])) { foreach ($_GET['sort'] as $field => $direction) { $field = ($field == 'price') ? 'sale_price' : $field; Works just fine with 'price' and 'sale_price' Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.