I'm trying to use an observer to add the thumbnail path to the sales order grid collection - the purpose of this is to add an extra column to the order grid which displays the sales item images. I've seen a few tutorials which call load() within the loop to get the product thumb, however this isn't great for performance. I think adding the thumbnail path to the collection is a cleaner way to do this, the problem I'm having is getting the db joins correct to get that data!
At the moment, I'm able to get the base product image URL added to the collection, but this obviously isn't ideal either as it means loading far higher resolution images than necessary. Below there's some commented out, failed, attempts at getting the thumbnail - if anyone can see where I'm going wrong that would be greatly appreciated.
$collection = $observer->getOrderGridCollection(); $select = $collection->getSelect(); $select->joinLeft( array('sfoi' => 'sales_flat_order_item'), 'sfoi.order_id=`main_table`.entity_id', array('product_id') ); $select->joinLeft( array('cpemg' => 'catalog_product_entity_media_gallery'), 'sfoi.product_id = cpemg.entity_id', array('value_id', 'thumbnail' => 'value') ); // The above adds the base product image URL to the collection, the below is what i've tried in order to try and get the thumb /* $select->joinInner( array('cpemgv' => 'catalog_product_entity_media_gallery_value'), 'cpemg.value_id = cpemgv.value_id AND position = 3', array('value_id') ); /*$select->joinLeft( array('ea' => 'eav_attribute'), 'ea.attribute_id = catalog_product_entity_varchar.attribute_id AND attribute_code = "thumbnail"', array('value') );*/