6

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.

4

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');
  • Thanks Vernard, but main_table and s both has field called status. – Adarsh Khatri Jun 18 '15 at 1:26
  • That's why it has the Integrity constraint violation is showing. Is is possible for you to modify the status column's filter to main_table.status? – Vernard Luz Jun 18 '15 at 1:30
  • If I do that, then status field will not render anything. Moreover, I don't want to touch anything in core fields. – Adarsh Khatri Jun 18 '15 at 1:39
  • I've updated my answer. Please see if that works. If it does, please select this as the answer to your question. – Vernard Luz Jun 18 '15 at 3:33
3

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
));
1

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;
}
1

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',

));

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.