I have installed Manadev Multiple Select extension which i thought was pretty good for a while until i noticed the sort by price didn't really work. I cannot quite figure out why however have enabled debugging and although it is a longshot was wondering if anyone could notice what could be wrong between these 2 queries or have experienced this issue with this plugin:
Working - Sort By Relavence
SELECT DISTINCT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `stock_status_index`.`stock_status` AS `is_salable`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`weight`, `e`.`image`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`url_key`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`swatch_image`, `e`.`tax_class_id`, `e`.`brand`, `e`.`brand_value`, `e`.`capacity_litres`, `e`.`capacity_load`, `e`.`cycle_time`, `e`.`dimension_basket_size`, `e`.`dimension_basket_size_value`, `e`.`dimension_clear_entry`, `e`.`dimension_depth`, `e`.`dimension_height`, `e`.`dimension_width`, `e`.`mpn`, `e`.`output_racks`, `e`.`pressure_min`, `e`.`type_chemical`, `e`.`type_loading`, `e`.`type_loading_value`, `e`.`type_packaging`, `e`.`type_stackable`, `e`.`type_stackable_value`, `e`.`warewasher_break_tank`, `e`.`warewasher_waste`, `e`.`warewasher_waste_value`, `e`.`warewasher_water_softener`, `e`.`warewasher_water_softener_value`, `e`.`warranty`, `e`.`wras_approved`, `e`.`availability`, `e`.`availability_value`, `e`.`cycle_type`, `e`.`cycle_type_value`, `e`.`water_supply`, `e`.`water_supply_value`, `e`.`laundry_waste`, `e`.`power_consumption_kw`, `e`.`material`, `e`.`power_dd`, `e`.`power_dd_value`, `e`.`type_broom`, `e`.`type_clothing`, `e`.`type_mop`, `e`.`type_paper`, `e`.`type_paper_value`, `e`.`type_sack`, `e`.`type_sack_value`, `e`.`type_sink`, `e`.`type_glassware`, `e`.`type_refrigeration`, `e`.`colour`, `e`.`colour_value`, `e`.`chsa_approved`, `e`.`chsa_approved_value`, `e`.`size_clothing`, `e`.`size_clothing_value`, `e`.`dimension_napkin`, `e`.`dimension_napkin_value`, `e`.`crockery_shape`, `e`.`glassware_ce`, `e`.`glassware_ce_value`, `e`.`glassware_headkeeper`, `e`.`glassware_headkeeper_value`, `e`.`cutlery_gauge`, `e`.`cutlery_gauge_value`, `e`.`crockery_style`, `e`.`crockery_style_value`, `e`.`glassware_range`, `e`.`glassware_range_value`, `e`.`cutlery_range`, `e`.`cutlery_range_value`, `e`.`capacity_centilitres`, `e`.`dimension_length`, `e`.`unit_of_sale`, `e`.`dimension_diameter`, `e`.`size_menu`, `e`.`size_menu_value`, `e`.`menu_pages`, `e`.`menu_pages_value`, `e`.`type_slicer`, `e`.`size_gastronorm`, `e`.`size_gastronorm_value`, `e`.`type_cookware`, `e`.`type_cookware_value`, `e`.`cookware_compatibility`, `e`.`non_stick`, `e`.`non_stick_value`, `e`.`type_knives`, `e`.`type_utensils`, `e`.`refrigeration_door_type`, `e`.`refrigeration_door_type_value`, `e`.`refrigeration_door_qty`, `e`.`refrigeration_door_qty_value`, `e`.`refrigeration_temp`, `e`.`refrigeration_door_hinge`, `e`.`refrigeration_door_hinge_value`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`
FROM `catalog_product_flat_2` AS `e`
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '2' AND price_index.customer_group_id = 0
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='2' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='39'
INNER JOIN `search_tmp_59e61cdf000e18_99744574` AS `search_result` ON e.entity_id = search_result.entity_id
WHERE (stock_status_index.stock_status = 1) ORDER BY `e`.`name` DESC
LIMIT 12
Not Working - Sort By Price
SELECT DISTINCT 1 AS `status`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`created_at`, `e`.`updated_at`, `e`.`sku`, `stock_status_index`.`stock_status` AS `is_salable`, `e`.`name`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`weight`, `e`.`image`, `e`.`small_image`, `e`.`thumbnail`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`url_key`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`swatch_image`, `e`.`tax_class_id`, `e`.`brand`, `e`.`brand_value`, `e`.`capacity_litres`, `e`.`capacity_load`, `e`.`cycle_time`, `e`.`dimension_basket_size`, `e`.`dimension_basket_size_value`, `e`.`dimension_clear_entry`, `e`.`dimension_depth`, `e`.`dimension_height`, `e`.`dimension_width`, `e`.`mpn`, `e`.`output_racks`, `e`.`pressure_min`, `e`.`type_chemical`, `e`.`type_loading`, `e`.`type_loading_value`, `e`.`type_packaging`, `e`.`type_stackable`, `e`.`type_stackable_value`, `e`.`warewasher_break_tank`, `e`.`warewasher_waste`, `e`.`warewasher_waste_value`, `e`.`warewasher_water_softener`, `e`.`warewasher_water_softener_value`, `e`.`warranty`, `e`.`wras_approved`, `e`.`availability`, `e`.`availability_value`, `e`.`cycle_type`, `e`.`cycle_type_value`, `e`.`water_supply`, `e`.`water_supply_value`, `e`.`laundry_waste`, `e`.`power_consumption_kw`, `e`.`material`, `e`.`power_dd`, `e`.`power_dd_value`, `e`.`type_broom`, `e`.`type_clothing`, `e`.`type_mop`, `e`.`type_paper`, `e`.`type_paper_value`, `e`.`type_sack`, `e`.`type_sack_value`, `e`.`type_sink`, `e`.`type_glassware`, `e`.`type_refrigeration`, `e`.`colour`, `e`.`colour_value`, `e`.`chsa_approved`, `e`.`chsa_approved_value`, `e`.`size_clothing`, `e`.`size_clothing_value`, `e`.`dimension_napkin`, `e`.`dimension_napkin_value`, `e`.`crockery_shape`, `e`.`glassware_ce`, `e`.`glassware_ce_value`, `e`.`glassware_headkeeper`, `e`.`glassware_headkeeper_value`, `e`.`cutlery_gauge`, `e`.`cutlery_gauge_value`, `e`.`crockery_style`, `e`.`crockery_style_value`, `e`.`glassware_range`, `e`.`glassware_range_value`, `e`.`cutlery_range`, `e`.`cutlery_range_value`, `e`.`capacity_centilitres`, `e`.`dimension_length`, `e`.`unit_of_sale`, `e`.`dimension_diameter`, `e`.`size_menu`, `e`.`size_menu_value`, `e`.`menu_pages`, `e`.`menu_pages_value`, `e`.`type_slicer`, `e`.`size_gastronorm`, `e`.`size_gastronorm_value`, `e`.`type_cookware`, `e`.`type_cookware_value`, `e`.`cookware_compatibility`, `e`.`non_stick`, `e`.`non_stick_value`, `e`.`type_knives`, `e`.`type_utensils`, `e`.`refrigeration_door_type`, `e`.`refrigeration_door_type_value`, `e`.`refrigeration_door_qty`, `e`.`refrigeration_door_qty_value`, `e`.`refrigeration_temp`, `e`.`refrigeration_door_hinge`, `e`.`refrigeration_door_hinge_value`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `cat_index`.`position` AS `cat_index_position`
FROM `catalog_product_flat_2` AS `e`
INNER JOIN `cataloginventory_stock_status` AS `stock_status_index` ON e.entity_id = stock_status_index.product_id AND stock_status_index.website_id = 0 AND stock_status_index.stock_id = 1
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '2' AND price_index.customer_group_id = 0
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='2' AND cat_index.visibility IN(3, 4) AND cat_index.category_id='39'
INNER JOIN `search_tmp_59e61cf6966ed7_38396838` AS `search_result` ON e.entity_id = search_result.entity_id
WHERE (stock_status_index.stock_status = 1) ORDER BY `price_index`.`min_price` desc
LIMIT 12 OFFSET 12
These should be the same collections sorted in a different way but they are not.
The collections seem to be related to a temp search table which appears to be generated each time like below:
INSERT INTO `search_tmp_59e62048639db5_21877615`
SELECT `main_select`.`entity_id`, MAX(score) AS `relevance`
FROM (SELECT `search_index`.`entity_id`, ((0) + LEAST((MATCH (data_index) AGAINST ('plate*' IN BOOLEAN MODE)), 1000000) * POW(2, search_weight)) AS `score`
FROM `catalogsearch_fulltext_scope2` AS `search_index`
LEFT JOIN `catalog_eav_attribute` AS `cea` ON search_index.attribute_id = cea.attribute_id
LEFT JOIN `cataloginventory_stock_status` AS `stock_index` ON search_index.entity_id = stock_index.product_id AND stock_index.website_id = 0
WHERE (stock_index.stock_status = 1) AND (MATCH (data_index) AGAINST ('plate*' IN BOOLEAN MODE))) AS `main_select` GROUP BY `entity_id` ORDER BY `relevance` DESC
LIMIT 10000
The temp collection table seem to be initially created each time. Then the sort joins to the temporary table and sorts separately however i cannot see anything wrong with the initial table that would suggest it gives a completely wrong collection on sort.
The sorting however seems to work fine within categories and it is just on search that the issue occurs.
