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.