Adding “Customer Group” column to sales grid in Magento

add-new-column

Surprisingly Magento admin area does not have a “customer group” column under the “Sales > Orders” grid, or under the “Sales > Orders > Create New Order” grid (which is a grid of customers). Why would we need this in a first place? Well, maybe you want to be able to list/filter all the orders in a grid which are purchased by customers from lets say “Dealers” group.
Maybe you want to be able to filter the “Sales > Orders > Create New Order” grid by customer group prior to the actual order creation just to see the total of “Dealers” group customers?

By default “Sales > Orders” grid uses the “sales_flat_order_grid” table for rendering. This table is basically a redundant subset of “sales_flat_order” table. Surprisingly “sales_flat_order” table has the “customer_group_id” column in it, while “sales_flat_order_grid” table does not. So our first step is to add the “customer_group_id” column to the “sales_flat_order_grid” table.

We do so by simple upgrade script that executes the code similar to this:

$installer->getConnection()
        ->addColumn($installer->getTable('sales/order_grid'), 'customer_group_id', array(
            'TYPE' => Varien_Db_Ddl_Table::TYPE_SMALLINT,
            'NULLABLE'  => false,
            'DEFAULT'   => '0',
            'COMMENT' => 'Customer Group'
        ));

If this install/upgrade script executed successfuly we should see the new “customer_group_id” column in the “sales_flat_order_grid” table. Please note that Magento will automatically populate this column from the data in the “sales_flat_order_grid.customer_group_id” once the new orders get saved.

After that we will rewrite the Mage_Adminhtml_Block_Sales_Order_Grid and Mage_Adminhtml_Block_Sales_Order_Create_Customer_Grid but change ony the minimum neceserrr for our new “Customer Group” column to appear.

We do so by adding the proper entries under the config.xml

<global>
<blocks>
    <adminhtml>
        <rewrite>
            <sales_order_create_customer_grid>Mycompany_Myextension_Block_Adminhtml_Sales_Order_Create_Customer_Grid</sales_order_create_customer_grid>
            <sales_order_grid>Mycompany_Myextension_Block_Adminhtml_Sales_Order_Grid</sales_order_grid>
        </rewrite>
    </adminhtml>
</blocks>
</global>

Finally we add the rewrite blocks.

<?php
 
class Mycompany_Myextension_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid 
{
    protected function _prepareColumns()
    {
        $groups = Mage::getResourceModel('customer/group_collection')
            ->addFieldToFilter('customer_group_id', array('gt'=> 0))
            ->load()
            ->toOptionHash();
 
        $this->addColumn('customer_group_id', array(
            'header'    =>  Mage::helper('customer')->__('Customer Group'),
            'width'     =>  '100',
            'index'     =>  'customer_group_id',
            'type'      =>  'options',
            'options'   =>  $groups,
        ));
 
        $this->addColumnsOrder('customer_group_id', 'shipping_name');
 
        return parent::_prepareColumns();
    }
}
<?php
 
class Mycompany_Myextension_Block_Adminhtml_Sales_Order_Create_Customer_Grid extends Mage_Adminhtml_Block_Sales_Order_Create_Customer_Grid
{
    protected function _prepareColumns()
    {
        $groups = Mage::getResourceModel('customer/group_collection')
            ->addFieldToFilter('customer_group_id', array('gt'=> 0))
            ->load()
            ->toOptionHash();
 
        $this->addColumn('group', array(
            'header'    =>  Mage::helper('customer')->__('Group'),
            'width'     =>  '100',
            'index'     =>  'group_id',
            'type'      =>  'options',
            'options'   =>  $groups,
        ));
 
        $this->addColumnsOrder('group', 'email');
 
        return parent::_prepareColumns();
    }
}

And that’s it. Now you should have the “Customer Group” columns on your “Sales > Orders” and “Sales > Orders > Create New Order” grids.


9 comments

  1. I have some similar problem. I added customer group on billing checkout. The customer gets registered with the selected group, the problem is on first order the group is not showing in the order. its important because the customer group has tex class attached to it.

  2. If you want to update existing grid customer ids use this SQL

    UPDATE sales_flat_order o, sales_flat_order_grid g
    SET g.customer_group_id = o.customer_group_id
    WHERE o.entity_id=g.entity_id
  3. You can do it using observers to avoid module conflicts.

    I’ve created an Observer.php model as follows:

    class Mycompany_Myextension_Model_Observer {
    	public function addCustomerGroupColumn(Varien_Event_Observer $observer){
    		if($observer->getBlock()->getType() == 'adminhtml/sales_order_grid'){
    			$block = $observer->getEvent()->getBlock();			
    			$sales_order_grid_block = $observer->getBlock();
    
    			$groups = Mage::getResourceModel('customer/group_collection')
    			->addFieldToFilter('customer_group_id', array('gt' => 0))
    			->load()
    			->toOptionHash();
    
    			$block->addColumn('customer_group_id', array(
    				'header' 	=> Mage::helper('customer')->__('Customer Group'),
    				'width'		=> '100',
    				'index'		=> 'customer_group_id',
    				'type'		=> 'options',
    				'options'	=> $groups,
    				));
    
    			$block->addColumnsOrder('customer_group_id', 'shipping_name');
    		}
    	}
    }

    and add an observer on the config.xml:

    <events>
          <adminhtml_block_html_before>
            <observers>
              <add_customer_group_column>
                <type>model</type>
                <class>customizations/observer</class>
                <method>addCustomerGroupColumn</method>
              </add_customer_group_column>
            </observers>
          </adminhtml_block_html_before>
        </events>

    I still didn’t test if new orders are getting populated, but this way the columns is shown with no conflicts.

  4. Hello Branko,

    I implemented your code, the Customer Group column shows up fine in both Sales_Order_Grid and Sales_Order_Create_Customer_Grid.

    However, the Customer Group column in Sales_Order_Grid is not getting populated.

    I flushed Magento cache, logged out and back in but it’s still showing up empty.

    Can you help me out?
    Thank you very much for the script.

  5. Curious, why in most of the articles, including this one you always propose extending core classes instead of using event-observer? Surely I do not like this way of extending admin grids as if every module do it managing the conflicts is simply a nightmare…

  6. OK i found out what happened …
    So if others pals have the problem just flush var/cache dir, cause zend generate cache of table structure even if your magento cache is disabled

  7. Hey Branko,

    Thanks for the nice tuts.
    Got some problem making it work.
    It seems that magento doesn’t populate the newly added column customer_group_id for the new orders …

    i quote you -> “Please note that Magento will automatically populate this column from the data in the “sales_flat_order_grid.customer_group_id” once the new orders get saved.”

    I am on a Magento EE 1.8 -> equivalent to a 1.4.2 CE

    Maybe you have a clue for me ?
    Thanks in advance and keep up the good work : )

  8. Hello Inchoo.net

    Thanks for Nice tutorials in support for Magento Developers.
    Tutorials are excellent to use and improve the skills.

    I have a requirement but no where I found the solution, so I’m posting it here, Please support me…

    My requirement, I want to allow customers with duplicate E-mail Id’s.
    Means same email id can be used by more customers. I’m using a custom module for login with user name.

    Its a very urgent requirement and I’m a very new bee to zend and magento as well, I believe I can expect this article soon.

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