Magento 2 – Get Custom Collection by Asc Sort Order with Position 0 at End

category-productsmagento2

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

Best Answer

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;
    }
Related Topic