Magento Observer to add order grid column - ambiguous issue

Question

I have fix the problem, FINALLY!

Basically I'm getting WHERE part with $where = $select->getPart('where') and then I go through each condition and searching for created_at, If I find matching then I replace created_at with main_table.created_at.

I've tested this, and everything works fine, if there is something that can be "buggy" please let me know.

Thanks all!!

public function salesOrderGridCollectionLoadBefore($observer)
{

  $collection = $observer->getOrderGridCollection();
  $select     = $collection->getSelect();
  $select->joinLeft(array('custab' => 'my_custom_table'), 'main_table.entity_id = custab.order_id',array('custab.field_to_show_in_grid'));

  if ($where = $select->getPart('where')) {
      foreach ($where as $key=> $condition) {
          if (strpos($condition, 'created_at')) {
              $new_condition = str_replace("created_at", "main_table.created_at", $condition);
              $where[$key] = $new_condition;
          }
      }
      $select->setPart('where', $where);
  }

}

I'm trying to add new column in sales order grid from custom table using observer. Everything works fine until I try to filter grid using column created_at.

Problem is because I have same column name (created_at) in custom table and in sales_flat_order_grid table.

I'm getting this error

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

If I edit this line 'index' => 'created_at' to 'index' => '**main_table**.created_at', in app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

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

everything work fine, but I don't want to change core files or to copy them to local folder and edit, I think that there is some simple solution that I need to add to my observer.

Here is my Observer.php

class Testinggrid_ExtendOrderGrid_Model_Observer{
    public function salesOrderGridCollectionLoadBefore($observer)
    {
       $collection = $observer->getOrderGridCollection();
       $select     = $collection->getSelect();
       $select->joinLeft(array('custab' => 'my_custom_table'), 'main_table.entity_id = custab.order_id',array('custab.field_to_show_in_grid'));
    }
}

Here is my module layout

<layout>
  <sales_order_grid_update_handle>
    <reference name="sales_order.grid">
        <action method="addColumnAfter">
            <columnId>field_to_show_in_grid</columnId>
            <arguments>
                <header>Column header</header>
                <index>field_to_show_in_grid</index>
                <filter_index>field_to_show_in_grid</filter_index>
                <type>text</type>
            </arguments>
            <after>shipping_name</after>
        </action>
    </reference>
  </sales_order_grid_update_handle>
  <adminhtml_sales_order_grid>
    <!-- apply layout handle defined above -->
    <update handle="sales_order_grid_update_handle" />
  </adminhtml_sales_order_grid>
  <adminhtml_sales_order_index>
    <!-- apply layout handle defined above -->
    <update handle="sales_order_grid_update_handle" />
  </adminhtml_sales_order_index>
</layout>

Answer 1

After lots of research, I've come up with various solutions to this problem, non of which seem ideal, but someone might be able to improve on one of them.

First, the why of the problem. Before you do your join, Magento has already prepared a portion of the query, represented as objects. This portion set ups the 'select' and and "where's" that need to be done for filtering, it will result in a query that will look something like:

SELECT `main_table`.* FROM `sales_flat_order_grid`
WHERE (`grand_total` <= '20')

The problem is the 'where' part of the query, as the column names are not prefixed with the table name alias main_table. If you then try to extend this query by adding a join to a table that has a column with the same name as any column in the where clause, you'll get the 'ambiguous' error. For example if you were to join the above query to the sales_flat_order table, then it would fail as that table also has a grand_total column, making the 'where' clause in the filter ambiguous.

Magento has a mechanism to deal with just this problem, the filterIndex. Column objects which form the the grid can have a filter index set, using

$column = $block->getColumn('grand_total');
$column->setFilterIndex('main_table.grand_total');

Which would adjust the 'where' clause in the above query to WHERE `main_table`.`grand_total` <= '20'

Which would solve all the problems if we were able to get at the $column object before the SQL has actually been prepared.

Solutions

1. Override the core files

Override and subclass Mage_Adminhtml_Block_Widget_Grid class. Only override the the _prepareColumns() method like so:

class Mycomp_Mymodule_Block_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
{
    protected function _prepareColumns()
    {
        //get the columns from the parent so they can be returned as expected
        $columns = parent::_prepareColumns();
        $this->addColumnAfter('custom_column', array(
            'header'    => 'Custom Column',
            'index'     => 'custom_column',
            'type'      => 'text',
            'filter_index'=> 'custom_table.custom_column',
        ), 'billing_name' );
        //columns need to be reordered
        $this->sortColumnsByOrder();
        return $columns;
    }
}

2. Rewrite the query before its executed

This is the solution shown in the question, where you grab the 'where' clause and then do some sort of find and replace to add in the main_table prefix. Something like

$select = $collection->getSelect();    
$where = $select->getPart('where');
//find an replace each element in the where array

3. Use a subquery in your 'join' clause

As the problem is caused by a column with the same name in both tables, its possible to change the 'join' query to rename or remove the ambiguous column. Adjusting the join clause to:

$subquery = new Zend_Db_Expr( 
    '(SELECT order_id AS order_alias, 
             field_to_show_in_grid AS field_to_show_in_grid_alias
      FROM my_custom_table)');
$select->joinLeft( array('custab' => $subquery), 
                   'main_table.entity_id = custab.order_alias',
                   array('custab.field_to_show_in_grid_alias'));

Whilst this does work, the query is too slow to be practical. The query can be modified to speed it up like so:

$subquery = new Zend_Db_Expr( 
    '(SELECT field_to_show_in_grid
      FROM my_custom_table
      WHERE main_table.entity_id=my_custom_table.order_id)');
$select->addFieldToSelect( array('field_to_show_in_grid_alias'=>$subquery) );

Whilst this works and is quick, the problem is that Magento uses the objects that make up this query twice, once for the grid itself and also in a 'count' to generate the pagination wigits at the top of the grid page. Unfortunately, when used in the 'count' query, Magento does not use the selected columns, only the 'from', 'join' and 'where' portions of the query. This means that if you use the above query, you can't filter on the new column you are adding.

4. Add column to the sales_flat_order_grid

The sales_flat_order_grid table can be extended to include the extra column you require and have that column automatically updated. This technique is described here https://magento.stackexchange.com/a/4626/34327

5. Intercept and add the filter index before the query is built

There is an event which can be intercepted, interrogated and altered before the SQL has been fully prepared. This is resource_get_tablename, but you need to check that you are working with the correct table.

To get this working, I'd write an observer of the resource_get_tablename event as follows

public function resourceGetTablename(Varien_Event_Observer $observer)
{
    //Check we are working with the correct table
    if( $observer->getTableName() == 'sales_flat_order_grid' ) {

        $block = Mage::getSingleton('core/layout')->getBlock('sales_order.grid');

        foreach( $block->getColumns() as $column ) {
            //create a filter index for each column using 'main_table' prefixed to the column index
            $column->setFilterIndex('main_table.' . $column->index);
        }

    }
}

I hope that someone can improve upon one of these methods to create something really helpful.

Answer 2

Try changing

<filter_index>field_to_show_in_grid</filter_index>

To

<filter_index>main_table.created_at</filter_index>

See Adding a column to Magento orders grid - alternative way using layout handles