sorting search or advanced search

[keat]

When searching for an item, the results contain part numbers and descriptions.

eg.

We have a roll of tape with the part number T30, we also sell tool sets which contains a T30 socket or screw driver.

So searching for T30, reveals a number of items.

How would I go about adding "sort by product code" in the search results, would this be even possible ??

[bsmither]

Fortunately, there is a hook that will allow for additional sorters on search results.

But note that only columns found in the CubeCart_inventory table can be added as sorters. (There may be other hooks that can ORDER BY database search queries using custom expressions.)

In admin, Manage Hooks, Code Snippets tab, click Add Snippet.

Enabled: checked
Unique ID: [email protected]    (Note: 32 chars max)
Execution Order: 99
Description: Adds Inventory product_code to search results sorter list
Trigger: class.catalogue.product_sort
Version: 1.0
Author: https://forums.cubecart.com/topic/51662-sorting-search-or-advanced-search/
PHP Code:
<?php
$sorters['product_code'] = $GLOBALS['language']->catalogue['product_code'];

Save.

Test.

[keat]

I just tried to add the snippet, but I can't see the trigger

class.catalogue.product_sort

I found this in GitHub

https://github.com/cubecart/v6/commit/4c61aaf128bb5c58cde8fb958e0aac1013d0f579

Now the option appears in the drop down, however, it doesn't seem to have any effect.

 

[bsmither]

If the hook wasn't there, then you must be running a CC6 less than CC6012.

If so, please let us know what exact version you are running and I will see if there is a problem with trying to sort on 'product_code' for that version.

[keat]

I'm still on 6.10

[Noodleman]

you can manually add the hook. just copy/paste it into the right location

[bsmither]

"Now the option appears in the drop down, however, it doesn't seem to have any effect."

My initial experiments with CC6010 (after having incorporated the code that adds to the sorter list by product_code, directly where the hook would be if this were CC6012) indicates that CC6010 will sort the results by the product's 'product_code'.

In your experiments, try sorting by Product Code in both High to Low and Low to High. See if the list reverses itself.

Also, enable debugging. Then, in the list of queries shown in the debug section, find (near query [29]) this query:

Line split for ease of readability:

[29] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_inventory` WHERE `product_id` IN (1,8,9,15) 
AND CubeCart_inventory.status = '1' AND ((CubeCart_inventory.stock_level > 0 AND CubeCart_inventory.use_stock_level = 1) 
OR CubeCart_inventory.use_stock_level = 0) 
ORDER BY `product_code` ASC LIMIT 12 OFFSET 0; -- (0.015625 sec) [CACHE WRITE]

 

[keat]

This is what I got when I tried to change high to low or low to high

 

 

 

 

[218] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1247' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000195980072021 sec) [CACHE WRITE]
[219] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '1965' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000262975692749 sec) [CACHE WRITE]
[220] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '1965' ; -- (0.000243902206421 sec) [CACHE WRITE]
[221] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000243902206421 sec) [CACHE READ]
[222] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1965' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000194072723389 sec) [CACHE WRITE]
[223] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '1304' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000262975692749 sec) [CACHE WRITE]
[224] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '1304' ; -- (0.000235795974731 sec) [CACHE WRITE]
[225] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000235795974731 sec) [CACHE READ]
[226] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1304' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000192165374756 sec) [CACHE WRITE]
[227] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '64' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000271081924438 sec) [CACHE WRITE]
[228] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '64' ; -- (0.000221014022827 sec) [CACHE WRITE]
[229] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000221014022827 sec) [CACHE READ]
[230] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '64' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000200033187866 sec) [CACHE WRITE]
[231] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '1924' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000289916992188 sec) [CACHE WRITE]
[232] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '1924' ; -- (0.000228881835938 sec) [CACHE WRITE]
[233] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000228881835938 sec) [CACHE READ]
[234] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1924' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000207185745239 sec) [CACHE WRITE]
[235] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '1925' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000261068344116 sec) [CACHE WRITE]
[236] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '1925' ; -- (0.000224113464355 sec) [CACHE WRITE]
[237] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000224113464355 sec) [CACHE READ]
[238] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1925' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000192880630493 sec) [CACHE WRITE]
[239] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '1459' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000274896621704 sec) [CACHE WRITE]
[240] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '1459' ; -- (0.000233888626099 sec) [CACHE WRITE]
[241] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000233888626099 sec) [CACHE READ]
[242] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '1459' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000188827514648 sec) [CACHE WRITE]
[243] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '535' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000275850296021 sec) [CACHE WRITE]
[244] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '535' ; -- (0.000231981277466 sec) [CACHE WRITE]
[245] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000231981277466 sec) [CACHE READ]
[246] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '535' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000211954116821 sec) [CACHE WRITE]
[247] SELECT CI.* , C.status FROM `CubeCart_category_index` AS CI, `CubeCart_category` AS C WHERE CI.product_id = '235' AND CI.cat_id = C.cat_id ORDER BY CI.product_id -- (0.000266075134277 sec) [CACHE WRITE]
[248] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '235' ; -- (0.000238180160522 sec) [CACHE WRITE]
[249] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000238180160522 sec) [CACHE READ]
[250] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '235' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000195026397705 sec) [CACHE WRITE]

[bsmither]

The query we are looking for would be much higher in the list -- probably query number somewhere between 25 to 35.

[keat]

Here are the first 50 lines. Hopefully, what we are looking for is in here ?

 

Queries (216):
[1] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = '237dde0e824085c50ced698e6b367a19' LIMIT 1; -- (0.000606060028076 sec) [NOT CACHED]
[2] SELECT * FROM `CubeCart_hooks` WHERE CubeCart_hooks.enabled = '1' ORDER BY priority ASC ; -- (0.000606060028076 sec) [CACHE READ]
[3] SELECT `php_code`, `unique_id`, `description`, `hook_trigger` FROM `CubeCart_code_snippet` WHERE CubeCart_code_snippet.enabled = '1' ORDER BY `priority` ASC ; -- (0.000606060028076 sec) [CACHE READ]
[4] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos' LIMIT 1; -- (0.000606060028076 sec) [CACHE READ]
[5] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'logos' LIMIT 1; -- (0.000606060028076 sec) [CACHE READ]
[6] SELECT `folder` FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1' ; -- (0.000606060028076 sec) [CACHE READ]
[7] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'GBP' ; -- (0.000606060028076 sec) [CACHE READ]
[8] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_customer` WHERE CubeCart_customer.customer_id = '19834' LIMIT 1; -- (0.000606060028076 sec) [CACHE READ]
[9] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'ccss' LIMIT 1; -- (0.000606060028076 sec) [CACHE READ]
[10] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'ccss' LIMIT 1; -- (0.000606060028076 sec) [CACHE READ]
[11] SELECT `ip_address` FROM `CubeCart_ccss_ip_addresses` WHERE CubeCart_ccss_ip_addresses.trusted = '0' ; -- (0.000606060028076 sec) [CACHE READ]
[12] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'T30' ; -- (0.000606060028076 sec) [CACHE READ]
[13] UPDATE `CubeCart_search` SET `hits` = '38' WHERE CubeCart_search.id = '9533'; -- (0.000380039215088 sec) [NOT CACHED]
[14] SELECT `group_id` FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000380039215088 sec) [CACHE READ]
[15] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000380039215088 sec) [CACHE READ]
[16] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000380039215088 sec) [CACHE READ]
[17] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') LIMIT 12 OFFSET 0 -- (0.000380039215088 sec) [CACHE READ]
[18] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.000380039215088 sec) [CACHE READ]
[19] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000380039215088 sec) [CACHE READ]
[20] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'category_product_options' LIMIT 1; -- (0.000380039215088 sec) [CACHE READ]
[21] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4829' ; -- (0.000380039215088 sec) [CACHE READ]
[22] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4829' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000380039215088 sec) [CACHE READ]
[23] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4855' ; -- (0.000380039215088 sec) [CACHE READ]
[24] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4855' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000380039215088 sec) [CACHE READ]
[25] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '5447' ; -- (0.000380039215088 sec) [CACHE READ]
[26] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '5447' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000380039215088 sec) [CACHE READ]
[27] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '6050' ; -- (0.000380039215088 sec) [CACHE READ]
[28] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '6050' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000380039215088 sec) [CACHE READ]
[29] SELECT `option_id`, `option_name`, `option_type`, `option_required`, `priority` FROM `CubeCart_option_group` WHERE `option_id` IN (1,1,1,1,1) ORDER BY `priority` ASC, `option_name` ASC ; -- (0.000380039215088 sec) [CACHE READ]
[30] SELECT * FROM `CubeCart_option_value` WHERE CubeCart_option_value.option_id = '1' AND `value_id` IN (1,7,10,4,5) ORDER BY `priority` ASC, `value_name` ASC ; -- (0.000380039215088 sec) [CACHE READ]
[31] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000380039215088 sec) [CACHE READ]
[32] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '4829' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000380039215088 sec) [CACHE READ]
[33] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '4829' ORDER BY `main_img` DESC LIMIT 1; -- (0.000380039215088 sec) [CACHE READ]
[34] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[35] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[36] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[37] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[38] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[39] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[40] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[41] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[42] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[43] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[44] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000380039215088 sec) [CACHE READ]
[45] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '4829' ; -- (0.000380039215088 sec) [CACHE READ]
[46] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000380039215088 sec) [CACHE READ]
[47] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '4855' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000380039215088 sec) [CACHE READ]
[48] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '4855' ORDER BY `main_img` DESC LIMIT 1; -- (0.000380039215088 sec) [CACHE READ]
[49] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000380039215088 sec) [CACHE READ]
[50] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000380039215088 sec) [CACHE READ]

[bsmither]

The queries above suggest that this is the first page of results after having received a request to search on 'T30' -- which, doesn't seem to be working as seen in [17] and [18] that have no values for the RLIKE expression.

Please verify that the above debug comes from after having made the choice to sort by 'Product Code' after getting the initial search results.

[keat]

I thought i tried that, but here it is again.

I chose to sort product_code, high to low, low to high and back again.

 

[1] SHOW COLUMNS FROM CubeCart_sessions; -- (0.00100493431091 sec) [CACHE WRITE]
[2] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = '237dde0e824085c50ced698e6b367a19' LIMIT 1; -- (0.000505924224854 sec) [NOT CACHED]
[3] SELECT FOUND_ROWS() as Count; -- (0.000130176544189 sec) [NOT CACHED]
[4] SHOW COLUMNS FROM CubeCart_hooks; -- (0.000916004180908 sec) [CACHE WRITE]
[5] SELECT * FROM `CubeCart_hooks` WHERE CubeCart_hooks.enabled = '1' ORDER BY priority ASC ; -- (0.000521898269653 sec) [CACHE WRITE]
[6] SHOW COLUMNS FROM CubeCart_code_snippet; -- (0.000680923461914 sec) [CACHE WRITE]
[7] SELECT `php_code`, `unique_id`, `description`, `hook_trigger` FROM `CubeCart_code_snippet` WHERE CubeCart_code_snippet.enabled = '1' ORDER BY `priority` ASC ; -- (0.000389814376831 sec) [CACHE WRITE]
[8] SHOW COLUMNS FROM CubeCart_seo_urls; -- (0.000720024108887 sec) [CACHE WRITE]
[9] SELECT * FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.path = 'search' ; -- (0.000339984893799 sec) [CACHE WRITE]
[10] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos' LIMIT 1; -- (0.000401973724365 sec) [CACHE WRITE]
[11] SELECT FOUND_ROWS() as Count; -- (0.000109910964966 sec) [NOT CACHED]
[12] SHOW COLUMNS FROM CubeCart_modules; -- (0.000743865966797 sec) [CACHE WRITE]
[13] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'logos' LIMIT 1; -- (0.000743865966797 sec) [CACHE READ]
[14] SELECT `folder` FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1' ; -- (0.000743865966797 sec) [CACHE READ]
[15] SHOW COLUMNS FROM CubeCart_currency; -- (0.000882863998413 sec) [CACHE WRITE]
[16] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'GBP' ; -- (0.000344038009644 sec) [CACHE WRITE]
[17] SHOW COLUMNS FROM CubeCart_customer; -- (0.00121998786926 sec) [CACHE WRITE]
[18] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_customer` WHERE CubeCart_customer.customer_id = '19834' LIMIT 1; -- (0.000427961349487 sec) [CACHE WRITE]
[19] SELECT FOUND_ROWS() as Count; -- (9.89437103271E-5 sec) [NOT CACHED]
[20] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'ccss' LIMIT 1; -- (0.000405073165894 sec) [CACHE WRITE]
[21] SELECT FOUND_ROWS() as Count; -- (0.000102996826172 sec) [NOT CACHED]
[22] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'ccss' LIMIT 1; -- (0.000275850296021 sec) [CACHE WRITE]
[23] SELECT FOUND_ROWS() as Count; -- (9.48905944824E-5 sec) [NOT CACHED]
[24] SHOW COLUMNS FROM CubeCart_ccss_ip_addresses; -- (0.000633955001831 sec) [CACHE WRITE]
[25] SELECT `ip_address` FROM `CubeCart_ccss_ip_addresses` WHERE CubeCart_ccss_ip_addresses.trusted = '0' ; -- (0.000633955001831 sec) [CACHE READ]
[26] SHOW COLUMNS FROM CubeCart_search; -- (0.000480175018311 sec) [CACHE WRITE]
[27] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'T30' ; -- (0.00773596763611 sec) [CACHE WRITE]
[28] UPDATE `CubeCart_search` SET `hits` = '40' WHERE CubeCart_search.id = '9533'; -- (0.000241994857788 sec) [NOT CACHED]
[29] SHOW COLUMNS FROM CubeCart_customer_membership; -- (0.000595092773438 sec) [CACHE WRITE]
[30] SELECT `group_id` FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000595092773438 sec) [CACHE READ]
[31] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000423192977905 sec) [CACHE WRITE]
[32] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.00036096572876 sec) [CACHE WRITE]
[33] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') LIMIT 12 OFFSET 0 -- (0.090763092041 sec) [CACHE WRITE]
[34] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.0940189361572 sec) [CACHE WRITE]
[35] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000530004501343 sec) [CACHE WRITE]
[36] SELECT FOUND_ROWS() as Count; -- (0.000102996826172 sec) [NOT CACHED]
[37] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'category_product_options' LIMIT 1; -- (0.000244140625 sec) [CACHE WRITE]
[38] SELECT FOUND_ROWS() as Count; -- (9.29832458496E-5 sec) [NOT CACHED]
[39] SHOW COLUMNS FROM CubeCart_options_set_product; -- (0.000678062438965 sec) [CACHE WRITE]
[40] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4829' ; -- (0.000678062438965 sec) [CACHE READ]
[41] SHOW COLUMNS FROM CubeCart_option_assign; -- (0.000653982162476 sec) [CACHE WRITE]
[42] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4829' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000653982162476 sec) [CACHE READ]
[43] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4855' ; -- (0.000653982162476 sec) [CACHE READ]
[44] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4855' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000653982162476 sec) [CACHE READ]
[45] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '5447' ; -- (0.000653982162476 sec) [CACHE READ]
[46] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '5447' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000653982162476 sec) [CACHE READ]
[47] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '6050' ; -- (0.000653982162476 sec) [CACHE READ]
[48] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '6050' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000806093215942 sec) [CACHE WRITE]
[49] SHOW COLUMNS FROM CubeCart_option_group; -- (0.000576019287109 sec) [CACHE WRITE]
[50] SELECT `option_id`, `option_name`, `option_type`, `option_required`, `priority` FROM `CubeCart_option_group` WHERE `option_id` IN (1,1,1,1,1) ORDER BY `priority` ASC, `option_name` ASC ; -- (0.000288963317871 sec) [CACHE WRITE]
[51] SHOW COLUMNS FROM CubeCart_option_value; -- (0.000489950180054 sec) [CACHE WRITE]
[52] SELECT * FROM `CubeCart_option_value` WHERE CubeCart_option_value.option_id = '1' AND `value_id` IN (1,7,10,4,5) ORDER BY `priority` ASC, `value_name` ASC ; -- (0.000369071960449 sec) [CACHE WRITE]
[53] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000369071960449 sec) [CACHE READ]
[54] SHOW COLUMNS FROM CubeCart_pricing_quantity; -- (0.000607013702393 sec) [CACHE WRITE]
[55] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '4829' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000607013702393 sec) [CACHE READ]
[56] SHOW COLUMNS FROM CubeCart_image_index; -- (0.000488042831421 sec) [CACHE WRITE]
[57] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '4829' ORDER BY `main_img` DESC LIMIT 1; -- (0.000266075134277 sec) [CACHE WRITE]
[58] SELECT FOUND_ROWS() as Count; -- (9.70363616943E-5 sec) [NOT CACHED]
[59] SHOW COLUMNS FROM CubeCart_filemanager; -- (0.000726938247681 sec) [CACHE WRITE]
[60] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE WRITE]
[61] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[62] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[63] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[64] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[65] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[66] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[67] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[68] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[69] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
[70] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000298976898193 sec) [CACHE READ]
 

[bsmither]

Not finding what I am looking for.

Try this: view a Category that has multiple products in it. Then choose to sort by Product Code. (That is, let's avoid using CubeCart's "Search" for now.)

[keat]

Never thought of that !!

That actually works.

[bsmither]

Ok, so now we need to figure out how to apply this to Search listings.

[bsmither]

Query [33] is of some concern. There are suppose to be search words in the query, but there are none.

Are you entering any words to search on?

[keat]

These results are from a standard search, searching the term T30.

Then switching between product code high-low then low-high.

 

MySQL
Queries (217):
[1] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_sessions` WHERE CubeCart_sessions.session_id = '237dde0e824085c50ced698e6b367a19' LIMIT 1; -- (0.000626802444458 sec) [NOT CACHED]
[2] SELECT * FROM `CubeCart_hooks` WHERE CubeCart_hooks.enabled = '1' ORDER BY priority ASC ; -- (0.000626802444458 sec) [CACHE READ]
[3] SELECT `php_code`, `unique_id`, `description`, `hook_trigger` FROM `CubeCart_code_snippet` WHERE CubeCart_code_snippet.enabled = '1' ORDER BY `priority` ASC ; -- (0.000626802444458 sec) [CACHE READ]
[4] SELECT * FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.path = 'search' ; -- (0.000626802444458 sec) [CACHE READ]
[5] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'logos' LIMIT 1; -- (0.000626802444458 sec) [CACHE READ]
[6] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'logos' LIMIT 1; -- (0.000626802444458 sec) [CACHE READ]
[7] SELECT `folder` FROM `CubeCart_modules` WHERE CubeCart_modules.module = 'livehelp' AND CubeCart_modules.status = '1' ; -- (0.000626802444458 sec) [CACHE READ]
[8] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.code = 'GBP' ; -- (0.000626802444458 sec) [CACHE READ]
[9] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_customer` WHERE CubeCart_customer.customer_id = '19834' LIMIT 1; -- (0.000626802444458 sec) [CACHE READ]
[10] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'ccss' LIMIT 1; -- (0.000626802444458 sec) [CACHE READ]
[11] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'ccss' LIMIT 1; -- (0.000626802444458 sec) [CACHE READ]
[12] SELECT `ip_address` FROM `CubeCart_ccss_ip_addresses` WHERE CubeCart_ccss_ip_addresses.trusted = '0' ; -- (0.000626802444458 sec) [CACHE READ]
[13] SELECT `id`, `hits` FROM `CubeCart_search` WHERE CubeCart_search.searchstr = 'T30' ; -- (0.000626802444458 sec) [CACHE READ]
[14] UPDATE `CubeCart_search` SET `hits` = '46' WHERE CubeCart_search.id = '9533'; -- (0.000392913818359 sec) [NOT CACHED]
[15] SELECT `group_id` FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[16] SHOW INDEX FROM `CubeCart_inventory`; -- (0.000392913818359 sec) [CACHE READ]
[17] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.000392913818359 sec) [CACHE READ]
[18] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') LIMIT 12 OFFSET 0 -- (0.000392913818359 sec) [CACHE READ]
[19] 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 (I.name RLIKE '[[::]]' OR I.description RLIKE '[[::]]' OR I.product_code RLIKE '[[::]]') -- (0.000392913818359 sec) [CACHE READ]
[20] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'category_product_options' LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[21] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'category_product_options' LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[22] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4829' ; -- (0.000392913818359 sec) [CACHE READ]
[23] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4829' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000392913818359 sec) [CACHE READ]
[24] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '4855' ; -- (0.000392913818359 sec) [CACHE READ]
[25] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '4855' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000392913818359 sec) [CACHE READ]
[26] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '5447' ; -- (0.000392913818359 sec) [CACHE READ]
[27] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '5447' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000392913818359 sec) [CACHE READ]
[28] SELECT `set_id` FROM `CubeCart_options_set_product` WHERE CubeCart_options_set_product.product_id = '6050' ; -- (0.000392913818359 sec) [CACHE READ]
[29] SELECT * FROM `CubeCart_option_assign` WHERE CubeCart_option_assign.product = '6050' AND CubeCart_option_assign.set_member_id = '0' AND CubeCart_option_assign.set_enabled = '1' ; -- (0.000392913818359 sec) [CACHE READ]
[30] SELECT `option_id`, `option_name`, `option_type`, `option_required`, `priority` FROM `CubeCart_option_group` WHERE `option_id` IN (1,1,1,1,1) ORDER BY `priority` ASC, `option_name` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[31] SELECT * FROM `CubeCart_option_value` WHERE CubeCart_option_value.option_id = '1' AND `value_id` IN (1,7,10,4,5) ORDER BY `priority` ASC, `value_name` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[32] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[33] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '4829' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[34] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '4829' ORDER BY `main_img` DESC LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[35] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[36] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[37] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[38] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[39] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[40] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[41] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[42] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[43] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[44] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[45] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2195' ; -- (0.000392913818359 sec) [CACHE READ]
[46] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '4829' ; -- (0.000392913818359 sec) [CACHE READ]
[47] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[48] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '4855' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[49] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '4855' ORDER BY `main_img` DESC LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[50] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[51] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[52] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[53] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[54] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[55] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[56] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[57] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[58] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[59] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[60] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2671' ; -- (0.000392913818359 sec) [CACHE READ]
[61] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '4855' ; -- (0.000392913818359 sec) [CACHE READ]
[62] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[63] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '5447' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[64] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '5447' ORDER BY `main_img` DESC LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[65] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[66] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[67] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[68] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[69] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[70] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[71] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[72] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[73] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[74] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[75] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2691' ; -- (0.000392913818359 sec) [CACHE READ]
[76] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '5447' ; -- (0.000392913818359 sec) [CACHE READ]
[77] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[78] SELECT `quantity`, `price` FROM `CubeCart_pricing_quantity` WHERE CubeCart_pricing_quantity.product_id = '6050' AND CubeCart_pricing_quantity.group_id = '0' ORDER BY `quantity` ASC, `price` ASC ; -- (0.000392913818359 sec) [CACHE READ]
[79] SELECT SQL_CALC_FOUND_ROWS * FROM `CubeCart_image_index` WHERE CubeCart_image_index.product_id = '6050' ORDER BY `main_img` DESC LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[80] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[81] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[82] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[83] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[84] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[85] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[86] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[87] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[88] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[89] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[90] SELECT * FROM `CubeCart_filemanager` WHERE CubeCart_filemanager.file_id = '2421' ; -- (0.000392913818359 sec) [CACHE READ]
[91] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '6050' ; -- (0.000392913818359 sec) [CACHE READ]
[92] SELECT SQL_CALC_FOUND_ROWS `array` FROM `CubeCart_config` WHERE CubeCart_config.name = 'languages' LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[93] SELECT SQL_CALC_FOUND_ROWS `status`, `countries` FROM `CubeCart_modules` WHERE CubeCart_modules.folder = 'languages' LIMIT 1; -- (0.000392913818359 sec) [CACHE READ]
[94] SELECT * FROM `CubeCart_currency` WHERE CubeCart_currency.active = '1' ; -- (0.000392913818359 sec) [CACHE READ]
[95] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'login' ; -- (0.000392913818359 sec) [CACHE READ]
[96] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'register' ; -- (0.000392913818359 sec) [CACHE READ]
[97] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'search' ; -- (0.000392913818359 sec) [CACHE READ]
[98] SELECT * FROM `CubeCart_customer_membership` WHERE CubeCart_customer_membership.customer_id = '19834' ; -- (0.000392913818359 sec) [CACHE READ]
[99] SELECT `price`, `sale_price`, `product_id`,`description`,`name`, `price`-`sale_price` AS `saving` FROM `CubeCart_inventory` WHERE `price` > `sale_price` AND `sale_price` > 0 AND `status` = '1' LIMIT 6 -- (0.000392913818359 sec) [CACHE READ]
[100] SELECT `path` FROM `CubeCart_seo_urls` WHERE CubeCart_seo_urls.type = 'prod' AND CubeCart_seo_urls.item_id = '522' ; -- (0.000392913818359 sec) [CACHE READ]
 

[Noodleman]

Pretty sure your running into a known issue with sorting, although I will see if I can walk through the code later and give you the answers to work around it.

 

 

[Noodleman]

Ok, a quick review and I've a better idea of what is going on here. My testing is using CC612,

Adding the code snippit works correctly within the hook class.catalogue.product_sort

$sorters['product_code'] = $GLOBALS['language']->catalogue['product_code'];

This adds the "Product Code" ASC/DESC option to the menu the users see on the storefront. Selecting this item works correctly  as long as you are within a category.  This is important because simply gathering the products within a category is not (well it is, kind of) using "search".

Using search applies some other methods for sorting data, which has known issues. Note in this ticket the example is using the "sale items" page, which does trigger a search and for the purposes of this explination should be considered "using search".
https://github.com/cubecart/v6/issues/821

When I try and use the sort by product_code on the pages that "search", It doesn't work. It's the same bug as described in the github ticket.

I dont have time to get into the details this very second, but the issue in my test system is that the sort code is NEVER called when you are on the sale items page.

				if (isset($_GET['sort']) && is_array($_GET['sort'])) {
					foreach ($_GET['sort'] as $field => $direction) {
						$order[$field] = (strtolower($direction) == 'asc') ? 'ASC' : 'DESC';
						break;
					}
				} else {
					$order['name'] = 'ASC';
				}

 

the $_GET variable contains only this data after the sort selection is made:

array(2) {
  ["_a"]=>
  string(9) "saleitems"
  ["cat_id"]=>
  string(4) "sale"
}

 

I suspect there is a redirect somewhere which is causing the data to not be processed OR the data is never submitted correctly.

Note all the above is specific to "sale items", there is a SIMILAR, but different behaviour if you manually use the search.

 

Summing up product code sorting works OK when:

1) you are in a category and use the menu

2) you use search and used the menu to sort.

I can't get it to work without code changes when viewing sale items.

[Noodleman]

Well, The issue with no sorting on the sale items page is a confirmed bug in my opinion.

When you change a sort order on the sale item page, the newly selected sort criteria is POSTED via a form. The posted elements are processed in cubecart.class.php and then it converts those to URL parameters and redirects.

httpredir('?'.http_build_query($query, null, '&'));

httpredir('?'.http_build_query($query, null, '&'));

The parameters are correct:

_a=saleitems&cat_id=sale&sort%5Bproduct_code%5D=DESC

 

the function that builds the sorting selections checks the URL parameters, but at the point it checks them the information in the URL above has already been dropped. This suggests there is another redirect in play somewhere which is taking you back to the standard sale items page.

so the next step is to find out where that other redirection is.

 

 

 

Update:

I tracked the problem back to the seo.class.php file. There is a redirect which when removed solves the search issues.

httpredir($seo_url);

 

[bsmither]

In CC6012, I have demonstrated to myself that the Full Search page can accept and use the sort:
search.html?_a=category&search[keywords]=Test&search[priceMin]=&search[priceMax]=&sort[product_code]=DESC

HOWEVER, the installation I tested also has Issue #1203 implemented, but should not interfere with testing.

HOWEVER, there are two bounces when viewing the Sale Items
POST /index.php?_a=saleitems (payload: x-www-form-urlencoded)
GET /index.php?_a=saleitems&cat_id=sale&sort[product_code]=ASC
GET /sale-items.html

I think a key difference (in addition to the previous post) is that the POST from the Search page starts with the SEO version:
POST /search.html?_a=category (payload: multipart/form-data)
GET /search.html?_a=category&search[keywords]=Test&search[priceMin]=&search[priceMax]=&sort[product_code]=DESC

I will try to see if making the link to the Sale Items sorter auto-submit operate the same as the Search Form sorter auto-submit makes any difference.

[bsmither]

Viewing the Sale Items page, the sorters get:

<form class="autosubmit" method="post" action="http://www.example.com/index.php?_a=saleitems">

Viewing a Category page, the sorters get:

<form class="autosubmit" method="post" action="http://www.example.com/test-category-01.html">

So, I think one step to solve is to get the proper seo_path as the Sale Item sorter's <form> action attribute.

[bsmither]

"There is a redirect which when removed solves the search issues."

If that is the statement in SEO->__construct(), then I think removing that clobbers any effort to get back on the seo_path path.

What I am blaming for the moment is that the code at CubeCart->loadPage(), case 'saleitems' wants to add to the $_GET array a token to the 'cat_id' index as a (lazy?) way of setting a flag that this is a saleitems category.

The problem I see is that

1) 'saleitems' is a member of _static_sections and so should never have an id parameter
2) Now that there are _a and cat_id indexes, this matches with PCRE_REQUEST_URI
3) Then, currentPage() uses SEO->SEOable, which sends 'cat_id' to generatePath().
4) Because there is no item_id in CubeCart_seo_urls for type 'saleitems, the URL ultimately returned is in a standard (that is, non-seo) format.

That's the setup. What follows is losing part of the querystring when the code tries to immediately bounce from the standard format to the seo format.

[Noodleman]

Quote

So, I think one step to solve is to get the proper seo_path as the Sale Item sorter's <form> action attribute.

totally agree. This appears to drive the need to rewrite the URL and breaks the sort. I think the change of the URL would be considered a work around for the time being.

Quote

If that is the statement in SEO->__construct(), then I think removing that clobbers any effort to get back on the seo_path path.

Yup, removing it was only for the purpose of testing / validating that was causing the issue. I didnt get any further with this yet. :). To clarify, I don't recommend people do this in their prodution stores

I think at this point we should open a github ticket for the issue as it's definately a bug, then get Al to comment on how he can address it. I think this issue will overlap with some other github requests so may be interesting to get that feedback. I will open it later unless somebody else does it first.

[Noodleman]

Opened ticket:

https://github.com/cubecart/v6/issues