price_index table indexes wrong max_price?

Question

We're currently experiencing a strange behaviour with the price_index table. We're having grouped products with several simple products associated, e.g.

Grouped product

1. Associated product 1 - 25,93€
2. Associated product 2 - 26,96€

For whatever reason the price_index table indicates the max_price as 49,74€ for that particular product. The $this->getLoadedProductCollection() SQL query results in showing what I suspected: http://screens.schloebe.biz/image_2013-06-04_09_32_41_001.png

A re-index didn't help as for indicating the max_price as the actual highest price of all associated products as for my understanding.

Sorry that I can't give you more info at this time, but I'm not getting what might be wrong with the price indexing mechanism (or is there something I don't get?).

I'll help with any info neccessary. Thanks!

Answer 1

Off the top of my head, I don't know how Magento calculates the max_price for a grouped product.

However, the code that does this is located here

#File app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Grouped.php
protected function _prepareGroupedProductPriceData($entityIds = null)
{
    $write = $this->_getWriteAdapter();
    $table = $this->getIdxTable();

    $select = $write->select()
        ->from(array('e' => $this->getTable('catalog/product')), 'entity_id')

    //...big mass of custom logic snipped
}

What I'd do is add some temporary debugging code to this method, and reindex the product prices

Specifically, at the bottom of the method

#File app/code/core/Mage/Catalog/Model/Resource/Product/Indexer/Price/Grouped.php
protected function _prepareGroupedProductPriceData($entityIds = null)
{
    //...
    $query = $select->insertFromSelect($table);
    $write->query($query);

    return $this;        
}

add an echo or some logging above the insertFromSelect call

echo($select->__toString());

Mage::Log($select->__toString());

file_put_contents('/tmp/poor-developers.log', $select->__toString()."\n",FILE_APPEND);

$query = $select->insertFromSelect($table);

I did this for the sample data, and got a query like this

SELECT `e`.`entity_id`, `cg`.`customer_group_id`, `cw`.`website_id`, 
    IF(MIN(i.tax_class_id) IS NULL, 0, MIN(i.tax_class_id)) AS `tax_class_id`, 
    NULL AS `price`, 
    NULL AS `final_price`, 
    MIN(IF(le.required_options = 0, i.min_price, 0)) AS `min_price`, 
    MAX(IF(le.required_options = 0, i.max_price, 0)) AS `max_price`, 
    NULL AS `tier_price`, 
    NULL AS `group_price` 
FROM `catalog_product_entity` AS `e`        
 LEFT JOIN `catalog_product_link` AS `l` ON e.entity_id = l.product_id AND l.link_type_id=3
 CROSS JOIN `customer_group` AS `cg`
 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 = e.entity_id AND pw.website_id = cw.website_id
 LEFT JOIN `catalog_product_entity` AS `le` ON le.entity_id = l.linked_product_id
 LEFT JOIN `catalog_product_index_price_idx` AS `i` ON i.entity_id = l.linked_product_id AND i.website_id = cw.website_id AND i.customer_group_id = cg.customer_group_id
 INNER JOIN `cataloginventory_stock_status` AS `ciss` ON ciss.product_id = e.entity_id AND ciss.website_id = cw.website_id 

 WHERE (e.type_id='grouped') AND (ciss.stock_status = 1) 

 GROUP BY `e`.`entity_id`,
    `cg`.`customer_group_id`,
    `cw`.`website_id`

Although based on extensions you have installed, yours may end up being a different query, so definitely add the debugging code yourself.

Dissecting this query should help point you towards the hows/whys of Magento's grouped product pricing, as well as determine if

1. You missed something obvious,
2. There's a bug with Magento
3. There's a bug with one of your extensions that doesn't take grouped pricing into account

Answer 2

Have a look at this question: https://magento.stackexchange.com/a/93034 .

I have encountered the same indexing problem and that was related to multi-store views for a same store. I solved the problem by adding a group by in SQL clause in Mage_Catalog_Model_Resource_Product_Indexer_Abstract::_addWebsiteJoinToSelect()

Hope that will help.