Magento sort bestseller hide unsold products issue

Question

I'm working on magento custom code which add function to sort product list by sell count,

I used this site code for that https://inchoo.net/magento/magento-products/sort-show-products-by-sold-quantity-in-magento/comment-page-1/

The code seems works fine, but i only need to show sold items when user hit that filter

code on /app/code/local/Inchoo/Catalog/Block/Product/List/Toolbar.php

public function setCollection($collection)
    {
        $this->_collection = $collection;

        $this->_collection->setCurPage($this->getCurrentPage());

        // we need to set pagination only if passed value integer and more that 0
        $limit = (int)$this->getLimit();
        if ($limit) {
            $this->_collection->setPageSize($limit);
        }
        if ($this->getCurrentOrder()) {


               if($this->getCurrentOrder() == 'qty_ordered') {
                $this->getCollection()->getSelect()
                     ->joinLeft(
                            array('sfoi' => $collection->getResource()->getTable('sales/order_item')),
                             'e.entity_id = sfoi.product_id',
                             array('qty_ordered' => 'SUM(sfoi.qty_ordered)')
                         )
                     ->group('e.entity_id')
                     ->order('qty_ordered ' . $this->getCurrentDirection());
            }

            else{           
            $this->_collection->setOrder($this->getCurrentOrder(), $this->getCurrentDirection());           
            }
        }
        return $this;
    }

is it possible to filter and sort the products which only have sales, or do i need to change some other function, Thank you

mtr.web mtr.web · Accepted Answer · 2018-11-01T18:34:59

You can use the having() function found in ~/lib/Zend/Db/Select.php to only return items in the collection that have count(*) > 0:

$this->getCollection()->getSelect()
    ->joinLeft(
        array('sfoi' => $collection->getResource()->getTable('sales/order_item')),
         'e.entity_id = sfoi.product_id',
         array('qty_ordered' => 'SUM(sfoi.qty_ordered)')
    )
    ->group('e.entity_id')
    ->having('qty_orderd > 0')
    ->order('qty_ordered ' . $this->getCurrentDirection());

This is untested, and sometimes the table's alias is required, so if it fails, try ->having('sfoi.qty_orderd > 0') or ->having('SUM(sfoi.qty_orderd) > 0') and see if those work.

Magento sort bestseller hide unsold products issue

1 Answers