Magento 2: Filter Product Collection using Category Id always return empty set

Question

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->addAttributeToSelect('*');
   $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`, 
       `e`.`entity_id`, 
       `e`.`attribute_set_id`,  
       ....
       `e`.`visibility`, 
       `e`.`weight`, 
       `e`.`weight_type` 
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

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

Answer 1

check below code

<?php 

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');
        if($category_load):
            $categoryId = $category_load->getId();
            $category_product_collection = $this->_categoryFactory->create()->load($categoryId);
            $collection = $this->_productCollectionFactory->create();
            $collection->addAttributeToSelect('*');
            $collection->addCategoryFilter($category_product_collection);
            $collection->addAttributeToFilter('visibility', Visibility::VISIBILITY_BOTH);
            $collection->addAttributeToFilter('status', Status::STATUS_ENABLED);
            $collection->setPageSize(4);

            return $collection;

        endif;

    }


}

?>

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

Result

I was getting the current category of product collection.

I hope this will help you....!