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

• Gabriel91: Great man!!! Thank you very much! :D One more thing please: To add a default filter to loading the order page how could I do? The code I would like to use for the filter is this: ->addFieldToFilter('agente',array('in' => array(312))) In customer grid works but in order grid not works.
• Gabriel91: Error log that return for the filter of the field 'agente' is this: Unknown column 'agente' in 'where clause', query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table WHERE (agente IN(312))";i:1;s:5885:
• Julien Loizelet: I'm not sure how to set a default filter on your grid. Maybe you can try to add $this->setDefaultFilter(array('agente'=>312)); in the __construct() method of your grid.
• Gabriel91: I tried man but the error log is the same: "Unknown column 'agente' in 'where clause', query was: SELECT COUNT(*) FROM sales_flat_order_grid AS main_table WHERE (agente LIKE '%312%')";"
• Gabriel91: This error occurs to me because in the "search filter" table of the orders the customer attribute "agent" does not really exist but only in the table of the customer list at the moment. Do I have to create a table on the database for this or does a function exist that allows me to recall this filter?