Catalog indexing semantically broken

Question

I'm working on a site that has 3 root categories, one per store in the Magento installation.

On the categories page (Catalog > Manage Categories) I can see the category ids and the number of products they have respectively.

• Category1 - Id: 6 - store: 3 - 11 products
• Category2 - Id: 10 - store: 4 - 11 products
• Category3 - Id: 14 - store: 5 - 11 products

I've used the CMS to distinguish the content at the store-view level for each store. For example, on the store for the first category, I have this (filtering the products to category 6)

{{block type="catalog/product_list" name="home.catalog.product.list" alias="products_homepage" category_id="6" template="catalog/product/list.phtml"}}

However, all 33 products are being displayed. Any idea why products from the other categories are being displayed?

Update

I've been digging more and it looks like it has something to do with product indexing in Magento. Here's the query that's erroneously selecting 33 products

SELECT `e`.*, 
       `cat_index`.`position` AS `cat_index_position`, 
       `price_index`.`price`, 
       `price_index`.`tax_class_id`, 
       `price_index`.`final_price`, 
       IF(price_index.tier_price IS NOT NULL, Least(price_index.min_price, 
                                              price_index.tier_price), 
       price_index.min_price) AS `minimal_price`, 
       `price_index`.`min_price`, 
       `price_index`.`max_price`, 
       `price_index`.`tier_price` 
FROM   `catalog_product_entity` AS `e` 
       INNER JOIN `catalog_category_product_index` AS `cat_index` 
               ON cat_index.product_id = e.entity_id 
                  AND cat_index.store_id = 3 
                  AND cat_index.visibility IN( 2, 4 ) 
                  AND cat_index.category_id = '6' 
                  AND cat_index.is_parent = 1 
       INNER JOIN `catalog_product_index_price` AS `price_index` 
               ON price_index.entity_id = e.entity_id 
                  AND price_index.website_id = '1' 
                  AND price_index.customer_group_id = 0

I've verified store_id=3 is correct (as well as category_id=6 like I mentioned earlier). If I query the catalog_category_product table directly, it meets expectations, there are 11 products.

SELECT Count(*) 
FROM   catalog_category_product_index 
WHERE  category_id = 6 
       AND store_id = 3; 

However, if I query catalog_category_product_index with the relevant filters from the large query Magento is running, there are 33 products.

SELECT Count(*) 
FROM   catalog_category_product_index 
WHERE  category_id = 6 
       AND store_id = 3 
       AND is_parent = 1 
       AND visibility IN ( 2, 4 );

I reindexed everything under Index Management and that had no effect.

Better Update

There is something definitely spurious going on with the indexing, I've narrowed the issue even further.

Directly selecting from catalog_category_product

SELECT category_id 
FROM   catalog_category_product 
WHERE  product_id = 3445; 

Result

+-------------+
| category_id |
+-------------+
|          10 |
|          13 |
+-------------+

Selecting from catalog_category_product_index

SELECT category_id 
FROM   catalog_category_product_index 
WHERE  product_id = 3445 
GROUP  BY category_id; 

Result

+-------------+
| category_id |
+-------------+
|           2 |
|           6 |
|          10 |
|          13 |
|          14 |
+-------------+

Somehow Magento's catalog indexing process is erroneously saying this product is in categories 2,6 & 14, when in point of fact, it's not! Any idea what could be wrong??

Answer 1

I ended up biting the bullet and digging into the indexing process. I tracked the addition of bogus products into this file

app/code/core/Mage/Catalog/Model/Resource/Category/Indexer/Product.php

which defines class Mage_Catalog_Model_Resource_Category_Indexer_Product which has the method _prepareEnabledProductsVisibility. This is where things were going awry.

It was running this query

SELECT `pw`.`product_id`, 
       IF(pvs.value_id > 0, `pvs`.`value`, `pvd`.`value`) AS `visibility` 
FROM   `catalog_product_website` AS `pw` 
       LEFT JOIN `catalog_product_entity_int` AS `pvd` 
              ON pvd.entity_id = pw.product_id 
                 AND pvd.attribute_id = '102' 
                 AND pvd.store_id = 0 
       LEFT JOIN `catalog_product_entity_int` AS `pvs` 
              ON pvs.entity_id = pw.product_id 
                 AND pvs.attribute_id = '102' 
                 AND pvs.store_id = '3' 
       LEFT JOIN `catalog_product_entity_int` AS `psd` 
              ON psd.entity_id = pw.product_id 
                 AND psd.attribute_id = '96' 
                 AND psd.store_id = 0 
       LEFT JOIN `catalog_product_entity_int` AS `pss` 
              ON pss.entity_id = pw.product_id 
                 AND pss.attribute_id = '96' 
                 AND pss.store_id = '3' 
WHERE  ( pw.website_id = '1' ) 
       AND ( IF(pss.value_id > 0, `pss`.`value`, `psd`.`value`) = 1 )

the purpose of which is to enumerate products and their visibility. However this query was returning products outside of the target store (in this case 3). By adding some more constraints to the WHERE clause I was able to reduce the result set to products within the target store. I did this by adding the following to the chained method invocation which builds the query

->where('pss.store_id=? AND pvs.store_id=?', (int)$storeId, (int)$storeId);

This seems to be working, but I'm guessing (read: hoping) there's just some sort of problem with how I've entered the products and categories into this multi-store installation. After all, it's my first such Magento installation, so perhaps there's something I've overlooked as to how the catalog has been entered.

Answer 2

I have applied the blow code with category id 3 it work perfectly for me

{{block type="catalog/product_list" name="home.catalog.product.list" alias="products_homepage" category_id="3" template="catalog/product/list.phtml"}}

Make sure clearing the cache

Let me know if you have any query