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)
        if ($this->getCurrentOrder()) {
            if($this->getCurrentOrder() == 'qty_ordered') {
                     ->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 {
                     ->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(
            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).

wrong item count

What am I doing wrong?

1 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

  • I have tested the raw query in SQL, it's not it. My own query returns 28 results/products (as it should) with joined qty_ordered but pager says "1 item" like on the screenshot. When I omitting the GROUP BY from the query THEN I got 1 row which isn't what I need.
    – MagentoFan
    Commented Jul 18, 2014 at 6:04
  • Sorry foe being unclear. The SQL is right, but the whole approach is wrong, as magento tries to add GROUP BY again when calculate COUNT later. Try to 1) define custom function getCountSql() in the collection or 2) use subselect to avoid incorrect using group by with count
    – Amasty
    Commented Jul 21, 2014 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.