addAttributeToFilter of Magento to get only the products with special price

addAttributeToFilter of Magento to get only the products with special price

August 22, 2026 · Illustrated author avatarBy Stefan Bogdanescu
addAttributeToFilter of Magento to get only the products with special price

Getting Products With Special Prices in Magento

As a senior developer working with clients on Magento projects, I frequently encounter questions about retrieving specific products based on attributes like special price. In this post, we’ll address your client’s request to display only products that have a special price set.

Understanding the Current Code

The provided code is attempting to filter the product collection based on two conditions:

  1. The special_from_date should be before the current date.
  2. Either the special_to_date is after tomorrow or it is null (meaning there’s no end date for the special price).

Let’s break down each part of the code.

$todayDate = date('m/d/y');
$tomorrow = mktime(0, 0, 0, date('m'), date('d') + 1, date('y'));
$tomorrowDate = date('m/d/y', $tomorrow);

$_productCollection = $this->getLoadedProductCollection();

$_productCollection
    ->addAttributeToFilter('special_from_date', array(
        'date' => true,
        'to'   => $todayDate,
    ))
    ->addAttributeToFilter('special_to_date', array(
        'or' => array(
            0 => array('date' => true, 'from' => $tomorrowDate),
            1 => array('is' => new Zend_Db_Expr('null')),
        ),
    ), 'left');

The Issue and the Solution

The issue with your code is that the filter conditions are not being applied correctly. In particular, the addAttributeToFilter method requires the date format to be in a specific format (timestamp or MySQL DATE string).

To fix this:

  1. Change the date formats to use the timestamp.
  2. Use the correct field types for each attribute.

Here’s the corrected code:

$todayDate = strtotime(date('m/d/y'));
$tomorrowDate = strtotime(date('m/d/y', strtotime('+1 day')));

$_productCollection = $this->getLoadedProductCollection();

$_productCollection
    ->addAttributeToFilter('special_from_date', array(
        'date' => true,
        'to'   => $todayDate,
    ))
    ->addAttributeToFilter('special_to_date', array(
        'or' => array(
            0 => array('null' => true),
            1 => array('datetime' => true, 'from' => $tomorrowDate),
        ),
    ), 'left');

Best Practices and Additional Considerations

While the corrected code should now filter the products correctly, there are a few additional best practices to keep in mind:

  • Use Indexes: Ensure that your special_from_date and special_to_date attributes have proper indexes for better performance.
  • Product Reindexing: After making changes to special prices, remember to reindex your product data.
  • Caching: If you’re working with a large number of products or facing performance issues, consider implementing caching strategies.

Conclusion

In this post, we’ve addressed the challenge of filtering Magento products based on special price attributes. By understanding the proper use of the addAttributeToFilter method and ensuring the correct date formats, you can successfully retrieve only the products with active special prices.

Remember to keep your store’s product data up-to-date, maintain proper indexing, and consider optimizing performance through caching as needed.

If you’d like to explore more advanced Magento customizations or need expert assistance, feel free to reach out to our team at Laravel Company. We’re always ready to help with your e-commerce development needs!

Catalog Database Pricing Errors

Our network