How to add Email address in sales orders admin grid?

Question

Trying to add email address into the grid in sales but once added can't search by order number. Throwing error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'increment_id' in where clause is ambiguous, query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table INNER JOIN customer_entity ON main_table.customer_id = customer_entity.entity_id WHERE (increment_id LIKE '%0013%')

my code:

protected function _prepareCollection()
{
    $customer_entity = Mage::getSingleton('core/resource')->getTableName('customer_entity');
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->getSelect()
           ->join(
           $customer_entity,
           'main_table.customer_id = '.$customer_entity.'.entity_id', array('customer_email' => 'email'));
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

and field definition:

        $this->addColumn('customer_email', array(
        'header'    => Mage::helper('Sales')->__('Email'),
        'width'     => '100px',
        'index'     => 'customer_email',
        'type'        => 'text',

    ));

what to do to make it work ? To display email I can just add a column but then while I'm trying to search by email magento throwing an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'customer_email' in 'where clause', query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table WHERE (customer_email LIKE '%custom%')

Answer 1

First of all, you need to get the customer email by performing a join with sales_flat_order table. Your _prepareCollection() function will be:

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->joinLeft(
           array('ordersTable'=>'sales_flat_order'),
           'ordersTable.entity_id = main_table.entity_id',
           array('ordersTable.customer_email')
         );
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

In _prepareColumns() you'll need to add:

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

Now, to fix the search issue on order number, you'll need to add 'filter_index' on 'order number' column. The issue is happening because there is duplicate increment_id column. Here is the code:

$this->addColumn('real_order_id', array(
            'header'=> Mage::helper('sales')->__('Order #'),
            'width' => '80px',
            'type'  => 'text',
            'index' => 'increment_id',
            'filter_index'=>'main_table.increment_id'
        ));

Answer 2

To avoid "ambiguous"

filter_index' => 'main_table.grand_total

is required. Problem solved.