Adding Customer Email to adminhtml order grid breaks the ability to search

Question

There are a couple of questions relating to adding a custom email field to the order grid. For example, this and this.

However, both these create a problem when you try and filter. What happens is that whilst you can search the new email field, if you search via order ID, store, or Purchased On - then you are taken to the Dashboard, and when you return to the Order grid, you get an error.

For the sake of example, I am using the following code to make the join in _prepareCollection

$collection->getSelect()->joinLeft(
        array('myorder'=>'sales_flat_order'),
        'myorder.entity_id = main_table.entity_id',
        array('myorder.customer_email')
        );

and the following to call it in _prepareColumns:

$this->addColumn('customer_email', array(
            'header' => $this->helper('sales')->__('Customer Email'),
            'index' => 'customer_email',
            'filter_index' => 'myorder.customer_email',
        ));

Anyone any ideas what is wrong with this code?

Answer 1

I tried the code and the error was: "Integrity constraint violation: 1052 Column 'increment_id' in where clause is ambiguous". It's happening because there is duplicate column. So you should modify the where clause or simply to change the table. Try with the address table, it's working fine:

$collection->getSelect()->joinLeft(
    array('oa' => 'sales_flat_order_address'),
    'oa.entity_id = main_table.entity_id',
    array('oa.email')
)->where("oa.address_type = 'billing'");

and

$this->addColumn('email', array(
    'header'       => $this->helper('sales')->__('Customer Email'),
    'index'        => 'email',
    'filter_index' => 'oa.email',
));