We are working on Magento 2.2.7. On PLP, sort by price is not working. This is because in the query it first sorts the items by entity_id then only using the price field. Example query is given below

SELECT 1 AS status, e.entity_id, e.attribute_set_id, e.type_id, e.created_at, e.updated_at, e.sku, cat_index.position AS cat_index_position, 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.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.price_type, e.weight_type, e.price_view, e.url_key, e.msrp, e.msrp_display_actual_price_type, e.links_purchased_separately, e.links_exist, e.giftcard_amounts, e.allow_open_amount, e.open_amount_min, e.open_amount_max, e.tax_class_id, e.swatch_image, e.size, e.size_value, e.main_colour, e.main_colour_value, e.specific_colours, e.style, e.preorder_date, e.preorder, 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, product_entity.row_id FROM mage_catalog_product_flat_1 AS e INNER JOIN mage_catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='6' INNER JOIN mage_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 mage_catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0 INNER JOIN mage_catalog_product_entity AS product_entity ON product_entity.entity_id = e.entity_id AND (product_entity.created_in <= '1549456415' AND product_entity.updated_in > '1549456415') INNER JOIN mage_search_tmp_5ce4e2b2df97d3_03136010 AS search_result ON e.entity_id = search_result.entity_id WHERE (stock_status_index.stock_status = 1) ORDER BY e.entity_id DESC, price_index.min_price desc, price_index.min_price desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.