I finally figured out what was wrong in indexer.
You put me on the right way by indicating that the problem was related to multi-websites.
The price variation will be applied as many times there are websites (minus the website code admin
, which has the website_code
0). E.g: if you have two websites (other than admin
) and set a price variation of 5, then the variation will be 10 instead 5. If you have three websites, then the variation will be 15, etc.
The problem also affect the max_price
column.
I looked back into older versions of Magento, and I found that the bug was not present in CE 1.8.0.0 (and may be some of other newer versions between 1.8.x and 1.9.x).
So, after making a diff between files, I found some additions, on latest CE version, in app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Configurable.php ; the offending lines are:
$this->_addWebsiteJoinToSelect($select, true);
$this->_addProductWebsiteJoinToSelect($select, 'cw.website_id', 'le.entity_id');
As you can guess, those lines are doing some joins to website
/store_group
/store
tables, and Mage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect()
is precisely responsible of the "bug":
protected function _addWebsiteJoinToSelect($select, $store = true, $joinCondition = null)
{
if (!is_null($joinCondition)) {
$joinCondition = 'cw.website_id = ' . $joinCondition;
}
$select->join(
array('cw' => $this->getTable('core/website')),
$joinCondition,
array()
);
if ($store) {
$select->join(
array('csg' => $this->getTable('core/store_group')),
'csg.group_id = cw.default_group_id',
array())
->join(
array('cs' => $this->getTable('core/store')),
'cs.store_id = csg.default_store_id',
array());
}
return $this;
}
If you look at the generated SQL select query at this state, it will look like this:
SELECT `l`.`parent_id`, `l`.`product_id`, `i`.`customer_group_id`, `i`.`website_id`
FROM `catalog_product_index_price_final_idx` AS `i`
INNER JOIN `catalog_product_super_link` AS `l` ON l.parent_id = i.entity_id
INNER JOIN `catalog_product_super_attribute` AS `a` ON l.parent_id = a.product_id
INNER JOIN `catalog_product_entity_int` AS `cp` ON l.product_id = cp.entity_id AND cp.attribute_id = a.attribute_id AND cp.store_id = 0
LEFT JOIN `catalog_product_super_attribute_pricing` AS `apd` ON a.product_super_attribute_id = apd.product_super_attribute_id AND apd.website_id = 0 AND cp.value = apd.value_index
LEFT JOIN `catalog_product_super_attribute_pricing` AS `apw` ON a.product_super_attribute_id = apw.product_super_attribute_id AND apw.website_id = i.website_id AND cp.value = apw.value_index
INNER JOIN `catalog_product_entity` AS `le` ON le.entity_id = l.product_id
CROSS JOIN `core_website` AS `cw`
INNER JOIN `core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
INNER JOIN `core_store` AS `cs` ON cs.store_id = csg.default_store_id
INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = le.entity_id AND pw.website_id = cw.website_id
WHERE (le.required_options=0)
GROUP BY `l`.`parent_id`,
`i`.`customer_group_id`,
`i`.`website_id`,
`l`.`product_id`
Did you see the CROSS JOIN core_website AS cw
part? It is risky to do that, as a CROSS JOIN
(which is, in MySQL, a strict alias of INNER JOIN
) without joining condition will do the join for each line of the joined table. The number of results is mathematically the product of the number of lines from the source table by the number of lines from the target table. E.g: if you had 2 lines in the source table, and 3 lines in the target table, then a CROSS JOIN
would give you 6 lines (2 x 3).
In our case, it could be okay, as we want a product for all websites and calculate its price for each website. But, that work is in fact already done by Mage_Catalog_Model_Resource_Product_Indexer_Price_Default::_prepareFinalPriceData
, which puts a line for each product and website in catalog_product_index_price_final_idx
table and is called just before Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption()
.
Here are the facts: if no $joinCondition
is specified as third parameter of Mage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect()
, it will result in a CROSS JOIN
without join condition, which is incoherent because we have already a website_id
in catalog_product_index_price_final_idx
table. Then... Guess the result: your calculated min_price
/max_price
is affected by the fact that a same variation will be duplicated as many times there are websites.
To solve the issue, we have to restore coherence between website_id
column of table catalog_product_index_price_final_idx
and the same one of core_website
table. In Mage_Catalog_Model_Resource_Product_Indexer_Price_Configurable::_applyConfigurableOption()
, specify the join condition in _addWebsiteJoinToSelect()
method:
$this->_addWebsiteJoinToSelect($select, true, 'i.website_id');
In SQL, the join will result to:
SELECT `l`.`parent_id`, `l`.`product_id`, `i`.`customer_group_id`, `i`.`website_id`
FROM `catalog_product_index_price_final_idx` AS `i`
INNER JOIN `catalog_product_super_link` AS `l` ON l.parent_id = i.entity_id
INNER JOIN `catalog_product_super_attribute` AS `a` ON l.parent_id = a.product_id
INNER JOIN `catalog_product_entity_int` AS `cp` ON l.product_id = cp.entity_id AND cp.attribute_id = a.attribute_id AND cp.store_id = 0
LEFT JOIN `catalog_product_super_attribute_pricing` AS `apd` ON a.product_super_attribute_id = apd.product_super_attribute_id AND apd.website_id = 0 AND cp.value = apd.value_index
LEFT JOIN `catalog_product_super_attribute_pricing` AS `apw` ON a.product_super_attribute_id = apw.product_super_attribute_id AND apw.website_id = i.website_id AND cp.value = apw.value_index
INNER JOIN `catalog_product_entity` AS `le` ON le.entity_id = l.product_id
INNER JOIN `core_website` AS `cw` ON cw.website_id = i.website_id
INNER JOIN `core_store_group` AS `csg` ON csg.group_id = cw.default_group_id
INNER JOIN `core_store` AS `cs` ON cs.store_id = csg.default_store_id
INNER JOIN `catalog_product_website` AS `pw` ON pw.product_id = le.entity_id AND pw.website_id = cw.website_id
WHERE (le.required_options=0)
GROUP BY `l`.`parent_id`,
`i`.`customer_group_id`,
`i`.`website_id`,
`l`.`product_id`
Ok, our join now looks like this: INNER JOIN core_website AS cw ON cw.website_id = i.website_id
.
Then... That's it. Problem solved.
Hope it will help some people. And don't expect Magento to correct the problem, as the version 2 is now out and that there will be probably only security updates in the future...
Painfully debugged Magento core code (log all queries and reviewed step-by-step), the issue is in:
vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php
line 192
$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 0, null, true);
The fourth parameter to Magento\Catalog\Api\Data\ProductInterface\AbstractIndexer::_addAttributeToSelect()
is supposed to the the store_id for the (non-default) price to join from the attribute "decimal" value table, instead Magento passes the hard-coded "0", which is causing default store (website) prices to be put in product price index for configurable products, for any website_id.
Quick&Dirty Solution:
Replace in vendor/magento/module-configurable-product/Model/ResourceModel/Product/Indexer/Price/Configurable.php
line 192
:
$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 0, null, true);
with:
$select->join(
['sg' => $this->getTable('store_group')],
'sg.website_id = i.website_id',
[]
);
$priceColumn = $this->_addAttributeToSelect($select, 'price', 'l.product_id', 'sg.default_store_id', null, true);
This way, Magento will select the price for non-default websites from the correct associated value for the default store in those websites, instead of statically assigning the default website price to each result.
Don't forget to reindex:
php bin/magento indexer:reindex catalog_product_price
Proper solution:
Create a custom module and overload the
Magento\ConfigurableProduct\Model\ResourceModel\Product\Indexer\Price\Configurable
class with your own modified version (see Quick&Dirty Solution) using DI preference ({your module}/etc/di.xml).
Don't forget to specify that your module depends on Magento_ConfigurableProduct module in your etc/module.xml and composer.json files.
Best Answer
I had some issue with wrong / old product price in the cart which was not the one displayed to the customer on the product page as well. Running Magento 1.9.2.1.
I was surprised finding out that Magento obviously does not use information from the product price index but from the article flat index instead. Don't know if bug or feature, but it can lead to unclear situations for sure.
If you have a look at the
catalog_product_flat
tables in the database you will find 2 fields containing the main price information: price and special price. Those are being used for cart, quotes and order.So updating the price index manually could not solve our problem. But we had article flat index switched on manual mode and updating it finally made the right price to show up in the cart - even in existing quotes.
Maybe this helps to solve your issue as well.