Best practice to join product attributes

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();
    }

Hi Celldweller I hope you're doing well :-)

Maybe you were wrong in your explanation about the class Mage_Core_Model_Resource_Db_Collection_Abstract, you extend a resource collection not the model because you must not extend a model with a collection class if you want to respect the Magento structure. Am I right?

Based on my correction, I see different kind of approach, depending on how often you want to get the product name attribute. In any case, I think to do a sql query via the Magento Framework is the best way and efficient. It's more faster than to do a Mage::getModel('catalog/product')->load(1234)->getName() for each loaded item. So in fact it will be very similar to the code you use for the sku

CODE NOT TESTED

You want those information each time a collection is loaded

You may in your collection class set into a _beforeLoad method such a code:

protected function _beforeLoad()
{
    $productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

    $this->getSelect()
        ->join( array('product_attribute' => $productName->getBackendTable()),
            'main_table.product_id = product_attribute.entity_id',
            array())
        ->where("product_attribute.attribute_id = ?", $productName->getId());
    }

    return parent::_beforeLoad();
}

You want those information ONLY for the grid

In your _prepareCollection, you will have to add a method in your collection with the same code as it was done above into the _beforeLoad then you can prepare the collection by using this method. Don't use both, I mean don't use together the same code in _beforeLoad and addProductName methods, use only one of them. Here is a sample:

Into your grid.php:

protected function _prepareCollection()
{
    ...
    $collection->addProductName();
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

Into your Collection.php:

public function addProductName()
{
    $productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

    $this->getSelect()
        -> join( array('product_attribute' => $productName->getBackendTable()),
            'main_table.product_id = product_attribute.entity_id',
            array())
        ->where("product_attribute.attribute_id = ?", $productName->getId());

    return $this;
}

I had almost the same problem, but i can't add a comment, because I not have 50 reputation. I spent a lot of time trying to figure out what is wrong (I used code of Sylvain Rayé). My product collection for some reason filtered. So I found a reason.

If you using some import tools (magmi, etc), they often do not create empty attributes at once. Therefore using ->where("product_attribute.attribute_id = ?", $productName->getId()) products that do not have this attribute, will disappear from selection.

Right way is using joinLeft like that:

$productName = Mage::getSingleton('eav/config')->getAttribute('catalog_product','name');

$this->getSelect()
     ->joinLeft(array('product_attribute' => $productName->getBackendTable()),
        "main_table.product_id = product_attribute.entity_id AND
         product_attribute.attribute_id = {$productName->getId()}",
        array());

Hope this helps someone.