How to extend Magento Order Grid?

Here is small example which explains how to modify order grid. Main class for order grid is “Mage_Adminhtml_Block_Sales_Order_Grid”, if you want to add some column you have to rewrite this class (block).
How to rewrite magento block:
Inchoo_Test_Block_Adminhtml_Order_Grid
If you call block “adminhtml/sales_order_grid”, you will get Inchoo_Test_Block_Adminhtml_Order_Grid of course you need to create your class Inchoo_Test_Block_Adminhtml_Order_Grid.
This is example of our block Inchoo_Test_Block_Adminhtml_Order_Grid:
< ?php class Inchoo_Test_Block_Adminhtml_Order_Grid extends Mage_Adminhtml_Block_Widget_Grid { public function __construct() { parent::__construct(); $this->setId('sales_order_grid');
$this->setUseAjax(true);
$this->setDefaultSort('created_at');
$this->setDefaultDir('DESC');
$this->setSaveParametersInSession(true);
}
/**
* Retrieve collection class
*
* @return string
*/
protected function _getCollectionClass()
{
return 'sales/order_grid_collection';
}
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
//we changed mysql query, we added inner join to order item table
$collection->join('sales/order_item', 'order_id=entity_id', array('name'=>'name', 'sku' =>'sku', 'qty_ordered'=>'qty_ordered' ), null,'left');
$this->setCollection($collection);
return parent::_prepareCollection();
}
protected function _prepareColumns()
{
$this->addColumn('real_order_id', array(
'header'=> Mage::helper('sales')->__('Order #'),
'width' => '80px',
'type' => 'text',
'index' => 'increment_id',
));
if (!Mage::app()->isSingleStoreMode()) {
$this->addColumn('store_id', array(
'header' => Mage::helper('sales')->__('Purchased from (store)'),
'index' => 'store_id',
'type' => 'store',
'store_view'=> true,
'display_deleted' => true,
'filter_index' => 'main_table.store_id'
));
}
$this->addColumn('created_at', array(
'header' => Mage::helper('sales')->__('Purchased On'),
'index' => 'created_at',
'type' => 'datetime',
'width' => '100px',
'filter_index' => 'main_table.created_at'
));
$this->addColumn('billing_name', array(
'header' => Mage::helper('sales')->__('Bill to Name'),
'index' => 'billing_name',
));
$this->addColumn('qty_ordered', array(
'header' => Mage::helper('sales')->__('Items Ordered'),
'index' => 'qty_ordered',
'type' => 'number',
'total' => 'sum'
));
$this->addColumn('sku', array(
'header' => Mage::helper('catalog')->__('SKU'),
'index' => 'sku',
'type' => 'text'
));
$this->addColumn('base_grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Base)'),
'index' => 'base_grand_total',
'type' => 'currency',
'currency' => 'base_currency_code',
));
$this->addColumn('grand_total', array(
'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
'index' => 'grand_total',
'type' => 'currency',
'currency' => 'order_currency_code',
));
$this->addColumn('status', array(
'header' => Mage::helper('sales')->__('Status'),
'index' => 'status',
'type' => 'options',
'width' => '70px',
'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
));
return $this;
}
protected function _prepareMassaction()
{
$this->setMassactionIdField('entity_id');
$this->getMassactionBlock()->setFormFieldName('order_ids');
$this->getMassactionBlock()->setUseSelectAll(false);
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/cancel')) {
$this->getMassactionBlock()->addItem('cancel_order', array(
'label'=> Mage::helper('sales')->__('Cancel'),
'url' => $this->getUrl('*/sales_order/massCancel'),
));
}
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/hold')) {
$this->getMassactionBlock()->addItem('hold_order', array(
'label'=> Mage::helper('sales')->__('Hold'),
'url' => $this->getUrl('*/sales_order/massHold'),
));
}
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/unhold')) {
$this->getMassactionBlock()->addItem('unhold_order', array(
'label'=> Mage::helper('sales')->__('Unhold'),
'url' => $this->getUrl('*/sales_order/massUnhold'),
));
}
$this->getMassactionBlock()->addItem('pdfinvoices_order', array(
'label'=> Mage::helper('sales')->__('Print Invoices'),
'url' => $this->getUrl('*/sales_order/pdfinvoices'),
));
$this->getMassactionBlock()->addItem('pdfshipments_order', array(
'label'=> Mage::helper('sales')->__('Print Packingslips'),
'url' => $this->getUrl('*/sales_order/pdfshipments'),
));
$this->getMassactionBlock()->addItem('pdfcreditmemos_order', array(
'label'=> Mage::helper('sales')->__('Print Credit Memos'),
'url' => $this->getUrl('*/sales_order/pdfcreditmemos'),
));
$this->getMassactionBlock()->addItem('pdfdocs_order', array(
'label'=> Mage::helper('sales')->__('Print All'),
'url' => $this->getUrl('*/sales_order/pdfdocs'),
));
return $this;
}
public function getRowUrl($row)
{
if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
return $this->getUrl('*/sales_order/view', array('order_id' => $row->getId()));
}
return false;
}
public function getGridUrl()
{
return $this->getUrl('*/*/grid', array('_current'=>true));
}
}
If you want to add some column you need to add next code in method: _prepareColumns
$this->addColumn('sku', array(
'header' => Mage::helper('catalog')->__('SKU'),
'index' => 'sku',
'type' => 'text'
));
Also you will notice that we modified method: _prepareCollection(), we changed SQL query, we made inner join to second database table in order to get data from second database table. This is our working example and your SQL query you can modify as you wish.
63 comments
I want to change the csv file when downloaded. But I dont know how to override the function getCsv() in core/Mage/Adminhtml/Block/Report/Grid.php? Please help
Or you could used this instead :
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
//we changed mysql query, we added inner join to order item table
$collection->join(‘sales/order_item’, ‘order_id=entity_id’, array(‘name’=>’name’, ‘sku’ =>’sku’, ‘qty_ordered’=>’qty_ordered’ ), null,’left’);
$this->setCollection($collection);
return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
}
like that you will avoid redefining massActions and _prepareColumns functions 🙂
If one order has more than one item it throws exception like “Item (Webtex_Giftcards_Model_Sales_Order) with the same id”, how to resolve this?, I need each item in separate row.
Use this simple and wonderfull extension will save your time.
http://codecanyon.net/item/order-grid-pro/11501101
simple way to add column
Step 1: Create the Renderer directory under /app/code/core/Mage/Adminhtml/Block/Sales/Order
Step 2:
Create a Block File in /app/code/core/Mage/Adminhtml/Block/Sales/Order/Renderer/Red.php (You can use any name)
getData($this->getColumn()->getIndex());
$order_id=$row->getData(‘increment_id’);
$order = Mage::getModel(‘sales/order’)->loadByIncrementID($order_id);
$items = $order->getAllItems();
// print_r($items);exit();
foreach ($items as $itemId => $item)
{
if($item->getSku())
{
$sku[] = $item->getSku();
}
}
if(count($sku))
{
$skutext = implode(‘,’,$sku);
}
$conbinetext=$skutext;
return $conbinetext;
}
}
?>
Step 3: Add the Below Code in
/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php
$this->addColumn(‘Shopby’,
array(
‘header’=>Mage::helper(‘catalog’)->__(‘Sku’),
‘index’ => ‘shopby’,
‘filter’=> false,
‘sortable’=>false,
‘renderer’=> ‘Mage_Adminhtml_Block_Sales_Order_Renderer_Red’,// THIS IS
WHAT THIS POST IS ALL ABOUT
));
People! Please do not suggest changing core magento files. Not a good idea.
I have done the changes as mentioned in the article and it is working beautifully. I am just stuck at one point and would like to have your suggestions for the same – The CUSTOMER Group Dropdown shows up and the searching on that column is also working beautifully however no values are showing up in that column. It is strange since the preceding column which is Order Status is also a dropdown but shows all values.
The most needed things for me to be displayed on the grid are thumbnails, quantity and shipping method. I’m using an extension to do that (http://amasty.com/extended-order-grid.html) as modifying code is not my strong side.
@Tajinder, You are correct that it isn’t 100%. However, the method in this article breaks all paging/counters regardless of filters used whereas my method works 100% when not using the SKU filter and when using the SKU filter the paging still works, just the count will be off.
An override of the sales/order_grid_collection class’ getSize method would be required to fix this issue which is not worth the trouble IMO as it is still very functional. Although, a pull request would gladly be accepted. 😉
Colin, Search counter in admin grid is not working with your module as well.
Paulo, this is because of the join, the count and paging get screwed up. So, you should instead query for the grid data in two passes. This is actually also much faster as MySQL is not able to optimize these complex queries.
See my module on github for an example: https://github.com/colinmollenhour/Cm_OrderProducts
Hello,
I modified with success my grid, but when it comes to count the orders, it’s not working correctly. It’s always showing just one page and 1 item on counter (above grid). The grid and all the list are ok.
Could anyone provide me a litte help ?
Thanks.
Hi Paulo, Have you found a workaround for this problem? I have also number 1 displayed no matter how many records there are in my custom order grid.
Thanks!
I added the tables to show product names and email in the following order collection:
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->join(‘order’, ‘order.entity_id=`main_table`.entity_id’, ‘customer_email’);
$collection->getSelect()->group(‘main_table.entity_id’);
$collection->join(
‘sales/order_item’,’`sales/order_item`.order_id=`main_table`.entity_id’, array(
‘names’ => new Zend_Db_Expr(‘group_concat(`sales/order_item`.name SEPARATOR “,”)’),
)
);
$collection->getSelect()->group(‘main_table.entity_id’);
$this->setCollection($collection);
return parent::_prepareCollection();
}
To filter product names I used
if ($this->getCollection() && $column->getFilter()->getValue())
{
if ($column->getId() == ‘names’)
{
$this->getCollection()->join(
‘order_item’, ‘order_item.order_id=main_table.entity_id’, array(
‘names’ => new Zend_Db_Expr(‘group_concat(order_item.name SEPARATOR “,”)’),
)
)
->getSelect()
->having(‘names like ?’, ‘%’.$column->getFilter()->getValue().’%’);
return $this;
}
}
It shows one row only but it should show other orders, can anyone fix this?
If you join multiple tables to get the query then the search is not working. My query is as follows. It is loading fine. But when you try to search then it shows an error. Please do you have any suggestions to make it work?
$collection = $observer->getOrderGridCollection();
$collection->getSelect()
->joinLeft(‘sales_flat_order_payment’, ‘main_table.entity_id = sales_flat_order_payment.parent_id’,’method’)
->joinLeft(‘customer_entity’, ‘main_table.customer_id = customer_entity.entity_id’,’email’)
->joinLeft(‘sales_flat_order_item’, ‘main_table.entity_id = sales_flat_order_item.order_id’,’name’);
$collection->getSelect()->group(‘main_table.entity_id’);
hello friends.
I could help introduce the field Tax / vat number in the grid order because I fail in any way.
thank you very much
hello, i have modify magento manage products grid and adding Brand (attribute) column, Stock Status column (filter: In Stock, Out of Stock), Backorders column (filter: No Backorders, Allow Qty Below, Allow Qty Below 0 and Notify Customer). Array status for Backorders in database: ‘0’=>’No Backorders’,’1’=>’Allow Qty Below 0′,’2’=>’Allow Qty Below 0 and Notify Customer’.
question: i want to adding filter ‘Allow Qty Below 0 and Allow Qty Below 0 and Notify Customer’
so for Backorders i will have 3 filter condition:
No Backorders,
Allow Qty Below 0,
Allow Qty Below 0 and Notify Customer,
Allow Qty Below 0 and Allow Qty Below 0 and Notify Customer
how to do that?
my code:
in prepareCollection :
in _prepareColumns :
Thanks
“SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘status’ in where clause is ambiguous”
Good spot Sean Breeden – just managed to do exactly that (not have status on my extended Grid) and using the status filter kills the Order Grid straight away!
Add it back in to the _prepareColumns() function and happy days again 🙂
Many thanks
hello
very good post and will be useful for big amount of magento users.
i thank in some cases it would be better use some magento extension to simplify such process.
i use amasty extensions for a long time and it is gold issue.
product grid
http://amasty.com/extended-product-grid.html
and their new extension
http://amasty.com/extended-order-grid.html
good luck
Perfect!
Hi
I think it’s not so good idea – to rewrite the block just to add a few columns to grid. The problems start when several modules are trying to rewrite the same grid – in the end only one can make it.
I would advise to use dispatches for such cases, as done in this module: https://github.com/sergoslav/BlockObserver. It helps to avoid such conflicts, several modules, and be sure that your module will work in any case.
Thanks!
In 1.7.0.2 adding the SKU column breaks pagination, but this fixes it: http://stackoverflow.com/questions/3485455/using-group-breaks-getselectcountsql-in-magento/4219386#4219386
How can I add the product size, color in magento admin sales order grid.. Please help me in this case..
Hi
As per you instruction I developed this page with order list items. I got the solution.. Thanks..
Also I need to know, how to display the color of the ordered list of items here..
Thanks..
I have 4 orders in the table. (I use Magento 1.7.0.2 stable) but it shows me on top of the table only 2 orders since the integration of the extension? What i need to do?
how to add color icon of status in sales order grid ??
I am not able to find the exactly where to code…
any solution here,need help??
it seems if two modules have that they will overwrite each other. how could we let both live together?
I’m trying to add a dropdown product attribute in the grid but its only showing the id of that option. How to show the text value of dropdown? Thats what I’m doing:
$this->addColumn(‘color’, array(
‘header’ =>Mage::helper(‘reports’)->__(‘Color’),
‘index’ =>’color’,
‘type’ => ‘options’
));
$collection->getSelect()->group(‘main_table.entity_id’)
has worked for
a:5:{i:0;s:64:”Item (Mage_Sales_Model_Order) with the same id “5? already exist”;i:1;s:5816:”#0 /home/
error.
I need to add category column in items list grid on order detail page (admin side) also need to make sortable / filterable columns there any one can help me
Hi!, this works with magento 1.6.x?
I was trying a lot of options, but I only can show the titles in the grid but the code for the collection brokes the grid.
thanks!
Thank you Sean Breedon!
NB: main_table.THIS_BIT will depend on your error.
So in my case I got
So I had to add
to
etc
Is there any documentation that will shed some light on all the possible attributes you can supply in that 2nd argument array? i.e. header, width, sortable, index, type, options, flying-elephants, etc.
Just posted a standalone module that adds Product SKUs to the orders grid. It uses GROUP_CONCAT but doesn’t break paging and supports filtering: http://colin.mollenhour.com/2012/03/03/view-and-filter-orders-grid-with-product-sku/
kamejoko…
// Get region name from id
$regionModel = Mage::getModel(‘directory/region’)->load($data[‘region_id’]);
$regionCode = $regionModel->getCode();
Hello,
I want to add a column Company. Kan someone help me with this ? I’ve got the error ‘Can’t retrieve entity config: sales/company’
My code:
$collection->join(‘company’, ‘order_address/company’, ‘billing_address_id’, null, ‘left’);
And:
$this->addColumn(‘company’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Billing company’),
‘index’ => ‘company’,
));
Hello!
This is my code for collect address customer order.
$collection->join(‘sales/order_address’, ‘order_id=parent_id’, array(‘street’=>’street’, ‘postcode’ =>’postcode’,’region_id’=>’region_id’, ‘city’=>’city’ ), null,’left’);
Everything work fine. But “region_id” only show “2”.
I want it show name “Alaska”.
How to get query show name of State?.
Thanks.
Can you help me to add field “company” to sales grid?
I manage to do this in Magento 1.3.1 inserting following code in: app/code/core/ Mage/Adminhtml/Block/Sales/Order/Grid.php
->joinAttribute(‘billing_company’, ‘order_address/company’, ‘billing_address_id’,null,’left’)
and
$this->addColumn(‘company’, array(
‘header’ => Mage::helper(‘sales’)->__(‘Firma’),
‘width’ => ‘150’,
‘index’ => ‘billing_company’,
));
Unfortunatelly this wont work in magento 1.6. The field “company” appears in grid but is not populated.
Can I use your tutorial for this?
Finally, I reallly know how to create a grid with your tutorial! Great post buddy!!!
Any idea how to show totals at the last row of the sales> order grid?
Just thought I would point this out because I hope I can save someone else the aggravation of troubleshooting it. I’m posting this here because this page came up while I was searching for the solution.
if you get the following error:
There has been an error processing your request
SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘status’ in where clause is ambiguous
Then you have forgotten to add:
‘filter_index’=>’main_table.status’
to $this->addColumn().
hth!
Thanks for such info , how can we get the product thumbnails in sales order grid ?
Thanks again
How I can Acess/display the “last_trans_id” form the payment. I want to check if the paymend had worked with for example paypal or Sofortüberweisung.de.
Also, I tried this:
But the column is appended to the very end instead of after the order increment ID. I’m running Magento 1.5.1
Hi Bruce,
I hope this gets through properly.
Hi Jaap,
Could you post your code?
Hi Bruce,
Thanks for your comment, it helped alot. However, we can’t use the filter for this extra column. I use your code to show the invoice ID and I can sort, but when I use the filter, it returns:
Column not found: 1054 Unknown column ‘invoice_id’ in ‘where clause’
Is there anything I should to to get the filter to work?
Thanks!
Hey guys,
None of the above is working for me.
But have come up with this thanks to code above.
Hello Guys,
I have created one magento admin module with product listing block. There is product list come as well as good but sorting and filtering is not working. Ajax loader is continue loading. i checked code but i didnt get what is issue. anybody help me ? or any advice ?
Thanks,
Manish
@Zionko
I had the same problem as yo when i tried to use the filter the result (a:5:{i:0;s:111:”SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘main_table.GROUP_CONCAT(sku SEPARATOR ‘ in ‘field list’”;i:1;s:5789:”#0)
I found a solution while mixin both Pawel Dubiel and Inchoo’s code:
In this case I add the product name to the orders grid.
Thanks for this post it helped me a lot!
Works like a champ in 1.5.01. I had an extension that was causing some problems.
Has anyone got this to work in 1.5.01? I have copied the code verbatim and everything seems to work fine, no errors in log file, the sku column shows up, output the SQL and that is OK – but no values are displayed in the SKU column. I can’t seem to find any reason why it wouldn’t work in 1.5.
Addition: Blogg substituted my chars… i mean
Pawels code works fine for me (thanks Pawel!).
Zionko check if you substitited the ‘, ’, “, ” completely with ‘ and “.
Zionko it’s working for me in 1.4.1
Pawel Dubiel
a:5:{i:0;s:111:”SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘main_table.GROUP_CONCAT(sku SEPARATOR ‘ in ‘field list'”;i:1;s:5789:”#0
dont work you code
If you want to merge skus and avoid ( a:5:{i:0;s:64:”Item (Mage_Sales_Model_Order) with the same id “5? already exist”;i:1;s:5816:”#0 /home/ ) use GROUP_CONCAT …
eg
protected function _prepareCollection()
{
$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection->getSelect()->joinLeft( array(‘item_table’=>’sales_flat_order_item’), ‘main_table.entity_id=item_table.order_id’, array( ‘sku’ =>’item_table.sku’ ) );
$collection->addExpressionFieldToSelect ( ‘sku’ , “GROUP_CONCAT(sku SEPARATOR ‘ , ‘)”);
$collection->getSelect()->group(‘entity_id’);
$this->setCollection($collection);
return parent::_prepareCollection();
}
Very useful and exactly what I was looking for—thanks guys!
Hi, I am overwriting the order module and making my own grid columns, But I want to make mandatory column so when user click on Search or Submit button in the right then it will show an alert that this field is require to search.
According to my code it will show a dropdown of all customers and I want to make mandatory this option field. Please help me to sort out this issue.
Thanks
Hi. This is the second time when I submit a comment but it is not approved, did my comments reach you? if they reach I have broken a rule or something? Please let me know.
On this topic I commented after Marcio first comment, saying what the problem is.
Have a good day.
If you have more than one item in your order it will produce a fatal error. Magento collections contain elements with unique identifiers, so it should be done in another way.
For example, use order item as main record and join order table to it. Also placing join statement inside of the block is not the following MVC concept of Magento. You need to move this join to a new resource model, or extend existing resource collection.
Also you need to override block only if it is not possible to perform a customization in other way (for example using layout xml), because each rewrite decreases upgrade-ability to a new Magento version and compatibly with other modules because you cannot be sure, that other community extension or module of your colleague will not rewrite the same model.
Also you can pre-generate this column value by using of addVirtualGridColumn in order resource model (GROUP_CONCAT may help you with merging more than one sku for example).
See this article with example of adding new column to order grid via layout:
http://www.ecomdev.org/2010/07/27/adding-order-attribute-to-orders-grid-in-magento-1-4-1.html
I always want to write you – nice help articles, but please can you write it for dummies like me? E.g. does this mean i should modify core files (i hope not). If not, then what file/folder and where i have to create and what to put there.
In Order with multiples products error ocurred
a:5:{i:0;s:64:”Item (Mage_Sales_Model_Order) with the same id “5” already exist”;i:1;s:5816:”#0 /home/