SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'store_id' in where clause is ambiguous

Question

After searching around, I still haven't found a solution. So I thought I would post it here. Any help would be greatly appreciated.

When I go to filter one of the columns it redirects me to dashboard. After that when I click on sales -> orders it loads the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'store_id' in where clause is ambiguous, query was: SELECT COUNT(*) FROM mg_sales_flat_order_grid AS main_table INNER JOIN mg_sales_flat_order AS order ON order.entity_id=main_table.entity_id WHERE (store_id = '1')

Answer 1

Try to use below code just before the $this->setCollection($collection);

$collection->addFilterToMap('store_id', 'main_table.store_id');

Another solution used in some default Magento modules is

if (!Mage::app()->isSingleStoreMode()) {
            $this->addColumn('store_id', array(
                'header'    => Mage::helper('sales')->__('Purchased From (Store)'),
                'index'     => 'store_id',
                'type'      => 'store',
                'store_view'=> true,
                'display_deleted' => true,
                'filter_index' => 'main_table.store_id',
            ));
        }