1

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']);
}

2 Answers 2

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.

4
  • Thanks, I will try, in the meanwhile can you answer my following question ? magento.stackexchange.com/questions/126308/…
    – Vicky Dev
    Jul 19, 2016 at 7:32
  • please apply this solution and let me know if you still face any issue,I will check you another issue whenever i get time,Thanks :)
    – Nitin Vala
    Jul 20, 2016 at 13:04
  • Great use of the filter_index option, this is not a widely documented feature.
    – Niels
    Apr 19, 2017 at 9:26
  • Hi @NitinVala, How make the same with this code plese $collection->join(array('soa' => 'sales/order_address'), 'soa.parent_id=main_table.entity_id and soa.address_type = "billing"', array('full_address' => 'CONCAT(soa.street, ",", soa.city, ",", soa.region, ",", soa.postcode, ",", soa.country_id)'), null, 'left');
    – MagEGY
    Nov 1, 2017 at 19:58
0

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']);
}
1
  • can you please provide this solution for magento2? Nov 15, 2019 at 10:52

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.