Iam trying build a grid in Magento , where i need to calculate days between two dates so that i can filter days.But i could not get it right with ,
Following is the piece of collection code in grid .
preparecollection
$collection->getSelect()->columns(array('days' => new Zend_Db_Expr ('datediff(main_table.support_end_date,now())')));
$collection->getSelect()->having('days <= ?', '60');
Prepare columns
$this->addColumn('days', array(
'header' => Mage::helper('tp_adminpurchase')->__('Days '),
'align' => 'left',
'index' => 'days',
'filter_index' => 'days',
'filter_condition_callback' => array($this, '_filterDays'),
));
protected function _filterDays($collection, $column) {
$condition = $column->getFilter()->getCondition();
$collection->getSelect()->having('days <= ?', '60');
return $countSelect;
}
In collection.php , i have this
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->columns('datediff(main_table.support_end_date,current_date) AS days');
$countSelect->columns('COUNT(*)');
return $countSelect;
}
My issues are
1) Filter on days is not pulling proper records
2) Count on the grid is showing incorrect
I have refferred below issues but no luck
How to add dynamic field in magento collection?
Issue using "having" in Magento collection
Could any one help in sorting this out, where iam going wrong
Best Answer
First of all
$countSelect->reset(Zend_Db_Select::HAVING);
means it will resetHAVING
from your collection. That means it will remove the having clause. And Its not what you want. You may want to add it to the collection (app/code/core/Mage/Catalog/Model/Resource/Product/Collection.php->_getSelectCountSql()
here.)But the main culprit it the
getSize()
method which exists inlib/Varien/Data/Collection/Db.php
file.Now i did the below.
or if you want to keep the original code for performance for any other cases
Check i am not even using the
getSelectCountSql()
. I am just reading the whole SQL QUERY and fetching all data and return the count of it. That's all.