Integrity constraint violation: 1052 Column 'increment_id' in where clause is ambiguous

Solved it!

The increment_id column need to have the additional

'filter_index'=>'main_table.increment_id',

So the Grid column now looks like this:

$this->addColumn('order_id', array(
    'header' => Mage::helper('sales')->__('Order Id'),
    'align' =>'left',
    'index' => 'increment_id',
    'filter_index'=>'main_table.increment_id',
));

The increment_id column need to have the additional

'filter_index'=>'main_table.increment_id',

Copy file

app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php 

to

app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php

Find function _prepareColumns()

Change

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

To

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

I have searched everywhere for this by I am not getting any exact result which will help me in this. But now I am with the result of why such thing is happening. It’s simple as it is saying “Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous”, means it is finding another created_at field. because when we adding or joining the other table then it has also a field named as created_at. So what is the solution for this?

Any idea……………

It's simple just told Magento that created_at is of the main_table not of my custom table, how can you do so, I will tell you the full procedure.

Step 1. Find the below code in the sales order grid.php file

$this->addColumn('created_at', array(
     'header' => Mage::helper('sales')->__('Purchased On'),
     'index'  => 'created_at',
     'type'   => 'datetime',
     'width'  => '100px',
));

Step 2. in the second step just replace the code with below one.

$this->addColumn('created_at', array(
     'header'       => Mage::helper('sales')->__('Purchased On'),
     'index'        => 'created_at',
     'type'         => 'datetime',
     'width'        => '100px',
     'filter_index' => 'main_table.created_at',
));

Have you find what I have changed in the code, not you, ok let me explain. in this code I have added the following line:

'filter_index' => 'main_table.created_at',

thinking of what’s the use of the following line, here created_at column of sales order grid find created_at from the main the collection. But we are getting twice created_at in the collection. So I have added the following line that this created_at is of the main table sales_flat_order not of the other table.

That’s the process

for more info, you can visit the blog

http://www.webtechnologycodes.com/integrity-constraint-violation-1052-column-created_at-in-where-clause-is-ambiguous/