Get all products that has special price and special price < price

Question

I want to get all products that has special price and special price < price.

Can anyone show me how can i achieve that ?

Answer 1

The best way I can think of doing this is via an SQL statement

SELECT cpeid1.entity_id, cpeid1.value as price, cpeid2.value as special_price FROM catalog_product_entity_decimal cpeid1 LEFT JOIN catalog_product_entity_decimal cpeid2 ON (cpeid1.entity_id = cpeid2.entity_id AND cpeid2.attribute_id = 76) WHERE cpeid1.attribute_id = 75 AND cpeid2.value IS NOT NULL ;

Where 75 = price attribute id and 76 = special_price attribute id (from the eav_attribute table)

Answer 2

I think you can use this code

        $_productCollection = Mage::getModel('catalog/product')->getCollection();
    $_productCollection->addAttributeToSelect(array(
        'image',
        'name',
        'short_description'
    ))->addFieldToFilter('visibility',
        array(Mage_Catalog_Model_Product_Visibility::VISIBILITY_BOTH, Mage_Catalog_Model_Product_Visibility::VISIBILITY_IN_CATALOG))
        ->addFinalPrice()
        ->addAttributeToSort('price_index.price', 'asc')
        ->getSelect()
        ->where('price_index.final_price < price_index.price')
        ->limit(30);