Magento – Magento special price is showing zero by default

it took me a while to figure this out. Should work for you, if you customy it for your needs.

To list products affected by a price rule, you must make your way over the rule-model.

Here is the code:

<?php
// we need the date to filter for the special price
    $dateToday = date('m/d/y'); 
    $tomorrow = mktime(0, 0, 0, date('m'), date('d')+1, date('y'));
    $dateTomorrow = date('m/d/y', $tomorrow);

// this gets the product collection and filters for special price;
// grabs products with special_from_date at least today, and special_to_date at least tomorrow
        $_productCollection = Mage::getModel('catalog/product')->getCollection()
            ->addAttributeToFilter('special_from_date', array('date' => true, 'to' => $dateToday))
            ->addAttributeToFilter('special_to_date', array('date' => true, 'from' => $dateTomorrow))
            ->load();

// this loads the standard magento catalog proce rule model
        $rules = Mage::getResourceModel('catalogrule/rule_collection')->load();

// read: if there are active rules
        if($rules->getData()) {
            $rule_ids = array(); // i used this down below to style the products according to which rule affected
            $productIds[] = array(); // this will hold the ids of the products

            foreach($rules as $rule) {
                $rule_ids[] = $rule->getId();
                $productIds = $rule->getMatchingProductIds(); // affected products come in here
            }

// merge the collections: $arr is an array and keeps all product IDs we fetched before with the special-price-stuff
            $arr = $_productCollection->getAllIds();
            if($productIds) {
                // if there are products affected by catalog price rules, $arr now also keeps their IDs
                $arr = array_merge($arr,$productIds);
            }

// we initialize a new collection and filter solely by the product IDs we got before, read: select all products with their entity_id in $arr
            $_productCollection = Mage::getModel('catalog/product')->getCollection()
                ->addAttributeToFilter('entity_id',array('in'=>$arr))
                ->load();
        }

== UPDATE ==

I added some comments to the code and renamed $skus to $productIds (which fits better).

If you are still getting errors, please let me know what's exactly happening. This code is tested for Magento v1.7.0.2.

Cheers
Simon

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.