3

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
|improve this question
  • you have tried this $collection->addAttributeToSort('price', 'ASC'); instead of ->setOrder('price', 'ASC'); – Rakesh Donga Mar 11 '19 at 16:54
  • Yes, the result is the same. – quickshiftin Mar 11 '19 at 16:56
  • 1
    I would suggest to dump the SQL for your 3 variants and look at what the difference is - it seems strange, indeed, since the join of the price index table and the join of an custom attribute shouldn't interfere – HelgeB Mar 11 '19 at 16:58
  • I've added the SQL for each query in my original question. – quickshiftin Mar 11 '19 at 17:33
  • 1
    The SQL statements are OK in my opinion. So, yes, it looks like all the products with my_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
0

Turns out the products were not enabled, and moreover the indexer only considers products which are enabled.

After crafting a query to bulk enable the products and rerunning the indexer, the queries work as expected.

|improve this answer

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.