How to use SQL in Magento modules

Question

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?

Thanks!

---

REAL EXAMPLE:

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');
$this->getSelect()
 ->join(
 array('item' => $itemTable),
 "(item.order_id = main_table.entity_id AND item.parent_item_id IS NULL)",
 array()
 );

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

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() )
 ->joinInner(
 'catalog_category_entity_varchar',
 'catalog_category_product.category_id = catalog_category_entity_varchar.entity_id',
 array('value')
 )
 ->joinInner(
 'catalog_product_entity',
 'catalog_category_product.product_id = catalog_product_entity.entity_id',
 array()
 )
 ->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);
print_r($data);

• Bruno Monteiro: 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: 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.
• Sohel Rana: 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
• Bruno Monteiro: Just as a follow up, I was able to use Zend_Db_Expr to access the product ID and solve my problem =)