Magento - How to add Ship to information to order grid in Magento 1.5.1.0

Question

I found a great way to put the customer email address on the Magento Admin Sales Order grid via this Q&A (http://stackoverflow.com/questions/6416864/how-to-add-customer-email-to-order-grid-in-magento-1-4/6906254#6906254) by Ben Incani and it works great.

My question is: Using this method, how can I add the Ship to information (name, address, city, state, zip)?

I've tried doing two versions (that sort of work) but do not work fully so I am a bit stuck...

This is the code that works for customer emails:

$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),'sfo.entity_id=main_table.entity_id',array('sfo.customer_email', 'sfo.shipping_description'));

Now, when trying to go into the database table that has the Ship to information I tried this:

$collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),'sfoa.parent_id=sfo.entity_id',array('sfoa.postcode'));

This returns an error log with the message:

a:5:{i:0;s:68:"Item (Mage_Sales_Model_Order) with the same id "10860" already exist";i:1;s:5104:"#0

Trying this code (which most closely follows the original customer email code):

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

yields a grid that I can view with a populated column. However, the values in the column are NOT the correct Postal Codes - I can't even figure out what values it is pulling???

I guess one of my issues is that I don't exactly know what main_table.entity_id refers to (although I have a guess).

Anyway, I feel that I'm close and if someone can answer how I successfully get the information with this method, I'd be eternally grateful! Can anyone

Answer 1

The reason about that error "Mage_Sales_Model_Order) with the same id "10860" already exist" it's because sales_flat_order_address creates two records for every order, one for shipping address and another one for billing address, if you add this filter on the _prepareCollection() you can make it work

$collection->getSelect()->where("address_type='shipping'");

Answer 2

I have a similar requirement some time back, where I need to add customer email and shipping region to the sales order grid.
For achieving the requirement I have rewritten class Mage_Adminhtml_Block_Sales_Order_Grid as below in my custom module.

class Custom_OrderGrid_Block_Adminhtml_Sales_Order_Grid extends Mage_Adminhtml_Block_Sales_Order_Grid {

    protected function _getCollectionClass() {
        return 'sales/order_collection';
    }

    protected function _prepareCollection() {
        $collection = Mage::getResourceModel($this->_getCollectionClass());

        $collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'), 'main_table.entity_id = sfog.entity_id', array('sfog.shipping_name', 'sfog.billing_name'));

        $collection->getSelect()->joinLeft(array('sfo' => 'sales_flat_order'), 'sfo.entity_id=main_table.entity_id', array('sfo.customer_email', 'sfo.increment_id', 'sfo.store_id', 'sfo.created_at', 'sfo.status', 'sfo.base_grand_total', 'sfo.grand_total'));

        $collection->getSelect()->joinLeft(array('sfoa' => 'sales_flat_order_address'), 'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"', array('sfoa.region'));

        $this->setCollection($collection);

        return Mage_Adminhtml_Block_Widget_Grid::_prepareCollection();
    }

    protected function _prepareColumns() {

        $this->addColumn('real_order_id', array(
            'header' => Mage::helper('sales')->__('Order #'),
            'width' => '80px',
            'type' => 'text',
            'index' => 'increment_id',
            'filter_index' => 'sfo.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' => 'sfo.store_id'
            ));
        }

        $this->addColumn('created_at', array(
            'header' => Mage::helper('sales')->__('Purchased On'),
            'index' => 'created_at',
            'type' => 'datetime',
            'width' => '100px',
            'filter_index' => 'sfo.created_at'
        ));

        $this->addColumn('billing_name', array(
            'header' => Mage::helper('sales')->__('Bill to Name'),
            'index' => 'billing_name',
            'filter_index' => 'sfog.billing_name'
        ));

        $this->addColumn('shipping_name', array(
            'header' => Mage::helper('sales')->__('Ship to Name'),
            'index' => 'shipping_name',
            'filter_index' => 'sfog.shipping_name'
        ));

        $this->addColumn('customer_email', array(
            'header' => Mage::helper('sales')->__('Customer Email'),
            'index' => 'customer_email',
            'filter_index' => 'sfo.customer_email',
            'width' => '50px',
        ));

        $this->addColumn('region', array(
            'header' => Mage::helper('sales')->__('Shipping State'),
            'index' => 'region',
            'filter_index' => 'sfoa.region',
            'width' => '50px',
        ));

        $this->addColumn('base_grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Base)'),
            'index' => 'base_grand_total',
            'type' => 'currency',
            'currency' => 'base_currency_code',
            'filter_index' => 'sfo.base_grand_total'
        ));

        $this->addColumn('grand_total', array(
            'header' => Mage::helper('sales')->__('G.T. (Purchased)'),
            'index' => 'grand_total',
            'type' => 'currency',
            'currency' => 'order_currency_code',
            'filter_index' => 'sfo.grand_total'
        ));

        $this->addColumn('status', array(
            'header' => Mage::helper('sales')->__('Status'),
            'index' => 'status',
            'type' => 'options',
            'width' => '70px',
            'filter_index' => 'sfo.status',
            'options' => Mage::getSingleton('sales/order_config')->getStatuses(),
        ));

        if (Mage::getSingleton('admin/session')->isAllowed('sales/order/actions/view')) {
            $this->addColumn('action', array(
                'header' => Mage::helper('sales')->__('Action'),
                'width' => '50px',
                'type' => 'action',
                'getter' => 'getId',
                'actions' => array(
                    array(
                        'caption' => Mage::helper('sales')->__('View'),
                        'url' => array('base' => '*/sales_order/view'),
                        'field' => 'order_id'
                    )
                ),
                'filter' => false,
                'sortable' => false,
                'index' => 'stores',
                'is_system' => true,
            ));
        }
        $this->addRssList('rss/order/new', Mage::helper('sales')->__('New Order RSS'));

        $this->addExportType('*/*/exportCsv', Mage::helper('sales')->__('CSV'));
        $this->addExportType('*/*/exportExcel', Mage::helper('sales')->__('Excel XML'));

        return Mage_Adminhtml_Block_Widget_Grid::_prepareColumns();
    }

}

I hope this will help others.

Answer 3

Revised Answer (Due to two problematic errors)

I'm rewriting this answer in a more friendly, step by step way to hopefully help someone else out.

Note: This is for a local change at app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php

A. In order to get everything working correclty, you first need to change the _getCollectionClass() from this:

protected function _getCollectionClass()
{
    return 'sales/order_grid_collection';
}

to this:

protected function _getCollectionClass()
{
    //return 'sales/order_grid_collection';
    return 'sales/order_collection';
}

I ran into a major headache when doing this which was this:

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

This happens when you try to filter/search the grid by the Purchase On column.

In order to avoid/fix this error, you need to change the collectioin AND add the following to _prepareCollection() AND add a filter_index to each of the columns added to the grid.

You'll also run into another headache

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'billing_name' in 'where clause'

IF in the _prepareCollection() you try to dynamically create the columns of the Billing Name or Shipping Name which looks like this:

$collection->getSelect()->columns(new Zend_Db_Expr("CONCAT(s2.firstname, ' ',s2.lastname) AS billing_name"));
    $collection->getSelect()->columns(new Zend_Db_Expr("CONCAT(s1.firstname, ' ',s1.lastname) AS shipping_name"));

When this is done, there is no real easy way (that I came across to fix this), if it all.

In order to avoid these headaches (after you change the _getCollectionClass() to the above) do the following:

B. Change the _prepareCollection() to this:

 protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());

    $collection->getSelect()->joinLeft(array('sfog' => 'sales_flat_order_grid'),'main_table.entity_id = sfog.entity_id',array('sfog.shipping_name','sfog.billing_name'));
$collection->getSelect()->joinLeft(array('sfo'=>'sales_flat_order'),'sfo.entity_id=main_table.entity_id',array('sfo.customer_email','sfo.weight','sfo.discount_description','sfo.increment_id','sfo.store_id','sfo.created_at','sfo.status','sfo.base_grand_total','sfo.grand_total'));
    $collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),'main_table.entity_id = sfoa.parent_id AND sfoa.address_type="shipping"',array('sfoa.street','sfoa.city','sfoa.region','sfoa.postcode','sfoa.telephone'));


    $this->setCollection($collection);

    return parent::_prepareCollection();
}

C. Then for the existing columns in _prepareColumns() add a filer_index to each:

Example:

$this->addColumn('billing_name', array(
        'header' => Mage::helper('sales')->__('Bill to Name'),
        'index' => 'billing_name',
        'filter_index' => 'sfog.billing_name',
    ));

D. Then add the columns you want to add like this:

Example:

$this->addColumn('customer_email', array(
            'header' => Mage::helper('sales')->__('Customer Email'),
            'index' => 'customer_email',
            'filter_index' => 'sfo.customer_email',
            'width' => '50px',
    ));