I have the follow SQL that I want to execute from a custom module:

SELECT c2.value
FROM catalog_category_product c1
INNER JOIN catalog_category_entity_varchar c2 ON (c1.category_id = c2.entity_id)
INNER JOIN catalog_product_entity c3 ON (c1.product_id = c3.entity_id)
WHERE c2.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 3)
AND c3.entity_id = PRODUCT_ID_HERE

I am new on this and, from what I read, I should be able to use collections and commands like getSelect(), but I can't figure out a way to "convert" my SQL query into this format.

How can I do that? Or where can I read more about it?



To be more clear, I need to modify an extension in order to bring product categories field in a specific report. Editing .phtml files I am able to do somethings like this:

$product_id = PRODUCT_ID_HERE;
$product = Mage::getModel('catalog/product')->load($product_id);
$cats = $product->getCategoryIds();

foreach ($cats as $category_id) {
   $_cat = Mage::getModel('catalog/category')->load($category_id);
   echo $_cat->getName();

But with that I am changing just the Frontend. The better solution would be to edit the extension Model to do something like what they already have:

$itemTable = Mage::helper('advancedreports/sql')->getTable('sales_flat_order_item');
      array('item' => $itemTable),
      "(item.order_id = main_table.entity_id AND item.parent_item_id IS NULL)",

But I can't figure out how to "convert" my SQL query into this Model format...


You can do it following way.

$product_id = 1;

$db   = Mage::getSingleton('core/resource')->getConnection('core_read');
$subselect = $db->select()
    ->from('eav_attribute', array('attribute_id'))
    ->where('attribute_code = ?', 'name')
    ->where('entity_type_id = ?', 3);

$sql = $db->select()->from( 'catalog_category_product', array() )
        'catalog_category_product.category_id = catalog_category_entity_varchar.entity_id',
        'catalog_category_product.product_id = catalog_product_entity.entity_id',
    ->where("catalog_category_entity_varchar.attribute_id = ($subselect)")
    ->where("catalog_product_entity.entity_id=?", $product_id);
// print sql
echo $sql->__toString();

// print data
$data = $db->fetchAll($sql);
|improve this answer|||||
  • Thanks for the answer! Unfortunately, I am still having one last problem: I can't get the product ID dynamically. I posted the entire code here: codeshare.io/brunomonteiro. I inserted the above code in line 60. – Bruno Monteiro Aug 3 '16 at 18:10
  • If you could please take a look to see if you have any ideas on how I could get the product ID dynamically, I really appreciate that. In line 93 the product ID information is used, but I did a lot of tests and in none of them I was able to get that and insert in my code. – Bruno Monteiro Aug 3 '16 at 18:12
  • I have no idea about AW_Advancedreports(it's third party paid extension), But if you have order then you can get product id. Or pass product id to 'reInitSelect' if you have – Sohel Rana Aug 3 '16 at 18:18
  • Just as a follow up, I was able to use Zend_Db_Expr to access the product ID and solve my problem =) – Bruno Monteiro Aug 3 '16 at 20:06

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.