1

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

Can anyone show me how can i achieve that ?

enter image description here

2

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)

0

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);
  • This is for Magento 1 – Gediminas Sep 12 '17 at 12:15
  • yes, that is for M1. do you want that function for m2? – Trang Lee Sep 13 '17 at 10:49
  • Not yet, but you may write it for the future :) – Gediminas Sep 13 '17 at 13:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.