I am trying to sort by price on a product collection query, however, it seems sorting by price is reducing the result set to 0...
Here is a working query which returns expected number of products (865)
$productCollection = $this->productCollectionFactory->create()
->addAttributeToSelect('*')
->addFieldToFilter('my_product_type', ['eq' => 383]);
die(var_dump($productCollection->count()));
By merely adding sorting, the result set is reduced to 0...
$productCollection = $this->productCollectionFactory->create()
->addAttributeToSelect('*')
->addFieldToFilter('my_product_type', ['eq' => 383])
->setOrder('price', 'ASC');
die(var_dump($productCollection->count()));
Reindexing does not solve the problem. It looks like some JOINs introduced by the setOrder
clause are responsible for reducing the result set to 0 items.
I found this bug report, with this patch, which I've verified is on my system.
I have also tried using addAttributeToSort
instead of setOrder
; the result is the same.
It seems tied to filtering the product set by my custom attribute, for when I try this
$productCollection = $this->productCollectionFactory->create()
->addAttributeToSelect('*')
->setPageSize(10)
->setCurPage(1)
->setOrder('price', 'ASC');
It works as expected.
Is it something I'm doing wrong, or a bug, or perhaps something is off with my product data?
EDIT
I've added the SQL from the 3 queries above. It looks like the JOIN on catalog_product_entity_int
+ catalog_product_index_price
is what may be breaking things?
First Call:
SELECT `e`.*, IF(at_my_product_type.value_id > 0, at_my_product_type.value, at_my_product_type_default.value) AS `my_product_type` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `at_my_product_type_default` ON (`at_my_product_type_default`.`row_id` = `e`.`row_id`) AND (`at_my_product_type_default`.`attribute_id` = '593') AND `at_my_product_type_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_my_product_type` ON (`at_my_product_type`.`row_id` = `e`.`row_id`) AND (`at_my_product_type`.`attribute_id` = '593') AND (`at_my_product_type`.`store_id` = 1) WHERE ((IF(at_my_product_type.value_id > 0, at_my_product_type.value, at_my_product_type_default.value) = '383')) AND (e.created_in <= '1547298780') AND (e.updated_in > '1547298780')
Second Call:
SELECT `e`.*, IF(at_my_product_type.value_id > 0, at_my_product_type.value, at_my_product_type_default.value) AS `my_product_type`, `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_product_entity_int` AS `at_my_product_type_default` ON (`at_my_product_type_default`.`row_id` = `e`.`row_id`) AND (`at_my_product_type_default`.`attribute_id` = '593') AND `at_my_product_type_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_my_product_type` ON (`at_my_product_type`.`row_id` = `e`.`row_id`) AND (`at_my_product_type`.`attribute_id` = '593') AND (`at_my_product_type`.`store_id` = 1)
INNER JOIN `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 WHERE ((IF(at_my_product_type.value_id > 0, at_my_product_type.value, at_my_product_type_default.value) = '383')) AND (e.created_in <= '1547298780') AND (e.updated_in > '1547298780') ORDER BY `price_index`.`min_price` ASC
Third Call:
SELECT `e`.*, `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_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 WHERE (e.created_in <= '1547298780') AND (e.updated_in > '1547298780') ORDER BY `price_index`.`min_price` DESC
$collection->addAttributeToSort('price', 'ASC');
instead of->setOrder('price', 'ASC');
– Rakesh Donga Mar 11 '19 at 16:54my_product_type
set don't have an entry in the price index table. There is something going wrong with your price indexing if you already have tried to reindex – HelgeB Mar 11 '19 at 17:50