2 
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.

Improve this question

3 Answers 3

Reset to default
5

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'
));
Improve this answer
2
  • This worked well, thank you. A bit off topic but do you have a resource which explains why join left is good in that scenario?
    – Steve Perry
    Nov 1, 2014 at 11:00
  • A standard join will match rows only where content exists in both the left (existing), and right (new) tables. If there is no content in one of the tables for a particular row then that row is not included in the result set. With a left join data from all rows in the left table are maintained even if no equivalent row exists in the right table. For these rows the right table is just populated with null values.
    – Jonathan Hussey
    Nov 1, 2014 at 13:52
1

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..

Improve this answer
2
  • That worked but it didn't return all rows in the grid. I'll add my updated version but marked this as the correct answer.
    – Steve Perry
    Oct 31, 2014 at 12:12
  • edited the answer...
    – raduparvan
    Oct 31, 2014 at 12:29
0

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.

Improve this answer
1
  • This won't actually work correctly as each order can have 2 associated addresses (shipping and billing) - this will only associate one as you are using a standard join() but don't define which address to use.
    – Jonathan Hussey
    Oct 31, 2014 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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