Column 'store_id' in where clause is ambiguous when adding custom order attribute to grid and changing store

Question

I have created a custom order attribute and I am now able to show it in the sales order grid; my previous question: Adding custom sales order attribute to order grid

If I were to change the drop down of Purchased From (Store) and try to filter the grid by a specific store, I get redirect back to the dashboard view in backend and when I revisit the sales order grid, I start getting this error:

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

I believe the issue is with the way I join onto the sales_flat_order table:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $select = $collection->getSelect();
    $select->joinLeft(array
    (
        'order' => Mage::getModel('core/resource')->getTableName('sales/order')),
        'order.entity_id=main_table.entity_id',
        array('sales_rep' => 'sales_rep')
    );
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

But I am not sure how to solve this. Any ideas?

Answer 1

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

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

Second Solution edit your column code:

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',
            ));
        }

hope this helps you

Answer 2

The problem is how you do the addColumn. By doing a join with sales_flat_order all columns will be ambiguous when you try to make the addColumn.

What you should do is in columns that can be ambiguous should put the prefix of the table. main_table for sales_flat_order_grid and in the case of the code must be sales_rep: order

Answer 3

I have solved it, the issue was NOT with the _prepareCollection() method - it was fine.

The problem was with the _prepareColumns() method, because both tables sales_flat_order_grid and sales_flat_order has a store_id and the addColumn method did not know which one to use.

So, I did this:

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

And this has fixed it.

Answer 4

I have faced the same issue, but at the end i got a solution, The complete solution for the issue: First copy the core file in local directory:

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

To

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

Now

Step 1. In your app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php file find the function _prepareColumns() , Inside this function find the below code

    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,
            ));
    }

Step 2. replace the above code with below one

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

Step 3. Now again search below code in same function _prepareColumns()

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

Step 4. 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',
        ));

Step 5. Now try to filter by G.T.(base) or G.T.(Purchased), if you still face issue for G.T.(base) and G.T.(Purchased) then replace the below code for both in the same function _prepareColumns()

    $this->addColumn('base_grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Base)'),
            'index' => 'base_grand_total',
            'type'  => 'currency',
            'currency' => 'base_currency_code',
            'filter_index' => 'main_table.base_grand_total',
        ));

    $this->addColumn('grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
            'index' => 'main_table.grand_total',
            'type'  => 'currency',
            'currency' => 'order_currency_code',
            'filter_index' => 'main_table.grand_total',
        ));

Cheers!

Answer 5

We should use
'filter_index' => 'main_table.grand_total', line of code and smbigios error will dissapear.

Solution from Ajendra Panwar works for me. Thanks