I have a parent category with subcateogry that contains a lot of product in it, when i tried to filter the product collection using this parent category id, it always return an empty collection:

public function __construct(
   \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
   $this->_productCollectionFactory = $productCollectionFactory;

public function execute(){
   $collections = $this->_productCollectionFactory->create();
   $collections->addCategoriesFilter(array('in' => array(1400)));
   die($collections->getSize()); // always return 0

then i tried to print the collection sql query using $collections->getSelect()->__toString(), and it prints:

SELECT 1 AS `status`, 
FROM   `catalog_product_flat_1` AS `e` 
WHERE  ( e.entity_id IN((SELECT `cat`.`product_id` 
                         FROM   `catalog_category_product` AS `cat` 
                         WHERE  ( cat.category_id IN( '1400' ) ))))

it uses the catalog_category_product table to relate the product and category, but when i check the table in DB, the table has 0 records

enter image description here

i already run php bin/magento indexer:reminded , but still getting the same result

1 Answer 1


check below code


namespace VendorName\Modulename\Block;

use Magento\Framework\View\Element\Template;
use Magento\Framework\View\Element\Template\Context;
use Magento\Framework\View\Page\Config;
use Magento\Catalog\Model\ResourceModel\Product\CollectionFactory;
use Magento\Catalog\Model\CategoryFactory;
use Magento\Framework\Registry;
use Magento\Catalog\Model\Product\Visibility;
use Magento\Catalog\Model\Product\Attribute\Source\Status;

class Productmage extends Template

     * @var \Magento\Framework\Registry $registry
    private $_registry;

     * @var \Magento\Catalog\Model\CategoryFactory $categoryfactory
    private $_categoryFactory;
     * @var Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
    protected $_productCollectionFactory;

     * Constructor
     * @param \Magento\Framework\App\Helper\Context $context
     * @param \Magento\Catalog\Model\Session $catalogSession
    public function __construct(
        Context $context,
        Config $pageConfig,
        CollectionFactory $productCollectionFactory,
        Registry $registry,
        CategoryFactory $categoryfactory,
        array $data = []
    ) {
        parent::__construct($context, $data);
        $this->pageConfig = $pageConfig;
        $this->_registry = $registry;
        $this->_categoryFactory = $categoryfactory;
        $this->_productCollectionFactory = $productCollectionFactory;

    *@return Array
    public function getProductDataCollection()

        $category_load = $this->_registry->registry('current_category');
            $categoryId = $category_load->getId();
            $category_product_collection = $this->_categoryFactory->create()->load($categoryId);
            $collection = $this->_productCollectionFactory->create();
            $collection->addAttributeToFilter('visibility', Visibility::VISIBILITY_BOTH);
            $collection->addAttributeToFilter('status', Status::STATUS_ENABLED);

            return $collection;





my query print is

SELECT `e`.*, `cat_index`.`position` AS `cat_index_position` FROM `catalog_product_entity` AS `e` INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id=15


enter image description here

I was getting the current category of product collection.

I hope this will help you....!

  • thanks for your answer, but I'm still getting the same result
    – jojo
    Apr 29, 2020 at 10:24
  • Check category id is right or not
    – Devidas
    Apr 29, 2020 at 10:25
  • Template file print $block->getProductDataCollection()->getData();
    – Devidas
    Apr 29, 2020 at 10:27
  • the category id is correct i already double check, see my updated question
    – jojo
    Apr 29, 2020 at 10:36
  • see my updated answer
    – Devidas
    Apr 29, 2020 at 10:56

Your Answer

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

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