Sales Grid, adding columns from different SQL tables

Question

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.

Answer 1

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'
));

Answer 2

You should do something like this:

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

    $collection->getSelect()->join('sales_flat_order', 'main_table.entity_id = sales_flat_order.entity_id', array('base_shipping_amount', 'base_subtotal', 'base_tax_amount'));
    $collection->getSelect()->join("sales_flat_order_address", "main_table.entity_id = sales_flat_order_address.entity_id, array('country_id'));

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

You just need to replace shipping with billing if you need country_id for billing address..

Answer 3

Radu's answer worked but it didn't return all rows. I updated the answer to:

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

    $this->setCollection($collection);

    return parent::_prepareCollection();
}

I.E. Removed AND sales_flat_order_address.address_type LIKE 'shipping'from the second collection. This now returns all rows as expected.