We try to add the invoice status column to the sales order grid.

We added the code beneath to the prepareCollection function:

$collection->getSelect()->join('sales_flat_invoice', 'main_table.entity_id = sales_flat_invoice.parent_id',array('state'));

And the code beneath to the prepareColumns function:

$this->addColumn('state', array(
    'header'    => Mage::helper('sales')->__('Invoice Status'),
    'index'     => 'state',
    'type'      => 'options',
    'options'   => Mage::getResourceModel('sales/order_invoice_collection')->addAttributeToSelect('state'),

We got the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_flat_invoice.parent_id' in 'on clause'

What is going wrong?

4 Answers 4


Firstly - be careful with joining live tables on your sales_order_grid in the admin view. The reason is simple - any action or long-running query that your admins might be executing (for instance, sorting by a custom unindexed column with no other filters and 300k+ orders) may cause locks for your join table and prevent production orders.

Your column defines this as type of options but the option list you're providing is the entire invoice collection!!!

Out of the box, invoice state is only 1 of 3 potential values:

 * Invoice states
const STATE_OPEN       = 1;
const STATE_PAID       = 2;
const STATE_CANCELED   = 3;

So you can handle it one of two ways - explicitly state the options:

'type'      => 'options',
'options'   => array(
    '1' => Mage::helper('core')->__('Open'),
    '2' => Mage::helper('core')->__('Paid'),
    '3' => Mage::helper('core')->__('Canceled'),

Or, use something handy that Magento provides -- a static method for all invoice states in case they differ from version to version:

'type'      => 'options',
'options'   => Mage_Sales_Model_Order_Invoice::getStates()

I prefer the latter as I am not a fan of typing out array options manually :)



Replace sales_flat_invoice.parent_id with sales_flat_invoice.order_id. Not sure if it will give you the desired result, but you won't get the error anymore. The reference to the order table is kept in order_id. parent_id does not exist.

  • The error is gone. Unfortunately the column values are still empty.
    – Michael
    Sep 3, 2013 at 11:31

If you want to add the invoice id in the sales order grid then you can use the following code in your prepareCollection() function as

$collection->getSelect()->joinLeft('sales_flat_invoice', 'main_table.entity_id = sales_flat_invoice.order_id', 'increment_id as invoice_id');

By using the following code you will able to get the invoice id from the current order id in sales order grid. after this add column field as

            'header'=> $this->__('Invoice Id'),
            'align' =>'right',
            'type=' => 'text',
            'index' => 'invoice_id',

For more follow me on http://www.webtechnologycodes.com

  • its working but my filter is not working ? Aug 22, 2017 at 6:43
$collection->getSelect()->joinLeft(array('soa'=> 'sales_flat_invoice'), 'soa.entity_id = main_table.entity_id', array('soa.state'));

$this->addColumn('state', array(
        'header'    => Mage::helper('sales')->__('invoice Status'),
        'index'     => 'state',
        'type'      => 'options',
        'filter_index' => 'soa.state',
        'options'   => array(
        '1' || '3' => Mage::helper('core')->__('0'),
        '2' => Mage::helper('core')->__('1')),
        //'3' => Mage::helper('core')->__('cancelled')),

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.