Magento 1.9 join table issue

Question

I'm having an issue with Magento 1.9.2.4 trying to make a join on my collection, here's the problem:

Actually I've got a custom table named product_banner which I'm getting by:

$collection = Mage::getModel('mymodule_productbanner/productbanner')->getCollection();

But I need to join this table with catalog_category_entity_varchar, I've tried solution:

    $catalogTable = Mage::getSingleton('core/resource')->getTableName('catalog/category');

    $collection = Mage::getModel('exablack_productbanner/productbanner')->getCollection()->getSelect()->join(array('category' => $catalogTable),
        'main_table.category_id = category.entity_id AND category.attribute_id = 41');

But for no avail... The pure sql query I need to run to get my desired result would be:

    select catalog_category_entity_varchar.value, product_banner.banner_url, 
    product_banner.text from product_banner inner join catalog_category_entity_varchar 
    on product_banner.category_id = catalog_category_entity_varchar.entity_id and 
    catalog_category_entity_varchar.attribute_id = 41;

How can I achieve this with magento collections?

Answer 1

Please try with below code as I haven't run a query on MySQL because I have no any custom module table include with category id field but I build it based on your collection

$collection =
Mage::getModel('mymodule_productbanner/productbanner')->getCollection()
->addFieldToSelect('banner_url');

$attribute =
Mage::getSingleton('catalog/config')->getAttribute('catalog_category',
'name');

$collection->getSelect()
     ->join(array('category_varchar' => $attribute->getBackendTable()),'category_varchar.entity_id=
 main_table.category_id',array('value'))
     ->where('category_varchar.attribute_id = ?',$attribute->getId());

echo $collection->getSelect();
die;

and run this query on MySQL.Hope it will work for you.