Product Collection Only Getting the Main Website Products not All Store Products

Question

I am using a basic product collection to get in-stock enabled product but it isn't returning all in-stock enabled products in my store. Please find the code below:

$Products =  Mage::getModel('catalog/product')->getCollection();
$Products->addAttributeToSelect('*');
$Products->addAttributeToFilter('status', 1);
Mage::getSingleton('cataloginventory/stock')->addInStockFilterToCollection($Products);
echo count($Products);

Its showing the count of 1964 records which is present in main website but total it must show 2003 records which is present including all stores.

I tried with filter $Products->addWebsiteFilter(array(1,2,3,4)); but still it returns the main website product count.

I tried this https://magento.stackexchange.com/questions/67893/product-collection-wont-get-all-productstoo, but not worked.

Also suggest some best query to fetch the collection,so that it not load the server.

Thanks

Answer 1

It seems your project has some problems working with collections, this can be caused by several things... so, let's try working with database. Even if you need to return product objects this would be (kindly, depending by the number of products in catalog) lighter & faster than working with collections

First, let's get the connection resource & implement some method to get attribute data in the class where you want to manage this

protected $_resource = null;

public function getResource()
{
    if (is_null($this->_resource)) {
        $this->_resource = Mage::getSingleton('core/resource');
    }
    return $this->_resource;
}

protected function _getAttribute($code)
{
    $readConnection = $this->getResource()->getConnection('core_read');
    $query = $readConnection->select()
        ->from($this->getResource()->getTableName('eav_attribute'))
        ->where('attribute_code = ?', $code);

    $results = $readConnection->fetchAll($query);
    if (!empty($results)) {
        return $results[0];
    }
    return false;
}

By default, status attribute has website scope (don't know if you have set it as Global in your project), so you need to select some website to filter by that attribute. If you want to get products enabled at least in one website you can implement some like this

protected function _isEnabled($productId)
{
    $attribute = $this->_getAttribute('status');
    $attributeId = $attribute['attribute_id'];

    $readConnection = $this->getResource()->getConnection('core_read');
    $query = $readConnection->select()
        ->from($this->getResource()->getTableName('catalog_product_entity_int'))
        ->where('attribute_id = ?', $attributeId)
        ->where('entity_id = ?', $productId)
        ->where('value = ?', Mage_Catalog_Model_Product_Status::STATUS_ENABLED);
        // you can implement additional website / store filter here

    $results = $readConnection->fetchAll($query);
    if (!empty($results)) {
        return true;
    }
    return false;
}

Let's assume stock management is enabled in the project, so all products must have some records in stock tables, so implement a method to get only in_stock products

protected function _isInStock($productId)
{
    $readConnection = $this->getResource()->getConnection('core_read');
    $query = $readConnection->select()
        ->from($this->getResource()->getTableName('cataloginventory_stock_item'))
        ->where('product_id = ?', $productId)
        ->where('is_in_stock = ?', 1);

    $results = $readConnection->fetchAll($query);
    if (!empty($results)) {
        return true;
    }
    return false;
}

Now, get all products in catalog product main table & apply the filters

$readConnection = $this->getResource()->getConnection('core_read');
$query = $readConnection->select()->from($this->getResource()->getTableName('catalog_product_entity'));
$results = $readConnection->fetchAll($query);
$products = array();
foreach ($results as $row){
    if (! $this->_isEnabled($row['entity_id'])){ continue; }
    if (! $this->_isInStock($row['entity_id'])){ continue; }
    // if you need to return product objects, load them
    // $products[] = Mage::getModel('catalog/product')->load($row['entity_id']);
    $products[] = $row['entity_id']; 
}
print_r(count($products));

Answer 2

Check if flat Catalog is enabled (System -> Configuration -> Catalog -> Catalog -> Frontend). If so disable it.