SQL query to obtain all products info from a specific category

Question

I have on the tree of categories one category named Trademarks and within this one, a category named Pharmatron (and many other). I am trying to get all products from this category (Pharmatron). The category Trademarks is just another way of providing access to the products in the frontend menu, in case the visitor just wants to check products from a specific brand.

The ID of Trademarks is 7 and the ID of Pharmatron is 411.

I am kind of guessing, but not quite coming clear.. select *

FROM cp8_catalog_product_entity_varchar as cepvar
LEFT JOIN cp8_catalog_category_entity_varchar as ccevar
ON cepvar.entity_id = ccevar.entity_id
LEFT JOIN eav_attribute AS ea 
ON cepvar.entity_type_id = ea.entity_type_id 
AND ea.backend_type = 'varchar'
AND cepvar.value = 'Pharmatron'

I am getting zero rows with that query. However, if I remove the WHERE clause I get basically a whole list of any product.

I have learned a bit about the EAV system and I understand why it has to have those JOINS, yet not grasped fully the interrelation between categories and products tables so as to solve the query.

Answer 1

Magento-way:

echo Mage::getModel('catalog/category')
    ->load(1) // your category ID here
    ->getProductCollection()
    ->getSelect()
    ->__toString();

Result from above:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position`
FROM `prefix_catalog_product_entity` AS `e`
INNER JOIN `prefix_catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id = '1' AND cat_index.is_parent=1

Answer 2

I would not use a direct database query like you are doing here. I would instead get the product collection and filter that instead.

$collection = Mage::getModel('catalog/category')->load($categoryId)->getProductCollection();