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.

2 Answers 2



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

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
  • I only get numbers as result: 860 4 11 simple 163180 0 0 2014-04-04 20:53:43 2016-03-29 12:33:58 1 861 4 11 simple 45600 0 0 2014-04-04 20:53:45 2015-08-14 12:37:22 1 899 4 11 simple 161420 0 0 2014-04-04 20:54:37 2015-10-13 07:04:15 1 927 4 11 simple 275230 0 0 2014-04-04 20:55:18 2015-08-14 12:39:44 1 963 4 11 simple 160933 0 0 2014-04-04 20:56:08 2017-03-13 17:55:41 1
    – Arminius
    Commented May 11, 2017 at 16:42
  • Do you want to get all product details? Name, description .... ?
    – sv3n
    Commented May 11, 2017 at 17:00
  • yes, because I want to export that table to another database that is not based on magento.
    – Arminius
    Commented May 12, 2017 at 11:10
  • I can send you some sample queries at the begining of next week (have them at work and im too lazy to write them again now :P ) ... BUT ... i defintly suggest to learn some things about "magentos way" to build queries (Mage::getModel('catalog/product')... and append ->getSelect()->__toString() I'm pretty sure it takes less time as building correct queries at your own.
    – sv3n
    Commented May 13, 2017 at 20:19
  • Thank you so much. I am indeed learning about the queries but yet dont know where to paste them so as to export the results. I do use Laravel MVC and I am familiar with this MVC model. However, once I finish this I won't need magento anymore, this was just a friend request to me.
    – Arminius
    Commented May 14, 2017 at 8:05

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();
  • You are right, but I am a coder who has been thrown in to get magento database data and I am not familiar at all with magento language. I dont even know where to paste that. Could you kindly tell me on what page can I write that query?
    – Arminius
    Commented May 11, 2017 at 9:50
  • In order to do that I have to create a huge number of xml files, modules, models, blocks, etc. That is a nightmare. I prefer to use SQL code so that I dont need to go through that hell of files.
    – Arminius
    Commented May 11, 2017 at 12:32
  • Besides, I already downloaded all products using magento export tool, but I cannot filter there because they are not classified by trademark.
    – Arminius
    Commented May 11, 2017 at 12:54
  • @Rene .... missing braces at the end ;)
    – sv3n
    Commented May 11, 2017 at 14:39
  • @Arminius that depends on where you want to show the data. But the code should work on any location in Magento. Including the template files, but it's bad practice to directly load models and collections there. Commented May 12, 2017 at 9:14

Your Answer

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

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