Magento Observer will add order grid column - controversial issue

Question

I will fix the problem, FINALLY!

Basically I get the WHERE part with $where = $select->getPart('where') and then I look through each condition and looked for created_at . If I find a match, then instead of created_at I replace main_table.created_at .

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

Thanks everyone!

 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 am trying to add a new column in a sales order grid from a user table using an observer. Everything works fine until I try to filter the grid using the created_at column.

The problem is that I have the same column name ( created_at ) in the user table and in the sales_flat_order_grid table.

I get this error

SQLSTATE [23000]: Violation of integrity constraint: 1052 Column 'created_at' in where the sentence is ambiguous

If I edit this line 'index' => 'created_at' in '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 works fine, but I don’t want to change the main files or copy them to a local folder and edit, I think 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

Try to change

 <filter_index>field_to_show_in_grid</filter_index> 

For

 <filter_index>main_table.created_at</filter_index> 

See Adding a column to the Magento order grid . An alternative way to use document layouts.

Answer 2

After much research, I came up with various solutions to this problem that do not seem ideal, but someone can improve one of them.

First, why is the problem. Before you join, Magento has already prepared the part of the request, presented as objects. This part sets up the "select" and "where" that need to be run for filtering, this will result in a query that looks something like this:

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

The problem is the “where” part of the query, because the column names do not have a prefix with the table alias main_table . If you then try to expand this query by adding a join to a table with a column with the same name as the any column in the where clause, you will get an "ambiguous" error. For example, if you joined the above query in the sales_flat_order table, then it would fail, since this table also has a grand_total column, which makes the where clause in this file ambiguous.

Magento has a mechanism to solve this problem, filterIndex . The column objects that form the grid can have a filter index using

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

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

To solve all the problems, if we could get the $column object before SQL was actually prepared.

Decision

• Override core files

Override Class and Subclass Mage_Adminhtml_Block_Widget_Grid . Just override the _prepareColumns() method as follows:

 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 request before executing it

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

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

3. Use subquery in 'join' clause

Since the problem is caused by a column with the same name in both tables, you can modify the 'join' query to rename or delete an ambiguous column. Connection offer setup:

 $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')); 

While this works, the request is too slow to be practical. The query can be modified to speed it up as follows:

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

While this works and runs quickly, the problem is that Magento uses the objects that make up this query twice, once for the grid itself, and also for “counting”, to create paginated widgets at the top of the grid page . Unfortunately, when using the "count" query, Magento does not use the selected columns, but only the "from", "attach" and "where" parts. This means that if you use the above query, you cannot filter the new column that you are adding.

4. Add column to sales_flat_order_grid

The sales_flat_order_grid table can be expanded to include the extra column that you need and update that column. This method is described here https://magento.stackexchange.com/a/4626/34327

5. Intercept and add a filter index before creating a query

There is an event that can be intercepted, interrogated, and modified before SQL is 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 would write a resource_get_tablename event observer 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 someone can improve one of these methods to create something really useful.

Answer 3

You can use $collection->addFilterToMap('created_at', 'main_table.created_at'); in his observer