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?

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.

  • Your solved my problem, thanks a lot +1 – Pedro de Albuquerque May 11 '18 at 14:56
  • @PedrodeAlbuquerque accept him to answer if really help you. so it will help others. – Nikunj Vadariya May 12 '18 at 4:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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