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


This returns 4,972 products, but there is a total of 7,265 products in my store which is shown under Manage Products in the admin panel.

Has anyone experienced this before and possibly knows a way to fix this?



I have also ran the following code to see the SQL being executed:

echo $collection->getSelect();

This has returned the following SQL:

SELECT 1 AS `status`, `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurring`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurring_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`manufacturer`, `e`.`prmotional_text`, `e`.`brand`, `e`.`brand_value` FROM `shop_catalog_product_flat_1` AS `e`


I have just amended all my products to be included in my main store view (ID 1), but still no luck.

I've also noticed the total number of products changes slightly, it has gone from 4,972 to 4,971 to 4,973.

  • Can you elaborate on your "custom page outside of Magento". It seems to me you're using this collection on the frontend in your store ID. So, this would make it a custom page within Magento. You're getting only 4972 products because those are all that's supposed to be visible on your store (ID 1). Why do you need all of the products on the frontend? – musicliftsme May 14 '15 at 14:01
  • It's a .php file which isn't part of Magento. I need it to generate a .txt file of all my products to submit to Google Base. – Scott Bowers May 14 '15 at 14:14
  • Strange, in your PHP script, are you setting a store view mistakenly? Try setting it to the admin view, which is supposed to return all products. Mage::app()->setCurrentStore(0) – musicliftsme May 14 '15 at 14:15
  • I haven't set a store view, however, your code has solved my problem. Thank you very much! :-) – Scott Bowers May 14 '15 at 14:18
  • I'm gonna make it an answer :) – musicliftsme May 14 '15 at 14:19

When you get a collection of products and you have it in the system configuration to use the product flat table, the collection will retrieve products from the flat table of the corresponding store view.

Looking at your query, it's retrieving your products from the product flat table of store ID 1 (shop_catalog_product_flat_1) for some reason. If you want to retrieve all products as you see in the admin, then you can force a store in your independent script using the following.


This will ensure your collection will retrieve products from the main product table, shop_catalog_product_entity in your case.


I guess that you have use the code at Frontend. As per magento, only enable and current store product will be shown at frontend. That is why it only shows 4972 products.

This will occur when flat setting enabled

  • Hi Amit, Apologies but I do not understand. The code is being used on a custom page outside of Magento. Thanks. – Scott Bowers May 14 '15 at 13:13
  • frontend means the area from customer will purchase the products – Amit Bera May 14 '15 at 13:23

So there is really no easy way for debugging this without know exactly how this collection is being built.

For all collections in Magento you can get the select query that it is made up of.

$select = $collection->getSelect();

Now this will give you an object of the type Varien_Db_Select and I often find that the simplest way of getting the actual sql behind this select is to cast the object to a string and log it.

Mage::log((string) $select);

Once you have this sql you will probably see that either the products are not active or not attached to the store.

  • Please see Edit on my original post, I've included the SQL. Thanks! – Scott Bowers May 14 '15 at 13:22
  • @ScottBowers then you can play around with that sql in and figure out when the other products are cut out. It could be website, index or a number of other issues but we wont really be able to help you. – David Manners May 14 '15 at 13:26

Maybe you have different products in different stores? Try

$collection = Mage::getResourceModel('reports/product_collection')->addAttributeToSelect('*');

  • Still the same number unfortunately, 4,972. – Scott Bowers May 14 '15 at 13:22

To make sure you are loading all product regardless of the store. I suggest you use the following.

    ->addStoreFilter(Mage_Core_Model_App::ADMIN_STORE_ID) // default store

Then to print the sql query that is being executed you could use the following.


This will echo the query.

If you want to echo the query and log it you can use.

// $collection->load($printQuery, $logQuery);
$collection->load(true, true);
  • Post the query here if you want some help :) – Danny Dev Nz May 14 '15 at 13:25

Your Answer

