3

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

8

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'
        ));
  • Already sorted but thank you Alex ;) – Rob D. A. Jun 28 '17 at 21:35
  • The only problem I've had was "ambiguous" but ... I found how to fix it ;) Also I found other solution, creating additional renderer but it was just overcomplicated :) – Rob D. A. Jun 28 '17 at 21:37
  • Glad to hear that, good luck ! – Alex Constantinescu Jun 28 '17 at 21:38
2

To avoid "ambiguous"

filter_index' => 'main_table.grand_total

is required. Problem solved.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.