Grid pagination not working when use group clause in collection

Collections and Lazy Loading in Magento

The reason pagination does not work is because of how collections are counted and how lazy loading works with collections.

Collections in Magento implement the class Countable. Due to lazy loading of collections in Magento, whenever the method count() is called, the data has to be loaded. As a workaround of this, collections implement a method called getSize(). It will clone your SQL statement, wrap it in a COUNT() and return the result. This allowed a collection to get a total count without loading all the data. This allows for things such as filters to be added at the last minute.

This is what Varien_Data_Collection_Db::getSize() and it's partner getSelectCountSql() looks like:

/**
     * Get collection size
     *
     * @return int
     */
    public function getSize()
    {
        if (is_null($this->_totalRecords)) {
            $sql = $this->getSelectCountSql();
            $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
        }
        return intval($this->_totalRecords);
    }

    /**
     * Get SQL for get record count
     *
     * @return Varien_Db_Select
     */
    public function getSelectCountSql()
    {
        $this->_renderFilters();

        $countSelect = clone $this->getSelect();
        $countSelect->reset(Zend_Db_Select::ORDER);
        $countSelect->reset(Zend_Db_Select::LIMIT_COUNT);
        $countSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
        $countSelect->reset(Zend_Db_Select::COLUMNS);

        $countSelect->columns('COUNT(*)');

        return $countSelect;
    }

Basically, it drops limits, columns, ordering, etc and leaves the filters behind. Then it adds a MySQL COUNT() to the columns.

The Problem

Normally, on one table, this would return one row with the total count. This is why getSize() does a fetchOne() against the query. However, when doing things such as table joins, group bys, and the like, you will not return one row, you will return multiple. It is because of this that you need to alter the getSize() method in your collection.

The Solution

This is what your method should look like now:

public function getSize() {

        if ( is_null( $this->_totalRecords ) ) {
            $sql = $this->getSelectCountSql();
            // fetch all rows since it's a joined table and run a count against it.
            $this->_totalRecords = count( $this->getConnection()->fetchall( $sql, $this->_bindParams ) );
        }

        return intval( $this->_totalRecords );
    }

Instead of a fetchOne(), we ran a fetchAll() wrapped in a count() PHP function. Now your totals will return appropriately.

Great solution. Maybe someone has the same problem as we had, so I will post another possible solution. In our case we had a collection, which sometimes included a group by statement and sometimes it didn't, depending on the grid where the collection was loaded. Using the solution above, we found two problems:

1. If the collection is empty, the size is valued as 1, although it should be zero.
2. In the cases where the getSize method was called without a group by statement on the collection, the size is valued as 1 no matter how many items are in the collection.

After debugging a while we found out, that in case 1 the part

$this->getConnection()->fetchall( $sql, $this->_bindParams ) 

returns an array which has one entry with value 0. That is why the count function returns 1 although no entries were found.

In case 2 the same part returns an array with one entry, whose value is the real size of the collection. The count function again returns 1 and not the value.

Searching for an alternative, we found out, that the product collection uses a rewrite of the function getSelectCountSql(). We adapted this and changed it a bit, which ended in this solution:

public function getSelectCountSql()
{
    $countSelect = parent::getSelectCountSql();
    $countSelect->reset(Zend_Db_Select::COLUMNS);
    $countSelect->reset(Zend_Db_Select::GROUP);
    $countSelect->columns('COUNT(DISTINCT item_id)');

    return $countSelect;
}

It solves the two issues I already mentioned and as far as I can see, it also works for the other cases.