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