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...

1 Answer 1


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);
  • 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. Aug 3, 2016 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. Aug 3, 2016 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, 2016 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 =) Aug 3, 2016 at 20:06

Your Answer

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

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