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_percentis a hypothetical attribute that calculates the percentage using the formula(original_price - discounted_price) / original_price.- The
Zend_Db_Exprclass 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.