0

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;```
0

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!!!

|improve this answer|||||
  • Sumit I know what does cat_index_position do? – MadzQuestioning Aug 28 '19 at 6:37
  • cat_index_position is for the position of the products defined in the category products section – Sumit Aug 28 '19 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 – MadzQuestioning Aug 28 '19 at 6:40
  • You're welcome. Happy Coding!!! – Sumit Aug 28 '19 at 6:41
0

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
|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.