Best Practice – Join Product Attributes in Magento

backendbest practicecollection;

I have a custom table with a product reference product_id. Now I would like to show product information (sku, name) in my backend grid, but I am unsure what is the best practice to do this?

My best guess for SKU is as follows:

$collection->join(
    'catalog/product',
    'product_id=`catalog/product`.entity_id',
    array('product_sku' => 'sku')
)

(code from the _prepareCollection() method in my grid block class)

But what about the product name? It can be found in catalog_product_entity_varchar. My understanding is that you can rather easy get it if your own resource model and collection is based on Mage_Eav_Model_Entity_Collection_Abstract because then you can use methods like joinAttribute.
But my model is based on a simple table and extending from Mage_Core_Model_Resource_Db_Collection_Abstractand there is no joinAttribute method available.

So what is the best way to get the product name in this case?

Thanks for your time and help 🙂

Update:
To be more precise, I was talking about my resource model and collection. It matches a simple flat table with just a few attributes like

entity_id    product_id    created_at    user_id

My intention is to grid in the backend where I show some statistics: 

ProductSku    Count(ProductSku)    MAX(created_at)

As far as I know, the best approch to do this, is via the grid block class and the method to go is _prepareCollection().

My method looks like this:

protected function _prepareCollection()
{
    // Get and set our collection for the grid
    $collection = Mage::getResourceModel($this->_getCollectionClass());
    $collection
        ->join(
            'catalog/product',
            'product_id=`catalog/product`.entity_id',
            array('product_sku' => 'sku')
            )
        ->addExpressionFieldToSelect('product_count', 'COUNT({{product_id}})', 'product_id')
        ->addExpressionFieldToSelect('newest', 'MAX({{created_at}})', array('created_at'=>'main_table.created_at'))
        ->getSelect()->group('product_id');
    $this->setCollection($collection);

    return parent::_prepareCollection();
}

This works well for the sku (which I refer to as product_sku in the _prepareColums() method. But what join do I need to insert here in order to get the name (and e.g. the manufacturer)?

Am I doing something wrong because I can't use joinLeft()?

Best Answer

In your collection class (/Some/Module/Model/Mysql4 (or Resource)/YourModel/Collection.php) add this method:

public function addProductData()
    {
        /** add particular attribute code to this array */
        $productAttributes = array('name', 'price', 'url_key');
        foreach ($productAttributes as $attributeCode) {
            $alias     = $attributeCode . '_table';
            $attribute = Mage::getSingleton('eav/config')
                ->getAttribute(Mage_Catalog_Model_Product::ENTITY, $attributeCode);

            /** Adding eav attribute value */
            $this->getSelect()->join(
                array($alias => $attribute->getBackendTable()),
                "main_table.product_id = $alias.entity_id AND $alias.attribute_id={$attribute->getId()}",
                array($attributeCode => 'value')
            );
            $this->_map['fields'][$attributeCode] = 'value';
        }
        /** adding catalog_product_entity table fields */
        $this->join(
            'catalog/product',
            'product_id=`catalog/product`.entity_id',
            array('sku' => 'sku', 'type_id' => 'type_id')
        );
        $this->_map['fields']['sku']     = 'sku';
        $this->_map['fields']['type_id'] = 'type_id';
        return $this;
    }

In your grid block use this function:

 protected function _prepareCollection()
    {
        $collection = Mage::getModel('some/yourmodel')
            ->getCollection()->addProductData();
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }
Related Topic