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