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;        
        $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()) {
            $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 1.7.0.2 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 :

<?php

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
        $this->_renderFilters();
        $countSelect = (is_null($select)) ?
            $this->_getClearSelect() :
            $this->_buildClearSelect($select);
        $countSelect->columns('COUNT(DISTINCT e.entity_id)');
        if ($resetLeftJoins) {
            $countSelect->resetJoinLeft();
        }
        
        // Here the trick, reset group
        if ($this->calculateSizeWithoutGroupClause)
            $countSelect->reset(Zend_Db_Select::GROUP);
        
        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

<global>
        <models>
          
          <!-- our custom rewrite -->
          <catalog_resource_eav_mysql4>
                <rewrite>
                    <product_collection>Visualwebs_Toolkit_Model_Resource_Eav_Mysql4_Product_Collection</product_collection>
                </rewrite>
           </catalog_resource_eav_mysql4
           <!-- end of our custom rewrite -->
...

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