Left join on custom collection, to get product name by product ID

Question

There's a collection which contains product ids. I'd like to join it with the magento table that stores paroduct names, in order to add product name to collection.

$collection = Mage::getModel("productquestions/questions")->getCollection();
$this->setCollection($collection);

$collection->getSelect()
            ->joinLeft(I_NEED_HELP_IN_THIS_PART_OF_CODE);

The table that's used to get collection is "nbpq_product_questions" and the column that stores product id is "product_id".

I've seen some code examples about left join collection, but I can't make it work in this particular case. Can you help me please?

Answer 1

Instead of starting from your collection, we should start from the Magento's product collection.

<?php

$collection = Mage::getModel('catalog/product')
    ->getCollection()
    ->addAttributeToSelect('name')
    ->getSelect()
    ->joinLeft(
        array('your_table' => $this->getTable('productquestions/questions')),
        'e.entity_id = your_table.product_id'
    );

Before using this code, make sure that the $this->getTable() function is returning your table correctly. If not, that part needs to be modified based on your config.xml file's <entity> tag content.

Since Magento is saving the Product data in EAV table structure, it is not an easy task to write the correct JOIN query to get the Product name. To make it simple, Magento is proving the addAttributeToSelect method. That's why we are starting from the Product collection rather than starting from your collection.