How to extend Magento Order Grid?

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:

<blocks>
<adminhtml>
<rewrite>
<sales_order_grid>Inchoo_Test_Block_Adminhtml_Order_Grid</sales_order_grid>
</rewrite>
</adminhtml>
</blocks>

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.

You made it all the way down here so you must have enjoyed this post! You may also like:

Magento Grid Serializer for Ajax Grids Damir Korpar
Damir Korpar, | 10

Magento Grid Serializer for Ajax Grids

Show product thumbnail in grids Petar Sambolek
Petar Sambolek, | 14

Show product thumbnail in grids

Filter order grid by multiple ID’s Petar Sambolek
Petar Sambolek, | 14

Filter order grid by multiple ID’s

63 comments

  1. 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?

  2. 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’);

  3. 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

  4. 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 :

    protected function _prepareCollection()
    {
            $store = $this->_getStore();
            $collection = Mage::getModel('catalog/product')->getCollection()
                ->addAttributeToSelect('sku')
                ->addAttributeToSelect('name')
                ->addAttributeToSelect('attribute_set_id')
                ->addAttributeToSelect('type_id')
    			->addAttributeToSelect('brand');
    
    $collection->joinTable( 'cataloginventory/stock_item', 'product_id=entity_id', array("stock_status" => "is_in_stock", "backorders" => "backorders") )
    		->addAttributeToSelect('stock_status');
    
    if (Mage::helper('catalog')->isModuleEnabled('Mage_CatalogInventory')) {
                $collection->joinField('qty',                
    				'cataloginventory/stock_item',
                    'qty',
                    'product_id=entity_id',
                    '{{table}}.stock_id=1',
                    'left');			
            }

    in _prepareColumns :

    protected function _prepareColumns()
        {
            .........(some code)
    
    $this->addColumn('backorders',
                array(
                    'header'=> 'Backorders',
                    'width' => '60px',
                    'index' => 'backorders',
    				'type'  => 'options',
                    'options' => array('0'=>'No Backorders','1'=>'Allow Qty Below 0','2'=>'Allow Qty Below 0 and Notify Customer'),
            ));

    Thanks

  5. “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

  6. 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!

  7. How can I add the product size, color in magento admin sales order grid.. Please help me in this case..

  8. 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..

  9. 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?

  10. 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??

  11. it seems if two modules have that they will overwrite each other. how could we let both live together?

  12. 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’
    ));

  13. $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.

  14. 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

  15. 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!

  16. Thank you Sean Breedon!

    NB: main_table.THIS_BIT will depend on your error.
    So in my case I got

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

    So I had to add

    'filter_index' => 'main_table.created_at'

    to

    $this->addColumn('created_at', array(

    etc

  17. 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.

  18. kamejoko…

    // Get region name from id
    $regionModel = Mage::getModel(‘directory/region’)->load($data[‘region_id’]);
    $regionCode = $regionModel->getCode();

  19. 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’,
    ));

  20. 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.

  21. 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?

  22. 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!

  23. 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.

  24. Also, I tried this:

    $this->addColumnAfter('invoice_id', array(
    			'header' => Mage::helper('sales')->__('Invoice #'),
                'index' => 'invoice_id',
                'type' => 'text',
                'width' => '100px'),
    			'real_order_id');

    But the column is appended to the very end instead of after the order increment ID. I’m running Magento 1.5.1

  25. Hi Bruce,

    I hope this gets through properly.

    class My_Module_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid
    {
    	protected function _prepareColumns()
    	{
    		parent::_prepareColumns();
    		unset($this->_columns['store_id']);
    		unset($this->_columns['base_grand_total']);
    
    		$this->addColumn('invoice_id', array(
    			'header' => Mage::helper('sales')->__('Invoice #'),
                'index' => 'invoice_id',
                'type' => 'text',
                'width' => '100px'));
    		return $this;
    	}
    
    	protected function _prepareCollection()
        {
    		$collection = Mage::getResourceModel($this->_getCollectionClass());
    		$collection->getSelect()->join('sales_flat_invoice', 'sales_flat_invoice.order_id=main_table.entity_id', array('invoice_id'=>new Zend_Db_Expr('group_concat(`sales_flat_invoice`.increment_id SEPARATOR ",")')))->group('main_table.entity_id');
            $this->setCollection($collection);
    		return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
        }
    }
  26. 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!

  27. Hey guys,

    None of the above is working for me.

    But have come up with this thanks to code above.

        protected function _prepareCollection()
        {
        	$collection = Mage::getResourceModel($this->_getCollectionClass());
    
            $collection->getSelect()->join(
            	'sales_flat_order_item',
                '`sales_flat_order_item`.order_id = `main_table`.entity_id',
                array(
    	            'sku' 	=> new Zend_Db_Expr('group_concat(`sales_flat_order_item`.sku SEPARATOR ",")'),
    	            'name' => new Zend_Db_Expr('group_concat(`sales_flat_order_item`.name SEPARATOR ",")'),
                )
            );
    
            $collection->getSelect()->group('main_table.entity_id');
            $this->setCollection($collection);
    
            return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
        }
  28. 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

  29. @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:

    $collection->join('sales/order_item', 'order_id=entity_id', array('name'=>'name', 'sku' =>'sku', 'qty_ordered'=>'qty_ordered' ), null,'left')
                        ->addExpressionFieldToSelect ( 'name' , "GROUP_CONCAT(name SEPARATOR ' | ')")
                        ->getSelect()->group('entity_id');

    In this case I add the product name to the orders grid.

    Thanks for this post it helped me a lot!

  30. 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.

  31. Pawels code works fine for me (thanks Pawel!).
    Zionko check if you substitited the ‘, ’, “, ” completely with ‘ and “.

  32. 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

  33. 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();
    }

  34. 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.

    		$this->addColumn('username', array(
    			'header'=> Mage::helper('sales')->__('Customer Username'),
    			'width' => '80px',
    			'index' => 'customer_email',
    			'type'  => 'options',
    			'options' => $customer_email,
    			'sortable'  => false,
    		));

    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

  35. 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.

  36. 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

  37. 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.

  38. 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/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.

Tell us about your project

Drop us a line. We'd love to know more about your project.