You can find several articles which are related to adding columns to Magento admin grids in Atwix blog. However, not everything is fully covered, and our readers keep asking how to add SKU, Products columns, etc. So, let’s take a closer look at this topic.
Today, we will add SKU column to the sales order grid. As a continuation of our last article related to this theme, we will try to use layouts again. Actually, the problem is that SKUs of ordered items are not stored in a single field in the database. This fact causes some difficulties if we want to filter and search through this column.
So, here are the lines that we need to add to our observer (from the previous article) to get SKUs for orders:
$select->joinLeft('sales_flat_order_item', 'sales_flat_order_item.order_id = main_table.entity_id', array('skus' => new Zend_Db_Expr('group_concat(sales_flat_order_item.sku SEPARATOR ", ")')));
$select->group(‘main_table.entity_id');
As you can see, we join sales_flat_order_item table and grab SKUs using MySQL group_concat function.
Then, here is how we insert a column – just add the following code to our layout extendedgrid.xml:
<action method="addColumnAfter">
<columnId>skus</columnId>
<arguments>
<header>Skus</header>
<index>skus</index>
<type>text</type>
</arguments>
<after>payment_method</after>
</action>
As a result, you should see a new column in the grid. That is all if you don’t care about filtering. Otherwise, if you need a fully workable column – be ready to make some tweaks.
The solution for filtering SKUs column can be found in one more our article Grid filter for columns with complex values (please, read it before continue).
As you can guess, we will use a callback function for filtering. But, how do we insert filter_condition_callback parameter via XML? Fortunately, Magento allows us to use the helper method as arguments for action element. Here is the modified layout:
<action method="addColumnAfter">
<columnId>skus</columnId>
<arguments helper="atwix_extendedgrid/getSkusColumnParams"/>
<after>payment_method</after>
</action>
Create a helper method now:
public function getSkusColumnParams()
{
return array(
'header' => 'SKUs',
'index' => 'skus',
'type' => 'text',
'filter_condition_callback' => array('Atwix_ExtendedGrid_Model_Observer', 'filterSkus'),
);
}
Here filterSkus is out callback function that handles filtering. We will place it to observer:
public function filterSkus($collection, $column)
{
if (!$value = $column->getFilter()->getValue()) {
return $this;
}
$collection->getSelect()->having(
"group_concat(`sales_flat_order_item`.sku SEPARATOR ', ') like ?", "%$value%");
return $this;
}
Furthermore, the function takes two arguments: $column and $collection. We add HAVING condition to the collection select to filter records. Now, a search through the SKU column should work well. But take a closer look: 
Records count is wrong now. The fact is Magento uses another query to get records count. It is generated in getSelectCountSql method of Mage_Sales_Model_Resource_Order_Grid_Collection:
/**
* Get SQL for get record count
*
* @return Varien_Db_Select
*/
public function getSelectCountSql()
{
if ($this->getIsCustomerMode()) {
$this->_renderFilters();
$unionSelect = clone $this->getSelect();
$unionSelect->reset(Zend_Db_Select::ORDER);
$unionSelect->reset(Zend_Db_Select::LIMIT_COUNT);
$unionSelect->reset(Zend_Db_Select::LIMIT_OFFSET);
$countSelect = clone $this->getSelect();
$countSelect->reset();
$countSelect->from(array('a' => $unionSelect), 'COUNT(*)');
} else {
$countSelect = parent::getSelectCountSql();
}
return $countSelect;
}
Original query looks like this:
SELECT COUNT(*) FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order_item` ON `sales_flat_order_item`.order_id=`main_table`.entity_id GROUP BY `main_table`.`entity_id` HAVING (group_concat(`sales_flat_order_item`.sku SEPARATOR ', ') like '%touch%')
In this case, we use SELECT COUNT(DISTINCT main_table.entity_id) instead of SELECT COUNT(*) to fix it and replace HAVING condition:
SELECT COUNT(DISTINCT main_table.entity_id) FROM `sales_flat_order_grid` AS `main_table` INNER JOIN `sales_flat_order_item` ON `sales_flat_order_item`.order_id=`main_table`.entity_id WHERE (sales_flat_order_item.sku like '%touch%')
Then, let’s apply our tweaks by rewriting this class:
<sales_resource>
<rewrite>
<order_grid_collection>Atwix_ExtendedGrid_Model_Resource_Sales_Order_Grid_Collection</order_grid_collection>
</rewrite>
</sales_resource>
And the rewritten method is:
public function getSelectCountSql()
{
$countSelect = parent::getSelectCountSql();
if (Mage::app()->getRequest()->getControllerName() == 'sales_order') {
$countSelect->reset(Zend_Db_Select::GROUP);
$countSelect->reset(Zend_Db_Select::COLUMNS);
$countSelect->columns("COUNT(DISTINCT main_table.entity_id)");
$havingCondition = $countSelect->getPart(Zend_Db_Select::HAVING);
if (count($havingCondition)) {
$countSelect->where(
str_replace("group_concat(`sales_flat_order_item`.sku SEPARATOR ', ')", 'sales_flat_order_item.sku', $havingCondition[0])
);
$countSelect->reset(Zend_Db_Select::HAVING);
}
}
return $countSelect;
}
It should work now. Also, the source code can be found at GitHub (tested with Magento v1.8.0.0).
On the other hand, there is also one more way to go. It is quite simple and requires no rewrites. The idea is to add a new column to sales_flat_order_grid and populate it with SKUs on every order creation using an observer. Then, you can simply add this column to the orders grid using layout and no joins are required. Hope you know how to do it :). You are welcome to share your methods of adding columns in the comments below.
Update. If you get “ambiguous column names” error when trying to filter grid then add the following code to app/code/local/Atwix/ExtendedGrid/Model/Resource/Sales/Order/Grid/Collection.php:
/**
* Init select
* @return Mage_Core_Model_Resource_Db_Collection_Abstract
*/
protected function _initSelect()
{
$this->addFilterToMap('store_id', 'main_table.store_id')
->addFilterToMap('created_at', 'main_table.created_at')
->addFilterToMap('updated_at', 'main_table.updated_at');
return parent::_initSelect();
}
This way we add mapping for those column names that are being used in multiple tables.
Source code at github updated
Update. The extensions below were suggested by Magento community after this post was published. Although we have not had a chance to review them personally, but we are including links to them for you to check out:
Read more:
