This week I was working to a customer that needs to add a category order by views and stock.

I modified the layout for use my own custom Toolbar block and rewrite the setCollection()  function;


    public function setCollection($collection)
        if ($this->getCurrentOrder() == 'position')
            $collection->getSelect()->joinInner('report_event AS _table_views', ' _table_views.object_id = e.entity_id', 'COUNT(_table_views.event_id) AS views')->group('e.entity_id');
            $collection->joinField('inventory_in_stock', 'cataloginventory_stock_item', 'is_in_stock', 'product_id=entity_id','is_in_stock>=0', 'left');
            $collection->getSelect()->order(new Zend_Db_Expr("inventory_in_stock {$this->getCurrentDirection()}, views {$this->getCurrentDirection()}"));
            $collection->calculateSizeWithoutGroupClause = true;
        $this->_collection = $collection;        

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

Take care of line

 $collection->calculateSizeWithoutGroupClause = true;

below I will explain its utillity.


When going to category navigation all seems right but noticed the pagination is not working.

Investigating a bit in Magento forums found and interesting answer, the function for count the items in a collection

getSelectCountSql() has issues when we use a GROUP BY in the query for retrieve catalog collection.


I found a code that rewrites this function but I see it was slighly different than Magento core file so decided to create my own respecting the core function.

For make active this rewrite I create a file with name Collection.php into my custom module folder into this path Visualwebs_Toolkit_Model_Resource_Eav_Mysql4_Product_Collection and included this code :


class Visualwebs_Toolkit_Model_Resource_Eav_Mysql4_Product_Collection extends Mage_Catalog_Model_Resource_Eav_Mysql4_Product_Collection

    public $calculateSizeWithoutGroupClause = false;

    public function getSelectCountSql($select = null, $resetLeftJoins = true)
        // My own solution respect original code
        $countSelect = (is_null($select)) ?
            $this->_getClearSelect() :
        $countSelect->columns('COUNT(DISTINCT e.entity_id)');
        if ($resetLeftJoins) {
        // Here the trick, reset group
        if ($this->calculateSizeWithoutGroupClause)
        return $countSelect;


Notice the property calculateSizeWithoutGroupClause set to false as default so the system function work like the original except when we need to use it , like in the function setCollection() explained in the start of this article.

The next step is to activate this on module config.xml

          <!-- our custom rewrite -->
           <!-- end of our custom rewrite -->

For adapt this changes to your module replace all coincidences to Visualwebs_Toolkit   with your Namespace_Modulename