Adding attribute using JOIN to Collection

Question

I have the following code snippet in my _prepareCollection function. In this collection, I am missing an attribute that I need to have in order to display to a grid(style number). I am able to access the attribute using the entity_id from the collection, but I have no idea how to go about joining this attribute to my collection.

protected function _prepareCollection() {
 $collection = $this->getOrder()->getProducts();
 foreach ($collection as $item){
 $styleNumber = Mage::getModel('Catalog/Product')->load($item->entity_id)->manufacturersstylenumber;
 //Join this style number to $collection 
 }
 $this->setCollection($collection);
 return parent::_prepareCollection();
}

Edit: Output of $collection->getSelect()->toString();

SELECT `main_table`.*, `catalog/product`.*, `Purchase/CatalogProductDecimal`.`value` AS `sale_price`, `catalog_product_entity_varchar`.`value` AS `small_image` FROM `purchase_order_product` AS `main_table`
INNER JOIN `catalog_product_entity` AS `catalog/product` ON entity_id=pop_product_id
INNER JOIN `catalog_product_entity_decimal` AS `Purchase/CatalogProductDecimal` ON `catalog/product`.entity_id=`Purchase/CatalogProductDecimal`.entity_id and `Purchase/CatalogProductDecimal`.store_id = 0 and `Purchase/CatalogProductDecimal`.attribute_id = 64
LEFT JOIN `catalog_product_entity_varchar` ON `catalog/product`.entity_id=`catalog_product_entity_varchar`.entity_id and `catalog_product_entity_varchar`.store_id = 0 and `catalog_product_entity_varchar`.attribute_id = 75 WHERE (pop_order_num = '3')

Edit: I suppose my question is how would I create a join using entity_id to my catalog/product and adding the styleNumber attribute for my addColumn later.

• Marius: Is this a custom module? What does $this->getOrder()->getProducts() do?
• nguyminh: yes, it's a an extension module that was downloadable. that line is simply just a way to load up the collection with certain parameters. Either way, it's similar to calling $collection = mage::getModel('purchase/order')
• Marius: And does $this->getOrder()->getProducts() return an instance of Mage_Catalog_Model_Resource_Product_Collection ?
• nguyminh: getOrder() function is $this -> order = mage::getModel('Ppurchase/Order')->load($value); return $this
• Marius: I'm more curious how getProducts looks like. Add the code to the question.

Answer 1

without knowing how the getProducts method looks like I can take a shot and advice you to try this:

protected function _prepareCollection() 
{
 $collection = $this->getOrder()->getProducts();
 $collection->addAttributeToSelect('manufacturersstylenumber');
 $this->setCollection($collection);
 return parent::_prepareCollection();
}

This only works if getProducts returns an instance of Mage_Catalog_Model_Resource_Product_Collection

• nguyminh: This is one of the first thing I tried to do, and sadly it does not work. I'm fairly certain that getProducts does not return that collection, but manufacturersstylenumber is definitely within Mage_Catalog_Model_Resource_Product_Collection. I just need to figure out how to join it
• philwinkle: I don't know if this will work because the collection is not able to be modified after it has already loaded. I believe getProducts() calls an explicit load.
• Marius: Yep. If load is called then it's forever.
• nguyminh: I don't know about getProducts(), but getOrder() calls an explicit load.
• nguyminh: Do you guys know how I can just do a simple join for this $collection with the 'Catalog/Product' table? Knowing the entity_id