3

How to get Category product collection with Product position 0 at end of list in collection.

Get Product Collection with position greater than 0 comes first and at the end of collection All 0 position product are arrive.

like, 3 Product in category collection with position is 0,1,2. I want to get collection like position 1,2,0 all 0 position index are end of list in collection.

code for php,

class Featured extends \Magento\Catalog\Block\Product\ListProduct
{
    protected $categoryFactory;

    public function __construct(
        \Magento\Catalog\Block\Product\Context $context,   
        \Magento\Catalog\Model\CategoryFactory $categoryFactory,  
        array $data = []
    ) {
        $this->categoryFactory = $categoryFactory;
        parent::__construct(
            $context,            
            $data
        );
    }

    public function getCategoryProductsAscZeroLast($categoryId) 
        {
            $products = $this->categoryFactory->create()->load($id)->getProductCollection()->addAttributeToSelect('*');
            //$products->getSelect()->setOrder('cat_index_position', 'DESC');
            return $products;
        }
}

1 Answer 1

6

Found Solution for above query, using order by case statement to working same as above query

public function getCategoryProductsAscZeroLast($categoryId) 
    {
        $products = $this->getCategoryData($categoryId)->getProductCollection();
        $products->addAttributeToSelect('*')->setOrder('cat_index_position','ASC');;        
        $products->getSelect()->order(new \Zend_Db_Expr("CASE WHEN `cat_index_position` = '0' THEN 9999 ELSE `cat_index_position` END"));        
        return $products;
    }
3
  • How It will Work in Magento 2 Jun 5, 2019 at 11:20
  • thanks, it worked for me (y) Jun 13, 2020 at 6:35
  • it's throwing error Error filtering template: Unrecognized method 'getSelect()' in grid.phtml page. Sep 19, 2020 at 10:34

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.