0

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.

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
| improve this answer | |
  • 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 May 11 '17 at 16:42
  • Do you want to get all product details? Name, description .... ? – sv3n May 11 '17 at 17:00
  • yes, because I want to export that table to another database that is not based on magento. – Arminius May 12 '17 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 May 13 '17 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 May 14 '17 at 8:05
0

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();
| improve this answer | |
  • 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 May 11 '17 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 May 11 '17 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 May 11 '17 at 12:54
  • @Rene .... missing braces at the end ;) – sv3n May 11 '17 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. – René Schep May 12 '17 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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