I have a 3rd party extension which provides a grid. Now I want to extend the grid with a simple Magento attribute. It looks so easy but I can't get it to work! I modified the function _prepareCollection() and wanted to add an attribute to the selection. Unfortunately all attempts failed.
That is my most promising solution (added before "setCollection") but it doesn't work either:
$collection->getSelect()->join( array( '_atr'=>'eav_attribute'), "_atr.attribute_code = 'second_sku'", array());
// $collection->addAttributeToSelect('second_sku');
Thats the (original) full function:
protected function _prepareCollection() {
$orderId = $this->getOrder()->getid();
$collection = mage::getResourceModel('Purchase/OrderProduct_collection')
->addFieldToFilter('pop_order_num', $orderId);
//only products not supplied
$collection->getSelect()->where('(pop_qty - pop_supplied_qty) >0');
//add image picture
$smallImageTableName = mage::getModel('Purchase/Constant')->getTablePrefix() . 'catalog_product_entity_varchar';
$collection->getSelect()->joinLeft($smallImageTableName,
'pop_product_id=`' . $smallImageTableName . '`.entity_id and `' . $smallImageTableName . '`.store_id = 0 and `' . $smallImageTableName . '`.attribute_id = ' . mage::getModel('Purchase/Constant')->GetProductSmallImageAttributeId(),
array('small_image' => 'value'));
//join with product
$productTableName = mage::getModel('Purchase/Constant')->getTablePrefix() . 'catalog_product_entity';
$collection->getSelect()->joinLeft($productTableName,
'pop_product_id=`' . $productTableName . '`.entity_id',
array('sku' => 'sku'));
$this->setCollection($collection);
return parent::_prepareCollection();
}
Any help will be much appreciated! Thank you!
EDIT: If I add my code to the original function I'll get following select:
SELECT `main_table`.*, `catalog_product_entity_varchar`.`value` AS `small_image`, `catalog_product_entity`.`sku` FROM `purchase_order_product` AS `main_table` LEFT JOIN `catalog_product_entity_varchar` ON pop_product_id=`catalog_product_entity_varchar`.entity_id and `catalog_product_entity_varchar`.store_id = 0 and `catalog_product_entity_varchar`.attribute_id = 109 LEFT JOIN `catalog_product_entity` ON pop_product_id=`catalog_product_entity`.entity_id INNER JOIN `eav_attribute` AS `_atr` ON _atr.attribute_code = 'second_sku' WHERE (pop_order_num = '1') AND ((pop_qty - pop_supplied_qty) >0);
As you see the select "works" but the attribute is not part of the selection. I already tried addAttributeToSelect and addFieldToSelect but both throw an error.
Mage::log($collection->getSelectSql(true), null, 'query.log', true);
to see the SQL produced for this collection.$collection->getSelect()->columns(array('second_sku'=>'_atr.attribute_value'));