Magento – Joining Products Table with Custom Module Table (Adding Product Thumbnails to Custom Grid)

customgridjoin-tablemagento-1.9product-images

I created a custom module with a grid and needed to display product thumbnail images there.
Tried codes from the answer below and the thumbnails were added, BUT ALL MY OTHER COLUMNS IN THE GRID ARE BLANK NOW.
How can I join the tables correctly?

..\app\code\local\Cpstest\ProductComment\Block\Adminhtml\Cps\Grid.php

    protected function _prepareColumns()
    {
    $this->addColumn('thumbnail',
    array(
        'header'=> Mage::helper('cpstest_productcomment')->__('Thumbnail'),
        'renderer'  => 'cpstest_productcomment/adminhtml_grid_renderer_image',
            'index'     => 'thumbnail',
            'sortable'  => true,
            'filter'    => false,
            'width'     => 90,
    ));
    }


    protected function _prepareCollection()
    {
    // Get and set our collection for the grid
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection = Mage::getModel('catalog/product')->getCollection()
    ->addAttributeToSelect('sku')
    ->addAttributeToSelect('name')
    ->addAttributeToSelect('attribute_set_id')
    ->addAttributeToSelect('type_id');
    //$collection = your collection
    $store = $this->_getStore();
    $collection->joinAttribute('thumbnail', 'catalog_product/thumbnail', 'entity_id', null, 'left', $store->getId());/**/
    $collection->joinField('id',
    'cpstest_productcomment/cps', //check namespace/modelname
    'id',
    'id=entity_id', 
    null,
    'left');        
    $this->setCollection($collection);      
    return parent::_prepareCollection();
    }

And the renderer file in …\app\code\local\Cpstest\ProductComment\Adminhtml\Grid\Renderer\Image.php

    <?php
    class Cpstest_ProductComment_Block_Adminhtml_Grid_Renderer_Image extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
    {
    public function render(Varien_Object $row)
    {
    try
    {
    $size    = 70;
    if (!$row->getThumbnail())
    {
    $product = Mage::getModel('catalog/product')->load($row->getEntityId());
        if ($product)
        {
            if ($product->getThumbnail())
            {
                $row->setThumbnail($product->getThumbnail());
            }
        }
    }
    $url     = Mage::helper('catalog/image')->init($row, 'thumbnail')->resize($size)->__toString();
    if ($url)
    {
        $html  = '';
        $html .= '<img src="' . $url . '" alt="" width="' . $size . '" height="' . $size . '" />';
        return $html;
    }
    } catch (Exception $e) { /* no file uploaded */ }
    return '';
    }
    }

All my other fields for the grid are blank now

Here is the table structure for my custom grid:

Here is the table structure for my custom grid

Thanks for all your help.

Best Answer

To this to work, your collection must be catalog type.

First add this in your _prepareCollection()

//$collection = your collection
$store = $this->_getStore();
$collection->joinAttribute('thumbnail', 'catalog_product/thumbnail', 'entity_id', null, 'left', $store->getId());

And now in your _prepareColumns()

    $this->addColumn('thumbnail',
        array(
            'header'=> Mage::helper('your_module')->__('Thumbnail'),
            'renderer'  => 'your_module/adminhtml_your_path_to_renderer_thumb',
                'index'     => 'thumbnail',
                'sortable'  => true,
                'filter'    => false,
                'width'     => 90,
        ));

Now, your renderer:

class NameSpace_YourModule_Block_Adminhtml_Path_To_Your_Renderer_Thumb extends Mage_Adminhtml_Block_Widget_Grid_Column_Renderer_Abstract
{
  public function render(Varien_Object $row)
  {
    try
    {
        $size    = 70;

        if (!$row->getThumbnail())
        {
            $product = Mage::getModel('catalog/product')->load($row->getEntityId());
            if ($product)
            {
                if ($product->getThumbnail())
                {
                    $row->setThumbnail($product->getThumbnail());
                }
            }
        }

        $url     = Mage::helper('catalog/image')->init($row, 'thumbnail')->resize($size)->__toString();

        if ($url)
        {
            $html  = '';
            $html .= '<img src="' . $url . '" alt="" width="' . $size . '" height="' . $size . '" />';
            return $html;
        }
    } catch (Exception $e) { /* no file uploaded */ }
    return '';
}

This should work.

UPDATE 1

Add this function in Grid.php

protected function _getStore()
{
    $storeId = (int) $this->getRequest()->getParam('store', 0);
    return Mage::app()->getStore($storeId);
}

UPDATE 2

In order to above code work, your collection must be type of catalog/product. In your _prepareCollection() add this:

$collection = Mage::getModel('catalog/product')->getCollection()
        ->addAttributeToSelect('sku')
        ->addAttributeToSelect('name')
        ->addAttributeToSelect('attribute_set_id')
        ->addAttributeToSelect('type_id');

Now, if you want to join this table with your custom table, then you can add this after above code:

$collection->joinField('your_field',
        'cpstest_productcomment/cps', //check namespace/modelname
        'your_field',
        'entity_id=entity_id', 
         //catalog table has entity_id field, so your custom table which you joining must have a field containing Product Id. Otherwise it wont join.
         //if your field name is product_id then make above like: product_id = entity_id
        null,
        'left');
Related Topic