I have a problem and can't find any solution since hours.
When I'm viewing a Category and click through the pages sometimes a product is showing multiple times e.g. on Page 1 and Page 2 – exactly the same article (sku).
So far I could discover this behavior with sort order: by name.
I think the problem is that every execution of "ORDER BY name" returns a different subset of products because of many articles names a pretty much identical.
But I'm not sure if this is really the problem.
I deactivated all extension in community and local and reactivate the default Template … the problem still exist (all indexes are refreshed).
Hope someone can help.
Many thanks
* Update 18.06.2013 *
Following the SQL-Query from Page 1 and Page 2
# Page 1
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`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` 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=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='130' 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 `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '65') AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '65') AND (`at_name`.`store_id` = 1) ORDER BY `name` asc LIMIT 5
Returning the following entity_id's
44419
36243
36235
29435
36386 [In Frontend 36330]
The Article with entity_id 36386 is "Bugatti Herren eleganter Schnürschuh" with SKU: 650243 which is also in the Category.
There are 9 configurable products with this exact same name. (sku: 640735, 650243, 650244, 650245, 650246, 650248, 650249, 650250, 650251)
No idea why. The products are imported via 3rd-Party Interface. But can this be a problem for Magento/Pagination having multiple articles with the same name?
# Page 2
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`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name` 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=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id='130' 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 `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '65') AND `at_name_default`.`store_id` = 0 LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '65') AND (`at_name`.`store_id` = 1) ORDER BY `name` asc LIMIT 5 OFFSET 5
Returning the following entity_id's
36330
36584
36354
28324
29175 [In Frontend 29183]
The Last Product in Query isn't the same as in the frontend.
* Update2 18.06.2013 *
Ok I executed the Page 1 SQL-Statement a few times now and the result differs:
Many-Times I get this:
44419
36243
36235
29435
36386
But Sometimes that:
44419
36235
36243
29435
36354
If I carry on I think more combinations would be possible.
The ID's changed in that list are products with exacly the same name.
Could it be the problem that "ORDER BY name" couldn't get a unique order because name's are repeated so the order is a bit change? But if this is the problem two things are curious.
1) All articles with the same name (and in this category etc.) must listet together and not this OR that article.
2) This would be a known problem.
I hope someone can clear up this strange behaviour.
Best Answer
If the product names aren't the same, and you only are sorting by name, the order of the products with the same name is undefined.
A simple solution might be to enable the flat product catalog of Magento under
After reindexing, Magento will use the flat table for the store view on product listing pages on the frontend.
It might be that solves the issue already for you.
If it doesn't solve your problem, try adding another sorting criteria, for example the
entity_id.You can do that using an event observer for the
catalog_product_collection_load_beforeevent. ($observer->getCollection()->setOrder('entity_id', 'ASC');).After that, the order will be defined, even if the product names are the same.