Magento Admin Grid Field Custom filter issue

Question

I have created Magento Admin module with Grid and field having custom filter.

    $this->addColumn('diff', array(
        'header'    =>'Diff.',
        'align'     =>'left',
        'type' => 'number',
        'index'     =>'diff',
        'filter_condition_callback' => array($this, '_diffFilter'),
    ));

Collection having group by as below:

$collection->getSelect()->group(array('main_table.order_id'));

Custom Filter function as below:

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

    if($_filter_data["from"]!=''){
        $collection->getSelect()->having('ROUND((main_table.base_cost-main_table.base_price)*100/main_table.base_cost) >= ?', $_filter_data["from"]);           
    }

    if($_filter_data["to"]!=''){
        $collection->getSelect()->having('ROUND((main_table.base_cost-main_table.base_price)*100/main_table.base_cost) <= ?', $_filter_data["to"]);
    }

    return $this;
}

Using this function if i load admin grid it's throwing below error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.base_cost' in 'having clause'

But i grab select query $collection->getSelect() using this and then run to MySQL directly then, it is working fine, but it only throwing error from Magento.

I did lots of research but it's not working at all with Magento.

Todd M Todd M · Accepted Answer · 2017-04-08T17:35:15

HAVING clauses are used to filter the results of a grouped query. If you want to filter for columns in the table, use a WHERE clause:

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

    if($_filter_data["from"]!=''){
        $collection->getSelect()->where('ROUND((main_table.base_cost-main_table.base_price)*100/main_table.base_cost) >= ?', $_filter_data["from"]);           
    }

    if($_filter_data["to"]!=''){
        $collection->getSelect()->where('ROUND((main_table.base_cost-main_table.base_price)*100/main_table.base_cost) <= ?', $_filter_data["to"]);
    }

    return $this;
}

Magento Admin Grid Field Custom filter issue

1 Answers