Use having clause in Magento grid

Question

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

Answer 1

First of all $countSelect->reset(Zend_Db_Select::HAVING); means it will reset HAVING 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 in lib/Varien/Data/Collection/Db.php file.

I tried the above solution mentioned by @bharat but that didnt work.

Now i did the below.

public function getSize()
{
    if (is_null($this->_totalRecords)) {
        //$sql = $this->getSelectCountSql();
        $sql = $this->getSelect();
        $this->_totalRecords = count($this->getConnection()->fetchAll($sql, $this->_bindParams));
    }
    return intval($this->_totalRecords);
}

or if you want to keep the original code for performance for any other cases

public function getSize()
{
    if (is_null($this->_totalRecords)) {

        if($this->getSelect()->getPart(Zend_Db_Select::GROUP) OR $this->getSelect()->getPart(Zend_Db_Select::HAVING))
        {
            $sql = $this->getSelect();
            $this->_totalRecords = count($this->getConnection()->fetchAll($sql, $this->_bindParams));
        }
        else
        {
            $sql = $this->getSelectCountSql();
            $this->_totalRecords = $this->getConnection()->fetchOne($sql, $this->_bindParams);
        }
    }
    return intval($this->_totalRecords);
}

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.

Answer 2

I could able to fix it on my own, it would be help full for others

In Grid.php, i have added following line to get calculated days

 
protected function _prepareCollection() 
{  
  $collection->getSelect()->columns(array('days' => new 
  Zend_Db_Expr('datediff(main_table.support_end_date,now())'))); 

 }

protected function _prepareColumns() 
{
 $this->addColumn('days', array(
             'header' => Mage::helper('tp_adminpurchase')->__('Expire Days '),
             'align' => 'left',
             'index' => 'days',
             'filter_index' => 'days',
             'filter_condition_callback' => array($this, '_filterDays'),
         ));

 }



protected function _filterDays($collection, $column)
{
  $condition = $column->getFilter()->getValue();
  $this->getCollection()->getSelect()->having('days = ?', $condition);
         return $this;

}

To fix ,the count issue in grid i have this function in my Collection.php class

  public function getSize() {
        if (is_null($this->_totalRecords)) {

            $sql = $this->getSelectCountSql();
            $this->_totalRecords = count($this->getConnection()->fetchAll($sql, $this->_bindParams));
        }
        return intval($this->_totalRecords);
    }

Now i have working grid which shows the calculated days which can also be filtered