0

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?

1 Answer 1

2

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.

2
  • Your solved my problem, thanks a lot +1 May 11, 2018 at 14:56
  • @PedrodeAlbuquerque accept him to answer if really help you. so it will help others. May 12, 2018 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.