add column to sales/orders grid

Question

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.

Answer 1

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.

Answer 2

Try This way, Its works fine for me.i Assume that you will create your own module for this task rather than editing the core files. Add this line to your _prepareCollection function in you grid.php file

$collection = Mage::getResourceModel('sales/order_collection')
        ->join(array('a' => 'sales/order_address'), 'main_table.entity_id = a.parent_id AND a.address_type != \'billing\'', array(
            'city'       => 'city',
            'country_id' => 'country_id'
        ))

Add this lines to function _prepareColumns in grid.php file

$this->addColumn('city', array(
        'header' => $helper->__('City'),
        'index'  => 'city'
    ));

hope it will work.

Answer 3

Just check out Enhanced Admin Grids. A so powerful and free extension from jayelkaake