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

| improve this question | | | | |

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....!

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

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.