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. – Raphael at Digital Pianism Jan 13 '16 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 '16 at 9:22
  • Have you tried using COUNT(suiviprepa_suiviorders.reason) instead ? – Raphael at Digital Pianism Jan 13 '16 at 9:32
  • It doesn't work, suiviprepa_suiviorders not being a table name. Thank you for your time @DigitalPianism – feub Jan 13 '16 at 10:01
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, privacy policy and cookie policy

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