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
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
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.