Magento – add column to sales/orders grid

columngridmagento-1.9order-grid

I'm trying to add a column to the sales/order grid in the backend. I've found some wonderfull tutorials, but I can't get it to work.

This is the tutorial I'm following..
http://www.atwix.com/magento/customize-orders-grid/

Though I want to display the city of the customer that placed the order.

The column is shown in the grid, but there is no content. I suspect that the query to add the sales/order table is incorrect, but I'm not sure what goes wrong here.

My code..

class Test_Manager_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);
}

protected function _getCollectionClass()
{
    return 'sales/order_grid_collection';
}
protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->getSelect()->joinLeft('sales_flat_order_address', 'main_table.entity_id = sales_flat_order_address.parent_id',array('city'));

    $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('city', array(
        'header'    => Mage::helper('sales')->__('City'),
        'index'     => 'city',
        '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));
}

}

—EDIT—
I've tried Amit's solution, but that didn't fill the column either.

I've also tried the method from the article..

In _prepareCollection 

$collection->getSelect()->join(array('address_billing' =>
$collection->getTable("sales/order_address"))

,
'main_table.entity_id = address_billing.parent_id AND
address_billing.address_type = "billing"',array('address_billing.city as billing_city'));

in _prepareColumns()

  $this->addColumn('city', array(
        'header' => Mage::helper('sales')->__(' Billing City'),
        'index' => 'billing_city',
        'filter_index' => 'address_billing.city',
        ));

This also doesn't fill the column. Also when I try to order the column, I get the error column not found 1054, unknown column 'address_billing.city' in 'order clause'. It seems like the field isn't added to the collection, but the query seems right to me.

I'm not sure what else could be wrong here.

Best Answer

This is a duplicate of the question Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue, here I explained how to add a column to the sales/order grid the right way (without file copy or a rewrite).

The sales/order grid uses the table sales_flat_order_grid to display its information, this is done to list all orders without adding joins and speeding-up the loading. In order to add a column to the grid you would need to add a field to this table and update the information in that field when an order is created/updated. Below the steps needed to add this to the grid:

I called the extension Example_SalesGrid, but you can change it to your own needs.

Let's start by creating the module init xml in /app/etc/modules/Example_SalesGrid.xml:

<?xml version="1.0" encoding="UTF-8"?>
<!--
 Module bootstrap file
-->
<config>
    <modules>
        <Example_SalesGrid>
            <active>true</active>
            <codePool>community</codePool>
            <depends>
                <Mage_Sales />
            </depends>
        </Example_SalesGrid>
    </modules>
</config>

Next we create our module config xml in /app/code/community/Example/SalesGrid/etc/config.xml:

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Example_SalesGrid>
            <version>0.1.0</version> <!-- define version for sql upgrade -->
        </Example_SalesGrid>
    </modules>
    <global>
        <models>
            <example_salesgrid>
                <class>Example_SalesGrid_Model</class>
            </example_salesgrid>
        </models>
        <blocks>
            <example_salesgrid>
                <class>Example_SalesGrid_Block</class>
            </example_salesgrid>
        </blocks>
        <events>
            <!-- Add observer configuration -->
            <sales_order_resource_init_virtual_grid_columns>
                <observers>
                    <example_salesgrid>
                        <model>example_salesgrid/observer</model>
                        <method>addColumnToResource</method>
                    </example_salesgrid>
                </observers>
            </sales_order_resource_init_virtual_grid_columns>
        </events>
        <resources>
            <!-- initialize sql upgrade setup -->
            <example_salesgrid_setup>
                <setup>
                    <module>Example_SalesGrid</module>
                    <class>Mage_Sales_Model_Mysql4_Setup</class>
                </setup>
            </example_salesgrid_setup>
        </resources>
    </global>
    <adminhtml>
        <layout>
            <!-- layout upgrade configuration -->
            <updates>
                <example_salesgrid>
                    <file>example/salesgrid.xml</file>
                </example_salesgrid>
            </updates>
        </layout>
    </adminhtml>
</config>

Now we create the sql upgrade script in /app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php:

<?php
/**
 * Setup scripts, add new column and fulfills
 * its values to existing rows
 *
 */
$this->startSetup();
// Add column to grid table

$this->getConnection()->addColumn(
    $this->getTable('sales/order_grid'),
    'customer_city',
    'varchar(255) DEFAULT NULL'
);

// Add key to table for this field,
// it will improve the speed of searching & sorting by the field
$this->getConnection()->addKey(
    $this->getTable('sales/order_grid'),
    'customer_city',
    'customer_city'
);

// Now you need to fullfill existing rows with data from address table

$select = $this->getConnection()->select();
$select->join(
    array('address'=>$this->getTable('sales/order_address')),
    $this->getConnection()->quoteInto(
        'address.parent_id = order_grid.entity_id AND address.address_type = ?',
        Mage_Sales_Model_Quote_Address::TYPE_BILLING
    ),
    array('customer_city' => 'city')
);
$this->getConnection()->query(
    $select->crossUpdateFromSelect(
        array('order_grid' => $this->getTable('sales/order_grid'))
    )
);

$this->endSetup();

Next we create the layout update file in /app/design/adminhtml/default/default/layout/example/salesgrid.xml:

<?xml version="1.0"?>
<layout>
    <!-- main layout definition that adds the column -->
    <add_order_grid_column_handle>
        <reference name="sales_order.grid">
            <action method="addColumnAfter">
                <columnId>customer_city</columnId>
                <arguments module="sales" translate="header">
                    <header>Customer City</header>
                    <index>customer_city</index>
                    <type>text</type>
                    <width>200</width>
                </arguments>
                <after>shipping_name</after>
            </action>
        </reference>
    </add_order_grid_column_handle>
    <!-- order grid action -->
    <adminhtml_sales_order_grid>
        <!-- apply the layout handle defined above -->
        <update handle="add_order_grid_column_handle" />
    </adminhtml_sales_order_grid>
    <!-- order grid view action -->
    <adminhtml_sales_order_index>
        <!-- apply the layout handle defined above -->
        <update handle="add_order_grid_column_handle" />
    </adminhtml_sales_order_index>
</layout>

The last file needed is needed to update the data from a table other than sales/order (sales_flat_order). All fields in sales/order_grid matching the column name from sales/order is automatically updated in the sales/order_grid table.

/app/code/community/Example/SalesGrid/Model/Observer.php:

<?php
/**
 * Event observer model
 *
 *
 */
class Example_SalesGrid_Model_Observer {

    public function addColumnToResource(Varien_Event_Observer $observer) {
        // Only needed if you use a table other than sales/order (sales_flat_order)

        $resource = $observer->getEvent()->getResource();
        $resource->addVirtualGridColumn(
            'customer_city',
            'sales/order_address',
            array('billing_address_id' => 'entity_id'),
            'city'
        );
    }
}

This code is based on the example from http://www.ecomdev.org/2010/07/27/adding-order-attribute-to-orders-grid-in-magento-1-4-1.html

Hope the example above solves your problem.

Related Topic