Magento 1.8 – Add Columns from Different SQL Tables to Sales Grid

magento-1.8sales-order

Using Magento 1.8.1

I've added custom columns to the Sales Order Grid. The collection is as follows in my app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php:

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $this->setCollection($collection);
    $collection->getSelect()->join('sales_flat_order', 'main_table.entity_id = sales_flat_order.entity_id', array('base_shipping_amount', 'base_subtotal', 'base_tax_amount'));
    return parent::_prepareCollection();
}

Now that is working perfectly fine, I want to append a new column from a different table, the sales_flat_order_address table, but I'm having trouble. How do I effectively add in a second collection into the _prepareCollection method? Basically I want to add in:

$collection->getSelect()->join('sales_flat_order_address', 'main_table.entity_id = sales_flat_order_address.entity_id', array('country_id'));

Into the first collection so that I can use country_id in the columns with the other data.

Best Answer

The flat orders table contains the billing and shipping address id's so you should join against these instead of using entity_id and filtering by shipping or billing. Also not all orders will have an associated shipping address (i.e downloadable products) so you should use a left join to ensure you don't remove these orders from the grid. Finally, it's best practice to use the proper getTableName() method to pull the table names and also alias table names. Essentially you end up with:

$collection = Mage::getResourceModel($this->_getCollectionClass());
$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_country' => 'country_id')
);

$select->joinLeft(
    array('shipping' => $resource->getTableName('sales/order_address')),
    'order.shipping_address_id = shipping.entity_id',
    array('shipping_country' => 'country_id')
);

$this->setCollection($collection);
return parent::_prepareCollection();

You can then use billing_country and shipping_country to add the columns in your _prepareColumns() method. Adding filter_index ensures the correct table and column is referenced when sorting and searching:

$this->addColumn('billing_country', array(
    'header' => Mage::helper('sales')->__('Billing Country'),
    'index' => 'billing_country',
    'filter_index' => 'billing.country_id'
));

$this->addColumn('shipping_country', array(
    'header' => Mage::helper('sales')->__('Ship to Country'),
    'index' => 'shipping_country',
    'filter_index' => 'shipping.country_id'
));
Related Topic