Magento Admin Order Grid Filter by "Status" giving error while joined with "sales_flat_order" table

Question

I am working on a project where there is custom column already installed on sales_flat_order table.

I know, in order grid magento uses sales_flat_order_grid table to fetch data.

I have used join technique and works fine, all filters are also working except filter by status.

My code on my Grid.php

   /**
    * @brief add custom_column
    * @param Mage_Sales_Model_Resource_order_grid_collection $collection
    */
    public function setCollection($collection)
    {
        $collection->getSelect()->join(array('s' => 'sales_flat_order'), 'main_table.entity_id=s.entity_id', 'custom_column');

         //Mage::log((string) $collection->getSelect(), null, 'order-grid.log');

        parent::setCollection($collection);
    }

    protected function _prepareColumns()
    {
        $this->addColumn('custom_column', array(
            'header'        => Mage::helper('sales')->__('custom column'),
            'index'         => 'custom_column',
            //'filter'        => 's.custom_column',
            'type'          => 'currency',
            'renderer'      => 'DigitalCinema_Pos_Block_Adminhtml_Sales_Order_Renderer_Grid',
        ));
        return parent::_prepareColumns();
    }

With this all filters work (not sure why it works even without filter tag in my custom column), and when I do filter by status then it tells me

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

Now

When I put 'filter' => 's.custom_column', back, I get following error:

Fatal error: Call to a member function setColumn() on boolean in \app\code\core\Mage\Adminhtml\Block\Widget\Grid\Column.php on line 370

I even tried putting 'filter_index' => 's.custom_column' too, but same result.

It's driving me insane.

Answer 1

I'm not sure but the error message suggests that the query for filtering does not mention where to get the status column, from main_table or s?

To be sure, check the query that is executed when the Integrity constraint violation appears.

You can log the query by modifying the {magento_root}\lib\Varien\Db\Adapter\Pdo\Mysql.php file and set $_debug = true and $_logAllQueries = true. Pretty sure it will appear at the end of log file {magento_root}/var/debug/sql.txt.

EDIT:

Put this on _prepareLayout() method of your Grid.php:

$this->getLayout()
    ->getBlock('sales_order.grid')
    ->getColumn('status')
    ->setFilterIndex('main_table.status');

Answer 2

Since your main table and s table both have the column called status and when you are trying to display magento not able to know from which table the column status to be called. so instead call like this

if you want to status from main table call 'filter_index'=>'main_table.status',

or else from s use 'filter_index'=>'s.status',

Edited

$this->addColumn('status', array(
    'header' => Mage::helper('sales')->__('Status'),
    'index' => 'status',
    'type'  => 'options',
    'width' => '70px',
    'options' => $optionsArray,
    'filter_index'=>'main_table.status', // some thing like this
));

Answer 3

Try to add this method in your class :

protected function _addColumnFilterToCollection($column)
{
    if ($this->getCollection()) {
        $field = ( $column->getFilterIndex() ) ? $column->getFilterIndex() : $column->getIndex();
        $field = 'main_table.'.$field;
        if ($column->getFilterConditionCallback()) {
            call_user_func($column->getFilterConditionCallback(), $this->getCollection(), $column);
        } else {
            $cond = $column->getFilter()->getCondition();
            if ($field && isset($cond)) {
                $this->getCollection()->addFieldToFilter($field , $cond);
            }
        }
    }
    return $this;
}

Answer 4

Try to use Alias in your join section, like below :

$select->joinLeft(array(
                    'order' => Mage::getModel('core/resource')->getTableName('sales/order')),
                    'order.entity_id=main_table.entity_id',
                    array(
                    'custom_column' => 'order.custom_column',
                    'custom_column2' => 'order.custom_column2',
                    )

EDIT : Also use column names with Aliases like 'filter_index'=>'main_table.increment_id' in addColumn method e.g.

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

));

Answer 5

Below option works for me !!

Open file from magento/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

go to line no. 226

replace 'index' => 'status', with 'index' => 'main_table.status',

Save the file. It resolve the issue of filter by order status.