Magento computed column value in 'filter_index'

Question

I have Magento CE 1.9.2.1 installed in my site.

Now in the Invoice grid, I have calculated "Grand Total Excl. Tax" in the Grid's collection query and also set that as a column with the below query customization:

$collection->getSelect()->join(array('invoice' => 'sales_flat_invoice'),
    'invoice.entity_id = main_table.entity_id', null)
    ->columns('(invoice.grand_total - invoice.tax_amount) AS grand_excl_tax');

It shows fine, but when I try to filter the grid by that column, then it generates error as following:

Column not found: 1054 Unknown column 'invoice.grand_excl_tax' in 'having clause', query was: SELECT COUNT(*) FROM sales_flat_invoice_grid AS main_table

My column code:

$this->addColumn('grand_excl_tax', array(
    'header' => Mage::helper('customer')->__('Amount Excl. Tax'),
    'index' => 'grand_excl_tax',
    ##'filter_index' => 'invoice.grand_excl_tax',
    'filter_condition_callback' => array($this, '_applyMyFilter'),
    'type' => 'currency',
    'align' => 'right',
    'currency' => 'order_currency_code',
));

_applyMyFilter function code:

protected function _applyMyFilter(Varien_Data_Collection_Db $collection, Mage_Adminhtml_Block_Widget_Grid_Column $column)
{
    $select = $collection->getSelect();
    $field = $column->getIndex();
    $value = $column->getFilter()->getValue();
    $select->having("invoice.grand_excl_tax=?", $value['from']);
}

Answer 1

Please add below changes to your code.

You need to add GROUP BY entity_id.

Add select query on collection like below.

$collection->getSelect()->joinLeft(
    array('s' => new Zend_Db_Expr('(SELECT (o.grand_total - o.tax_amount) grand_excl_tax,'
        . ' FROM ' . Mage::getSingleton('core/resource')->getTableName('sales/invoice') . ' AS o'
        . ' GROUP BY entity_id)')),
    "s.entity_id = main_table.entity_id",
    array('grand_excl_tax')
);

After that add filter_index in addColumn:

$this->addColumn('grand_excl_tax', array(
    'header'    => Mage::helper('customer')->__('Amount Excl. Tax'),
    'index'     => 'grand_excl_tax',
    'filter_index' => 's.grand_excl_tax',
    'type'      => 'currency',
    'align'     => 'right',
    'currency'  => 'order_currency_code',
));

I am tested it and it's working fine form me, After applying this there is not any issue on grid filter.

Let me know still if you face any issue.

Answer 2

use this _applyMyFilter function for filter_condition_callback

protected function _applyMyFilter(Varien_Data_Collection_Db $collection, Mage_Adminhtml_Block_Widget_Grid_Column $column)
{

    $select = $collection->getSelect();
    $field = $column->getIndex();
    $value = $column->getFilter()->getValue();
    $select->where("invoice.grand_total - invoice.tax_amount>?", $value['from']);
    $select->where("invoice.grand_total - invoice.tax_amount<?", $value['to']);
}