Grid column filter not working with renderer in magento custom module

Question

I'm working with custom module for the magento backend, in this filter not working when using filter calback! Thanks!

I have tried some codes like this,

Grid.php

protected function _prepareCollection() 
    {        
     $collection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToSelect('sku')
            ->addAttributeToSelect('name');

     $this->setCollection($collection);

     return parent::_prepareCollection();        
    }

protected function _prepareColumns() {
    $this->addColumn('entity_id', array(
        'header' => Mage::helper('catalog')->__('ID'),
        'width' => '50px',
        'type' => 'number',
        'index' => 'entity_id',
    ));

    $this->addColumn('name', array(
        'header' => Mage::helper('catalog')->__('Name'),
        'index' => 'name',
    ));

    $this->addColumn('sku', array(
        'header' => Mage::helper('catalog')->__('SKU'),
        'width' => '120px',
        'index' => 'sku',
    ));

    $this->addColumn('packet_associate', array(
        'header'    => Mage::helper('catalog')->__('Packets Associated'),
        'width'     => '80px',
        //'index'     => 'packet_associate',
        'filter_index' => 'packet_associate',
        'renderer'  => 'stockmanagement/adminhtml_productassociate_renderer_associatestatus',
        'type'      => 'options',
        'options'   => array('1' => 'Yes', '0' => 'No'),
        'filter_condition_callback'
                    => array($this, '_filterPacketAssociateCondition'),
    ));

    $this->addColumn('action', array(
        'header' => Mage::helper('catalog')->__('Action'),
        'width' => '200px',
        'type' => 'action',
        'getter' => 'getId',
        'actions' => array(
            array(
                'caption' => Mage::helper('catalog')->__('Associate / Edit Packets'),
                'url' => array(
                    'base' => '*/*/new',
                    'params' => array('store' => $this->getRequest()->getParam('store'))
                ),
                'field' => 'id'
            )
        ),
        'filter' => false,
        'sortable' => false,
        'index' => 'stores',
    ));

    return parent::_prepareColumns();
}

callback function

protected function _filterPacketAssociateCondition($collection, $column) {
    if (!$value = $column->getFilter()->getValue()) {
        return $this;
    }

    $collection->getSelect()->joinLeft('custom_table AS b', 'e.entity_id = b.product_id', 
            array('packet_associate' => 'COUNT(b.id)')); 
    $collection->getSelect()->group(entity_id);

    $collection->getSelect()->having(
            "packet_associate = ?"
            , $value);

    return $collection;
    //var_dump($collection->getSelect()->__toString());die();

}

Associatestatus.php

class   Module_Block_Adminhtml_Productassociate_Renderer_Associatestatus     extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{    
public function render(Varien_Object $row)
{        
    $collection = Mage::getModel("stockmanagement/productassociate")->load($row->getId(), 'product_id')->getData();
    $resultCount = count($collection);
    if($resultCount > 0){
        echo 'Yes';
    }else{
        echo 'No';
    }
}

}

In this, Renderer working fine., only filter callback function is not working!!!

When i print the query in callback function like below, it gives correct result.

SELECT `e`.*, COUNT(b.id) AS `packet_associate` FROM `catalog_product_entity` AS `e` LEFT JOIN `custom_table` AS `b` ON e.entity_id = b.product_id GROUP BY `entity_id` HAVING (packet_associate = '1')

But after applying filter it gives an error. Please suggest me anything.Thanks!

Answer 1

Comments are limited, so I put this in Answer box.

Try to set

protected function _filterPacketAssociateCondition($collection, $column) {
if (!$value = $column->getFilter()->getValue()) {
    return $this;
}

$this->getCollection()->getSelect()->joinLeft('custom_table AS b', 'e.entity_id = b.product_id', 
        array('packet_associate' => 'COUNT(b.id)')); 
$this->getCollection()->getSelect()->group(entity_id);

$this->getCollection()->getSelect()->having(
        "packet_associate = ?"
        , $value);

    return $this;
}

Instead of :

protected function _filterPacketAssociateCondition($collection, $column) {
if (!$value = $column->getFilter()->getValue()) {
    return $this;
}

$collection->getSelect()->joinLeft('custom_table AS b', 'e.entity_id = b.product_id', 
        array('packet_associate' => 'COUNT(b.id)')); 
$collection->getSelect()->group(entity_id);

$collection->getSelect()->having(
        "packet_associate = ?"
        , $value);

    return $collection;
}

Answer 2

If I understand correctly, you want to filter products that have/haven't corresponded records in the custom_table. In that case it's not necessary to group your results - it's a bit heavy operation, let's try to avoid it. Your callback function should look like this one:

protected function _filterPacketAssociateCondition($collection, $column)
{
    $value = $column->getFilter()->getValue();

    $collection->getSelect()->joinLeft(array('b' => 'custom_table'), 'e.entity_id = b.product_id',
        array('product_id'));

    if ($value > 0) {
        $collection->getSelect()->where("b.product_id IS NOT NULL");
    } else {
        $collection->getSelect()->where("b.product_id IS NULL");
    }

    return $this;
}

As you can see, on the left join you will have an integer value in case the joined product_id column if current product has at least one corresponded row in the custom_table, otherwise you will have NULL in this column.