3

I would like to sort the list of my Product Collection by its category product position. Right now I have this function

public function getProducts($category, $productFactory, $store)
{    
    $collection = $productFactory->create();
    $collection->addAttributeToSelect('*');    
    $anchorFlag = $category->getIsAnchor(); // Remove this if we want to include the child products within the parent category    
    $collection->setStore($store);
    $collection->addUrlRewrite($category->getId());
    $collection->addAttributeToFilter('visibility', \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH);
    $collection->addAttributeToFilter('status',\Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);         
    $collection->setOrder('position','ASC');

    return $collection;
}

As you can see right now. The list of products are Ordered by its position in Catalog->Product (CMS) so meaning it's ordered by its ID? or something right?. What I want is to retrieve the product by its position in a Catalog->Categories->Products. The idea is I have 5 products and 3 of this are assigned under a certain category. So what I want to do is order them based on their position in the Categories->Product list and NOT under PRODUCT Position.

What I was thinking to solve this is to Join the Product Collection with the Category Collection using the category ID and with that, I can sort it by product position. If I were to translate it into a SQL Query I want something like this

SELECT products.* 
FROM products 
INNER JOIN category ON (products.category_id = category.id) 
WHERE category.id = {category_id} 
ORDER BY category.products.position;```

2 Answers 2

4

You can achieve this by changing your getProducts() function to

public function getProducts($category, $productFactory, $store)
{    
    $collection = $productFactory->create();
    $collection->addAttributeToSelect('*');    
    $anchorFlag = $category->getIsAnchor();   
    $collection->setStore($store);
    $collection->addUrlRewrite($category->getId());
    $collection->addAttributeToFilter('visibility', \Magento\Catalog\Model\Product\Visibility::VISIBILITY_BOTH);
    $collection->addAttributeToFilter('status',\Magento\Catalog\Model\Product\Attribute\Source\Status::STATUS_ENABLED);         
    $collection->setOrder('cat_index_position','ASC');
    return $collection;
}

Hope it helps!!!

4
  • Sumit I know what does cat_index_position do? Aug 28, 2019 at 6:37
  • cat_index_position is for the position of the products defined in the category products section
    – Sumit
    Aug 28, 2019 at 6:39
  • Wow this is actually the thing that I need. Will test this out later after an hour. Will definitely accept this answer once I finish trying this out. Great stuff thanks a lot Aug 28, 2019 at 6:40
  • You're welcome. Happy Coding!!!
    – Sumit
    Aug 28, 2019 at 6:41
7

The cat index position only takes effect when the table flat mode is enalbe.

so you can use this code:

$collection->addAttributeToSort('position'); // Default SORT_ORDER_ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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