Magento - Add customer attribute to order grid

Question

I'm trying to add a custom customer attribute to the order grid. This custom attribute borns from an extension that I had installed on magento. This special field i managed to get it in customer grid by entering this code in the Grid.php of the customer list:

$this->addColumn('agente', array(
            'header'    => Mage::helper('customer')->__('Agente'),
            'width'     => '150',
            'index'     => 'agente',
        ));

But in the Grid.php of the orders does not work and I tried everything. The last code that I tried is this:

protected function _prepareCollection()
    {
        $collection = Mage::getResourceModel($this->_getCollectionClass());
        $collection->getSelect()->joinLeft(array('sfoa'=>'sales_flat_order_address'),'sfoa.entity_id=main_table.entity_id',array('sfoa.email'));
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

protected function _prepareColumns()
    {
        $this->addColumn('agente', array(
            'header' => 'Agente',
            'width' => '80px',
            'type' => 'text',
            'index' => 'agente',
        'filter_index' => 'sfoa.agente'));

The error that returns me in the log is always this: "Unknown column 'sfoa.agente' in 'field list', query was: SELECTmain_table. *, Sfoa.agente FROMsales_flat_order_grid AS main_table"

How else could I take this attribute and insert it into Order Grid?

Answer 1

agente is a customer attribute : so you have to join the sales_flat_order table (which is the main table of the order grid collection) and the table where the data of your attribute are stored. You should be able to use the customer_id to join the two tables.

So, could you try this :

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

    $agenteAttribute = Mage::getSingleton('eav/config')->getAttribute('customer', 'agente');
    $agenteTable = $agenteAttribute->getBackendTable();
    $agenteId = $agenteAttribute->getAttributeId();

    $collection->getSelect()->joinLeft(
        array('ca' => $agenteTable),
        'ca.attribute_id='.$agenteId.' AND ca.entity_id = main_table.customer_id',
        array('agente'  => new Zend_Db_Expr('ca.value')
        )
    );


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

protected function _prepareColumns()
{
   $this->addColumn('agente', array(
        'header' => Mage::helper('sales')->__('agente'),
        'index' => 'agente',
    ));

}