Adding SKU column to Magento orders grid

by Volodymyr Vygovskyi

January 16, 2014 Comments (22)

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:

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 (more information about it is in the following article. Here is the modified layout:

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:

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%')

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:

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:

Subscribe to our blog

Interestedin Atwix?

Smart Brands Choose Us.

From Mark Cuban and Sir Richard Branson backed startups, to Inc 500 US Fastest Growing Companies and Global Brands choose us to deliver and support unparalleled eСommerce experience for their customers.