Wrong products show up on substore main page ???

[John Ploeg]

We use Magento ver. 1.9.3.6

We have multiple subsores in our shop
In substore with store_id=3 products must be shown from cat_id=233
In CMS page I added:
{{block type="catalog/product_list" name="home.catalog.product.list" alias="products_homepage" category_id="233" template="catalog/product/list.phtml"}}

But the substore home page is also showing products from other categories??
I disabled all cache and enabled SQL logging.

This is the SQL Magento produces:

SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `cat_index`.`position` AS `cat_index_position`, `e`.`name`, `e`.`description`, `e`.`short_description`, `e`.`price`, `e`.`special_price`, `e`.`special_from_date`, `e`.`special_to_date`, `e`.`weight`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`image`, `e`.`small_image`, `e`.`thumbnail`, `e`.`color`, `e`.`color_value`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`status`, `e`.`url_key`, `e`.`visibility`, `e`.`required_options`, `e`.`image_label`, `e`.`small_image_label`, `e`.`thumbnail_label`, `e`.`msrp_enabled`, `e`.`msrp_display_actual_price_type`, `e`.`msrp`, `e`.`tax_class_id`, `e`.`price_type`, `e`.`weight_type`, `e`.`price_view`, `e`.`shipment_type`, `e`.`links_purchased_separately`, `e`.`links_exist`, `e`.`ean`, `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`, `stock_status`.`stock_status` AS `salable`, `at_bestsellers`.`bestsellers`
FROM `catalog_product_flat_3` 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 = '233' 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

LEFT JOIN `cataloginventory_stock_status` AS `stock_status` ON
e.entity_id = stock_status.product_id AND
stock_status.website_id=1

LEFT JOIN `am_sorting_bestsellers` AS `at_bestsellers` ON
(at_bestsellers.`id`=e.entity_id) AND (at_bestsellers.store_id = '3') WHERE
(e.status = 1) ORDER BY `salable` desc, `bestsellers` desc, `at_bestsellers`.`bestsellers` DESC LIMIT 12

Now I took a faulty product that was showing on the page:
it has entity_id 3601

Now when I do
SELECT * FROM `catalog_category_product_index` WHERE `product_id` = 3601 AND `store_id` = 3
Then I get 3 results with one result in category 233 ?? (other 2 correct for this product)
In the backoffice I checked this product, and only 2 categories are checked.

How is this possible??

When I look in `catalog_category_product` there the correct results (for that product) are given.

It seems `catalog_category_product_index` has faulty entries...
I re-indexed all products, without any success

Can anyone give me some guidance how to solve this problem?

Kind regards
John

[Tom Robertshaw]

I believe this would be the "category products" index in admin that refreshes this table. It's unlikely that incorrect categories would be added in. Are you able to double check that the product isn't assigned to category ID 233 in admin (or any subcategory of this category)? 

[John Ploeg]

Dear Tom,

Thanks for your reply.

I double checked the product with ID 3601. And it is only in 2 categories.

In table catalog_category_product it is correct shown

But Magento uses catalog_category_product_index in the SQL to select the products for this substore (see my problem description)

I tried many things now (removal of contraints in table catalog_category_product_index, deleting disabling cache and "flat table product" and then reindexing via SSH  (php shell/indexer.php reindex all)  all without succes. 

I cannot figure it out...

So if you have any pointers...

Kind regards

John

[John Ploeg]

The problem is that products appear in the extra ROOT categories (created for the substores)

[Tom Robertshaw]

To be honest, I'm not sure whether it's normal or not for products to be put into a root category for a store that they in. That might be expected. Whether or not it's meant to, this seems like it made be a distraction from the core issue:

The issue was when you selected category 233, products from outside of that category (and store) were shown. Is that correct? And 233 is a root category?

I think we should go back to the products that are showing that we believe aren't in the category that you've selected and trace why they're being pulled in. Let me know if I've misunderstood.