1

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?

4
  • Seems like your "sorders" alias cannot be used. Try printing your SQL Query using $collection->printlogquery(true,true); before and after adding your having clause to see what's going on here. Jan 13, 2016 at 9:05
  • @DigitalPianism The query is ok, it runs as I want (trying in MySQL Workbench): SELECT main_table.*, COUNT(sorders.reason) AS num_issues FROM sales_flat_order AS main_table LEFT JOIN suiviprepa_suiviorders AS sorders ON main_table.increment_id = sorders.order_increment_id WHERE (main_table.status = 'complete') GROUP BY main_table.increment_id HAVING (COUNT(sorders.reason) = 1) But not in Magento
    – feub
    Jan 13, 2016 at 9:22
  • Have you tried using COUNT(suiviprepa_suiviorders.reason) instead ? Jan 13, 2016 at 9:32
  • It doesn't work, suiviprepa_suiviorders not being a table name. Thank you for your time @DigitalPianism
    – feub
    Jan 13, 2016 at 10:01

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.