I added a prodct list widget that should show products from one category, sort by position.

Sort products in widgets seems not to work, so I tried a suggested module from this question. It adds a plugin for afterCreateCollection() and (nearly) works.

It adds

ORDER BY `cat_index_position` ASC

but the query still isn't correct.

SELECT DISTINCT  `e`.*, `cat_index`.`position` AS `cat_index_position`, 
`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` FROM 
`catalog_product_entity` AS `e`

 INNER JOIN `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=1146

 INNER JOIN `catalog_product_index_price_store` AS `price_index` ON 
price_index.entity_id = e.entity_id AND price_index.customer_group_id = 0
 AND price_index.website_id = '1' WHERE (price_index.store_id = 1) AND 
(price_index.store_id = 1) AND (price_index.store_id = 1) AND 
(((IFNULL(`e`.`entity_id`, 0) IN (SELECT 
`catalog_category_product`.`product_id` FROM `catalog_category_product` 
WHERE (category_id IN ('1397')))) )) ORDER BY `cat_index_position` ASC

This part is correct, 1397 is my select category.

WHERE (category_id IN ('1397')))) )) ORDER BY `cat_index_position` ASC

This one is wrong. 1146 is the root category. It is not set as "anchor", contains no product, but has entries in catalog_category_product_index_store1. The position is set for some reason, with different values as for category 1397.

AND cat_index.category_id=1146

Any ideas how to fix this?

Magento 2.3.6

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.