How to add a custom customer attribute to the sales grid

Question

I have a custom customer attribute named "sage_account_number" and I would like to add this to the sales grid.

Could someone advise how I would add this?

I have tried

$collection->getSelect()->join('customer_entity','main_table.customer_id = customer_entity.entity_id', array('sage_account_number' => 'sage_account_number'));

However this doesnt seem to work

Answer 1

Is the Sage Account Number attribute also saved against the order? If so you can achieve it like so:

Extend the following class within a custom extension Mage_Adminhtml_Block_Sales_Order_Grid and add the following.

protected function _prepareColumns()
{
    $this->addColumn('sage_account_number', array(
        'header'    => $this->__('Sage Acc Number'),
        'index'     => 'sage_account_number',
        'width'     => '100px'
    ));
    $this->addColumnsOrder('sage_account_number', 'shipping_name');

    return parent::_prepareColumns();
}

If not, you could write an observer to save this to the order after creating a new order attribute to match it.

This make sense?

Answer 2

You can create an extension and rewrite Mage_Adminhtml_Block_Sales_Order_Grid class and make changes described below or try to modify Order Grid block and collection via observer, but the simplest way is redefine the file:

Magento_root/app/code/core/Mage/Adminhtml/Block/Sales/Order/Grid.php

with the following file (copy of file above):

Magento_root/app/code/local/Mage/Adminhtml/Block/Sales/Order/Grid.php

and add the following code:

1) in _prepareCollection():

after

$collection = Mage::getResourceModel($this->_getCollectionClass());

add:

$tableNameCustomerEntity = Mage::getSingleton('core/resource')->getTableName('customer_entity');
$attribute = Mage::getModel('customer/attribute')->load('sage_account_number', 'attribute_code');
$collection->getSelect()
    ->joinLeft(
        array('_table_'.$attribute->getAttributeCode() => $tableNameCustomerEntity.'_' . $attribute->getBackendType()),
        '_table_' . $attribute->getAttributeCode() . '.entity_id = main_table.customer_id ' .
        ' AND _table_' . $attribute->getAttributeCode() . '.attribute_id = ' . $attribute->getAttributeId(),
        array($attribute->getAttributeCode() => '_table_' . $attribute->getAttributeCode() . '.value')
    );

2) in _prepareColumns():

add:

$this->addColumn('sage_account_number', array(
    'header' => $this->__('SagePay Account Number'),
    'index' => 'sage_account_number',
    'filter_index' => '_table_sage_account_number.value',
));

after a needed column.