How to Add Email Address in Sales Orders Admin Grid in Magento 1.9

columnemailmagento-1.9sales-order

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%')

Best Answer

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'
        ));
Related Topic