Magento 2.X: Sort Product Collection by Category Product Position

Question

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

Answer 1

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

Answer 2

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