Actually i want to know sql query possible to get all products of a category using category id.

  • Try this: SELECT DISTINCT cc.entity_id as id, cc.value as path, cc1.value as name FROM catalog_category_entity_varchar cc JOIN catalog_category_entity_varchar cc1 ON cc.entity_id=cc1.entity_id JOIN catalog_category_entity cce ON cc.entity_id=cce.entity_id WHERE cc.attribute_id = '57' AND cc1.attribute_id = '41' ; – Rv Singh Oct 1 '18 at 7:26
  • for get prodcut collection by category id try this answer magento.stackexchange.com/a/125092/55928 – Hitesh Oct 1 '18 at 7:30

You can get the products of a category by 2 methods(In my case category id is 3):

Method 1:

    $objectManager =  \Magento\Framework\App\ObjectManager::getInstance();
    $categoryFactory = $objectManager->get('\Magento\Catalog\Model\CategoryFactory');
    $category = $categoryFactory->create()->load(3);
    $categoryProducts = $category->getProductCollection()->addAttributeToSelect('*');

Method 2:

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e` INNER JOIN `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='3' AND cat_index.is_parent=1 

If you try to get the sql query of the product collection of a given category id(Method 1) then you just need to try the below code:

echo $categoryProducts->getSelect();

By above code you will get the sql of that magento 2 product collection of a category.

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.