Custom sorting products in category

Question

By default Magento has three sort options - by position, by name and by price. I want to implement sorting by sold quantity.

This is what I've came up so far (I'm overriding core files the Magento way via config.xml)

This is Myname/Catalog/Block/Product/List/Toolbar.php:

class Myname_Catalog_Block_Product_List_Toolbar extends Mage_Catalog_Block_Product_List_Toolbar
{
    public function setCollection($collection)
    {
        parent::setCollection($collection);
        if ($this->getCurrentOrder()) {
            if($this->getCurrentOrder() == 'qty_ordered') {
                $this->getCollection()->getSelect()
                     ->joinLeft('sales_flat_order_item AS sfoi', 'e.entity_id = sfoi.product_id', 'SUM(sfoi.qty_ordered) AS qty_ordered')
                     ->group('e.entity_id')->order('qty_ordered ' . $this->getCurrentDirection());

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

And this is Myname/Catalog/Model/Config.php:

class Myname_Catalog_Model_Config extends Mage_Catalog_Model_Config
{
    public function getAttributeUsedForSortByArray()
    {
        return array_merge(
            parent::getAttributeUsedForSortByArray(),
            array('qty_ordered' => Mage::helper('catalog')->__('Sold quantity'))
        );
    }
}

This WILL make the sorting actually working, but the pager is then not working. I always get the wrong item count as seen below and thus I have no pagination as there is 1 item according to pager, even though it's showing 10 (as the default limit is).

What am I doing wrong?

Answer 1

It's because you have a GROUP BY statement in the query. When Magento wants to calculate items count it sees only 1 row.

UPDATE: so the SQL is right. But later Magento gets select, adds the group by (and we already have one!) and tries to get count(*). So either use subselect or override the method getSelectCountSql