Get a Magento product collection by special price in percentage

Get a Magento product collection by special price in percentage

September 27, 2026 · Illustrated author avatarBy Stefan Bogdanescu
Get a Magento product collection by special price in percentage

Filtering Magento Product Collection by Discount Percentage

In this post, we’ll demonstrate how to efficiently filter a product collection in Magento based on the percentage of discount applied to each item. This is particularly useful when you want to create pages displaying products with specific discount ranges, such as “10% Sale”, “20% Sale”, etc.

Understanding the Challenge

The key challenge here lies in transforming the special price into a percentage of the original price and then applying this filter without loading unnecessary data. While it’s technically possible to load every product and perform the calculation within PHP, this approach would indeed introduce significant overhead.

Fortunately, Magento provides powerful tools for querying products with applied discounts directly from the database, allowing us to avoid excessive memory usage.

Getting Started

Let’s begin by setting up a basic collection of all saleable products:

$collection = Mage::getModel('catalog/product')->getCollection();
$collection->addAttributeToFilter('status', array('eq' => Mage_Catalog_Model_Product_Status::STATUS_ENABLED));

Calculating Discount Percentage

Next, we need to calculate the discount percentage for each product in the collection. Magento provides the getPrice and getFinalPrice methods, which return the original price and the final price after discounts respectively.

We can create a new attribute set that calculates this percentage:

$collection->addAttributeToFilter(
    'special_price_percent',
    array(
        'field' => new Zend_Db_Expr('ROUND((price - final_price) / price * 100, 2)')
    )
);

In this code snippet:

  • special_price_percent is a hypothetical attribute that calculates the percentage using the formula (original_price - discounted_price) / original_price.
  • The Zend_Db_Expr class allows us to insert SQL expressions directly into the query.
  • We round the result to two decimal places for accuracy.

Filtering by Discount Percentage

Now we can easily filter products based on their discount percentage. Let’s say we want to get all products with a discount greater than 20%:

$twenty_percent_discount = $collection->addAttributeToFilter(
    'special_price_percent',
    array('gt' => 20)
);

This query will return only the products that have a special price discount exceeding 20%. You can replace 20 with any desired percentage value.

Fetching Relevant Data

Finally, to ensure we don’t load unnecessary information, we should select only the required attributes:

$twenty_percent_discount->getSelect()->reset(Zend_Db_Select::COLUMNS)
    ->columns(array(
        'entity_id',
        'name',
        'short_description',
        'price',
        'final_price'
    ));

In this example, we’re selecting the product ID (entity_id), name, short description, original price, and final price. You can customize these columns based on your needs.

Conclusion

By leveraging Magento’s built-in capabilities for querying products with applied discounts and using SQL expressions to calculate percentage values, we’ve demonstrated an efficient way to filter a product collection based on the discount percentage without excessive memory usage.

Remember that this approach assumes you have a single store view configuration. If you’re dealing with multiple store views, ensure your attribute set is configured correctly across all views.

For more advanced Magento development techniques and best practices, consider exploring Laravel Company, a leading provider of expert services for Laravel and e-commerce projects, including Magento upgrades and migrations.

Catalog Database Configuration Pricing

Our network