Magento Collection GroupBy getSize

Question

I have a collection:

$this->_totalVersions = Mage::getModel('downloads/files')
                                ->getCollection()
                                ->addFieldToFilter('customer_groups', array('like' => '%'.$customergroupId.'%'))
                                ->addFieldToFilter('main_table.is_active', 1)
                                ->getSize()

Works perfectly! but when I add a group it doesn't work.

$this->_totalVersions = Mage::getModel('downloads/files')
                                ->getCollection()
                                ->addFieldToFilter('customer_groups', array('like' => '%'.$customergroupId.'%'))
                                ->addFieldToFilter('main_table.is_active', 1)
                                ->getSelect()->group(array('main_table.name'))
                                ->getSize()

I dont want to use ->count() or count($collection) because it holds 90.000+ items.

Is there a proper way to count the collection?

Thanks in advance,

Martijn

Answer 1

First, thanks Guerra for your reply. You pointed me into the right direction.

I just solved it after a couple of hours.. the trick was to add a filter into the resource collection XXXXX_Downloads_Model_Mysql4_Files_Collection

public function addGroupByNameFilter()
{
    $this->getSelect()->group('main_table.name');
    return $this;
}

Applying this filter:

$this->_totalItems = Mage::getModel('downloads/files')
        ->getCollection()
        ->addFieldToFilter('customer_groups', array('like' => '%'.$customergroupId.'%'))
        ->addFieldToFilter('main_table.is_active', 1)
        ->addGroupByNameFilter()
        ->getSize()

works like a charm! That way I will keep my XXXXXXX_Downloads_Model_Mysql4_Files_Collection object. : )

Cheers,

Martijn

Answer 2

The method "getSize()" is implemented on Varien_Data_Collection, when you call "getSelect()" in your collection it return a "Zend_Db_Select" object and this one doesn't implement getSize method.

So you can use the way that magento implements the getSize on collection but in your Zend_Db_Select., you can't group on collection just in Zend_Db_select.

Magento do like this:

 $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);

// Count doesn't work with group by columns keep the group by 
if(count($this->getSelect()->getPart(Zend_Db_Select::GROUP)) > 0) {
    $countSelect->reset(Zend_Db_Select::GROUP);
    $countSelect->distinct(true);
    $group = $this->getSelect()->getPart(Zend_Db_Select::GROUP);
    $countSelect->columns("COUNT(DISTINCT ".implode(", ", $group).")");
} else {
    $countSelect->columns('COUNT(*)');

Understand?

Magento reset all "heavy" arguments of the query, and simply make a query just with "COUNT()", but if you need to use group just COUNT() will count your groups, so you emulate group result with DISTINCT.