2

A client wants a specific piece of data from the customer_entity_varchar table to display in place of the customer's last name in the Sales Order Grid. I'm trying to join the customer_entity_varchar table to the sales_flat_order_grid table, but have run into issues. I was able to successfully join the sales_flat_order_payment table to the sales_flat_order_grid table to display the PO Number in the grid; Presumably due to the similar nature of the tables. Here's the join I initially tried from the Mage_Adminhtml_Block_Sales_Order_Grid class. And yes, I know not to modify core files. I just wanted to make sure the code was right before extending the framework. :)

protected function _prepareCollection()
{
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $resource = Mage::getSingleton('core/resource');
    $collection->getSelect()->join('customer_entity_varchar', 'main_table.customer_id = customer_entity_varchar.entity_id', array('value'));
    $this->setCollection($collection);
    return parent::_prepareCollection();
}
2

Suppose the attribute code of PO Number is po_number You need get attribute details using

Mage::getResourceModel('customer/customer') Model by attribute code .

If your attribute code is store_state the you can get it attribute table name and attribute using below code.

$po_number  = Mage::getResourceModel('customer/customer')->getAttribute('po_number');
$tablename=$po_number->getBackend()->getTable();

$table been taken by static.it can take dynamic by above code

$attributeId=$po_number>getAttributeId() 

As customer_entity_varchar table has lots of records with different attribute so you need to put attribute id in join query condition.

$collection = Mage::getResourceModel($this->_getCollectionClass());
$collection>getSelect()->joinLeft(
    array('ccp' =>$po_number->getBackend()->getTable()),
    'ccp.entity_id = main_table.customer_id
    AND ccp.attribute_id = '.(int) $po_number->getAttributeId() . '
    ',
    array('$po_number-'=>'value')); 
  • I've got the PO Number part worked out. What I'm specifically looking for is to grab the 'value' column of the customer_entity_varchar table and join it to the sales_flat_order_grid table (I'm making the assumption that that's the correct table to join it to in order for it to show up on the orders page in the admin grid). I'll make some alterations to the code you gave and see if I can get it to work. Thanks Amit! – Joshua Romero Apr 10 '15 at 20:47
  • That worked buddy! I had to tweak the code a bit to match the data I needed, but the bones of what you suggested completely worked. Thank you so much Amit. – Joshua Romero Apr 10 '15 at 21:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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