Magento – Magento Admin Order Grid Filter by “Status” giving error while joined with “sales_flat_order” table

adminfiltergridjoin-tablemagento-1.9

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.

Best Answer

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');
Related Topic