1

I trying to add customer's shipping address detail in Admin > Sales > Order grid with following code

in protected function _prepareCollection()

$collection->getSelect()->join(array('Shipping'=>'sales_flat_order_address'),
'main_table.entity_id=Shipping.parent_id 
and Shipping.address_type="Shipping"',array('city','street','telephone','postcode','region'));

in protected function _prepareColumns()

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

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

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

$this->addColumn('city', array(
        'header' => Mage::helper('sales')->__(' Ship to City'),
        'index' => 'city',
        'filter_index' => 'city',
    ));

but showing blank columns

3

The sales_flat_order_address address table can have 2 rows for each order so you can't directly join this table with the sales_flat_order_grid table, you need to join against the sales_flat_order table first and then join the sales_flat_order_address table using the shipping_address_id or the billing_address_id:

$select = $collection->getSelect();
$resource = Mage::getSingleton('core/resource');
$select->join(
    array('order' => $resource->getTableName('sales/order')),
    'main_table.entity_id = order.entity_id'
);

$select->join(
    array('billing' => $resource->getTableName('sales/order_address')),
    'order.billing_address_id = billing.entity_id',
    array('billing_city' => 'city', 'billing_street' => 'street', 'billing_telephone' => 'telephone', 'billing_postcode' => 'postcode', 'billing_region' => 'region')
);

$select->joinLeft(
    array('shipping' => $resource->getTableName('sales/order_address')),
    'order.shipping_address_id = shipping.entity_id',
    array('shipping_city' => 'city', 'shipping_street' => 'street', 'shipping_telephone' => 'telephone', 'shipping_postcode' => 'postcode', 'shipping_region' => 'region')
);

And then add the columns as follows:

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

$this->addColumn('shipping_city', array(
    'header' => Mage::helper('sales')->__('Shipping City'),
    'index' => 'shipping_city',
    'filter_index' => 'shipping.city'
));

// add whatever extra columns you need from those selected
|improve this answer|||||
  • how can i add telephone number on sales order grid? – Gem Apr 26 '17 at 5:19
1

It's not recommended to join the grid table with the sales_flat_order table, this will cause higher sql loads when you have a large amount of orsers. It can also cause locking problems which can result in problems with saving new orders. Check my answer at Add Column to a grid (observer) - Column ‘store_id’ in where clause is ambiguous issue on how to add custom fields to the grid table and adding these to the grid.

|improve this answer|||||
1

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 you.
You can check my code and try to find what is missing in yours.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.