Join to get thumbnail from sales order item - add column to order grid

[nickyc]

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')
);*/

[KuafuSoft]

1, you should join from catalog_product_entity_varchar where attribute_id = {thumbnail attribute id}

2, its possible to upload a large image for thumbnail, so you still have to resize it

in your for loop, you can create a dummy product model and set only the thumbnail value and resize with helper

foreach($items as $item){
   $product = Mage::getModel('catalog/product');
   $product->setThumbnail($item->getThumbnail());
   $url = Mage::helper('catalog/image')->init($this->getProduct(), 'thumbnail')->resize(75);
}

[Cristianriv]

Hello, I have a similar problem. I believe in the table sales_flat_order a column that stores a verification code that sends the bank at the time of payment , this data it captured and stored in the table without any problem, my problem is the time to show that column in the list of customer orders, which file to change , where should I put the line of code to insert the column in the order list frontend