Filter a count column in a custom admin grid

Question

I have an admin grid with a count column, everything works fine except the filtering.

This is my _prepareCollection() method :

protected function _prepareCollection()
{
    $orders              = Mage::getModel('Sales/Order')
                                ->getCollection()
                                ->addFieldToFilter('main_table.status', array('eq' => 'complete'));

    $orders->getSelect()
        ->joinLeft(
            array(
                'sorders' => 'suiviprepa_suiviorders'),
                'main_table.increment_id = sorders.order_increment_id ',
                array(
                    'num_issues' => 'COUNT(sorders.reason)'
                )
            )
        ->group('main_table.increment_id');

    $this->setCollection($orders);

    return parent::_prepareCollection();
}

In _prepareColumns() I added a filter callback:

$this->addColumn('prepa_order_issues',
    array(
        'header'                        => 'Problems',
        'index'                         => 'num_issues',
        'type'                          => 'number',
        'renderer'                      => 'Name_Mymodule_Block_Adminhtml_Orders_Renderer_Numissues',
        'filter_condition_callback'     => array($this, '_prepaIssuesFilter')
    )
);

Like this:

protected function _prepaIssuesFilter($collection, $column)
{
    (int) $filterValue = $column->getFilter()->getValue();

    if (!is_null($filterValue)) {
        $collection->getSelect()->having('COUNT(sorders.reason) = ?', $filterValue);
    }
}

The final request is working (if I run it in MySQL is does the job), but Magento returns an error, I guess running another simple request for pagination purpose :

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sorders.reason' in 'having clause', query was: SELECT COUNT(*) FROM `sales_flat_order` AS `main_table` WHERE (main_table.status = 'complete') HAVING (COUNT(sorders.reason) = 1)

Does anyone has any idea how to make such a filtering on count column work?

Answer 1

This is what I do to filter a count column in a custom grid:

// in the collection class
public function addProductCount()
{
    $innerSelect = $this->getConnection()->select()
        ->from(
            ['inner_items' => $this->getTable('custom/product')],
            ['prerequisite_id', 'cnt' => new Zend_Db_Expr('COUNT(inner_items.product_id)')]
        )
        ->group('inner_items.prerequisite_id');

    $this->getSelect()->joinLeft(
        ['items' => $innerSelect],
        'main_table.prerequisite_id = items.prerequisite_id',
        ['product_count' => new Zend_Db_Expr('COALESCE(items.cnt,0)')]
    );

    $this->addFilterToMap('product_count', 'COALESCE(items.cnt,0)'); // enable filtering in grid

    return $this;
}

The table 'custom/product' has 2 columns: prerequisite_id and product_id.