13

Basically, I've created a category 'Products on Sale', which I would like to automatically contain all products in my catalog that have had a Special Price applied to them (via Catalog > Manage Products). I would like the page to retain the layered navigation and sorting capabilities that a standard Magento Category page contains.

It seems like this is something that would be valuable to a majority of Magento users, and I'm surprised it is not included as part of the core functionality.

I've tried over a dozen Stack Exchange answers, blog posts, and forums, and nothing has worked thus far. Does anyone have a programmatic solution for this?

=== EDIT ===

Based on @pspahn's critique in the comments below, I've decided to pursue an alternate method of achieving similar functionality. With that said, if you are interested in pursuing this line, @sander-mangel describes a method which seems entirely feasible.

5
  • It seems to me that the requirement of "automatically contain all special price products" is a bit excessive. I assume the end result is that you want a page with special price products on it and have it look like a category page. Seems you could instead just create your own model/collection (based on products with a special price) and use that collection in template(s) based on the category view pages. I just don't see a category that gets populated automatically as a good solution, for example, how do you prevent a user from modifying it?
    – pspahn
    Dec 11, 2013 at 20:45
  • @pspahn Thank you for your response. I understand your critique and agree with your reasoning. I could utilize the method described by Sander Mangel with an additional category_save_after observer for good measure, but that seems like overkill. I'll go forward with an alternate solution.
    – rokkor
    Dec 11, 2013 at 23:00
  • @pspahn - I don't understand what you mean with 'how do you prevent a user from modifying it' - can you explain a bit further?
    – ProxiBlue
    Feb 10, 2014 at 5:13
  • @ProxiBlue Basically, if you create a category and populate with products automatically, an admin user can simply go into that category in the backend and add/remove products manually.
    – pspahn
    Feb 11, 2014 at 14:48
  • @pspahn ok, with user I misunderstood as front-end user.
    – ProxiBlue
    Feb 11, 2014 at 14:53

4 Answers 4

9

Easiest way to do this is to create a custom extension that works with an Observer and a Cronjob.

Create a category for the sales products. This way you can use the normal Magento functionalities in the frontend like layered navigation etc.

To get the products in this category automatically we will use the observer and cronjob. The observer will observe the catalog_product_save_after event that is triggered when a product is saved in the backend. When this happens you can check the special_price, special_price_from date and special_date_to date to determine if you need to put the product in the sales category or remove it from there.

The cronjob is there for the special from and to dates. Every night after midnight first empty the sales category of all the products. Then use a collection to retrieve all products that have a special price and are falling within the special from and to date. If so move them to this sales category.

7

The solution offered by @SanderMangel is top-notch. I can help expand on this with some code, which I currently use in my module Automated / Dynamic Category products - which has the ability to do Category rules of products on special

The code adjusts a standard product collection to get all the products with a special price set, on the day the code runs. You can use this in the cron to re-populate the categories at 00:00 and make sure they stay updated.

Note that the code is extracted from a larger module, thus I had compacted the relevant parts here for you. There may be a variable or two that is not represented in this extract, but they would be easy to deduce, or just ask :)

The $category object is the actual category that is to contain the products. The code below will also allow you to specify the discount in a % value as well :)

$collection = $category->getProductCollection();

$todayDate = Mage::app()->getLocale()->date()->toString(Varien_Date::DATE_INTERNAL_FORMAT);
$collection->addAttributeToFilter(array(
    array(
        'attribute' => "special_to_date",
        'null' => true
    ),
    array(
        'attribute' => "special_to_date",
        'from' => $todayDate,
        //'to'      => $todayDate,
        'date' => true
    )
));
$collection->addAttributeToFilter(array(
    array(
        'attribute' => "special_from_date",
        'null' => true
    ),
    array(
        'attribute' => "special_from_date",
        //'from'    => $todayDate,
        'to' => $todayDate,
        'date' => true
    )
));

$collection->addAttributeToSelect('special_price','left');
$collection->addAttributeToSelect('price','left');
$select = $collection->getSelect();

if (strpos($value, '%') > 0) {
    $value = str_replace('%', '', $value);
    $select->where('( 100 - (( at_special_price.value * 100 ) / at_price.value ) )  ' . $operator . ' ' . $value);
} else {
    $select->where('((at_price.value - at_special_price.value)) ' . $operator . ' ' . $value);
}

Now, to note is that the collection will not return products, as it contains links to the normal catalog <-> product link tables. Since you are not interested in the current linked products, you need to clear that table relation out of the collection.

I use the following code to get that done:

/**
 * Remove Catalog Product Link elements from collection
 * 
 * @param type $collection
 * @return type
 */
public function removeCatProPart($collection)
{
    $select = $collection->getSelect();
    $fromPart = $select->getPart(Zend_Db_Select::FROM);
    $select->reset(Zend_Db_Select::FROM);

    if (array_key_exists('cat_pro', $fromPart)) {
        unset($fromPart['cat_pro']);
        // also remove any reference to the table in the rest of the query
        $columns = $select->getPart(Zend_Db_Select::COLUMNS);
        $columnRemoved = false;
        foreach ($columns as $columnKey => $column) {
            if ($column[0] == 'cat_pro') {
                unset($columns[$columnKey]);
                $columnRemoved = true;
            }
        }

        if ($columnRemoved) {
            $select->setPart(Zend_Db_Select::COLUMNS, $columns);
        }

        $orderPart = $select->getPart(Zend_Db_Select::ORDER);
        $orderRemoved = false;
        foreach ($orderPart as $orderKey => $order) {
            if ($order[0] == 'cat_pro') {
                unset($orderPart[$orderKey]);
                $orderRemoved = true;
            }
        }

        if ($orderRemoved) {
            $select->setPart(Zend_Db_Select::ORDER, $orderPart);
        }
    }
    $select->setPart(Zend_Db_Select::FROM, $fromPart);
    return $collection;
}

as an added bonus, you can use the same teqnique in adjusting the catalog product collection, and find products that are in special mode due to catalog rules:

$storeDate = Mage::app()->getLocale()->storeTimeStamp($this->getStoreId());
$value = $this->getValue();
$conditions = 'price_rule.product_id = e.entity_id AND ';
$conditions .= "(from_time = 0
    OR from_time <= " . $storeDate . ")
    AND (to_time = 0
    OR to_time >= " . $storeDate . ") AND ";
$conditions .= "price_rule.rule_id IN (" . $value . ")";
$collection->getSelect()->joinInner(
        array('price_rule' => $collection->getTable('catalogrule/rule_product')), $conditions);
$collection->setFlag('applied_catalog_rule_id', true);
$collection->setFlag('applied_rule', true);

Once you have the working collection, all you need to do is get all the ids from the collection, flip the array, and use $category->setPostedProducts($products); and a $category->save()l; to complete the update.

For completeness, here is my daily cron that keeps the dynamic categories up-to-date. (again, it refers to methods not included here, but I am sure it will get you in the right direction

Have fun :)

public static function rebuildAllDynamic($schedule)
{
    try {
        $tempDir = sys_get_temp_dir() . "/";
        $fp = fopen($tempDir . "dyncatprod_rebuild.lock", "w+");
        if (flock($fp, LOCK_EX | LOCK_NB)) {
            if (Mage::getStoreConfig('dyncatprod/debug/enabled')) {
                   mage::log("DynCatProd - rebuildAllDynamic");
            }
            if (!Mage::getStoreConfig('dyncatprod/rebuild/max_exec')) {
                ini_set('max_execution_time', 3600); // 1 hour
            }
            $categories = Mage::getModel('catalog/category')
                ->getCollection()
                ->addAttributeToSelect('*')
                ->addIsActiveFilter()
                ->addAttributeToFilter('dynamic_attributes', array('notnull' => true));

            foreach ($categories as $category) {
                $products = Mage::helper('dyncatprod')->getDynamicProductIds($category);
                if (is_array($products)) {
                    if (Mage::getStoreConfig('dyncatprod/debug/enabled')) {
                        mage::log("rebuilding :" . $category->getName() . ' ' . $category->getPath() );
                    }
                    $products = array_flip($products);
                    $category->setPostedProducts($products);
                    $category->setIsDynamic(true);
                    $category->save();
                }
            }
            flock($fp, LOCK_UN); 
            unlink($tempDir . "dyncatprod_rebuild.lock");
        } else {
            mage::log('Could not execute cron for rebuildAllDynamic -file lock is in place, job may be running');
        }
    } catch (Exception $e) {
        flock($fp, LOCK_UN); 
        unlink($tempDir . "dyncatprod_rebuild.lock");
        mage::logException($e);
        return $e->getMessage();
    }
}

ref: http://www.proxiblue.com.au/magento-dynamic-category-products.html

3

Here is the collection which should give you the resultset of all the special price products in your catalog which you can display on one page

$collection = Mage::getResourceModel('catalog/product_collection')
    ->addAttributeToSelect('price')
    ->setStoreId($this->getStoreId());

$date = strtotime(date('Y-m-d')); $current_date = date("Y-m-d hh:mm:ss",$date);
    
$collection = $collection
    ->addAttributeToFilter('price',
        array('gt'=>0))
    ->addAttributeToFilter('visibility',
        array('neq'=>Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE));
                                        
if (Mage::getStoreConfigFlag(Mage_Catalog_Helper_Product_Flat::XML_PATH_USE_PRODUCT_FLAT, $this->getStoreId())){
    $collection = $collection->addAttributeToFilter('special_price',array('lt'=>new Zend_Db_Expr('e.price')));
}
else{
    $collection = $collection->addAttributeToFilter(array(
        array('attribute'=>'special_price','lt'=>new Zend_Db_Expr('at_price.value'))
    ));
}

$collection = $collection->addAttributeToFilter(array(
        array('attribute'=>'special_from_date','lteq'=>$current_date),
        array('attribute'=>'special_from_date','eq'=>''),
        array('attribute'=>'special_from_date','null'=>true)
    ),'','left')
    ->addAttributeToFilter(array(
        array('attribute'=>'special_to_date','gteq'=>$current_date),
        array('attribute'=>'special_to_date','eq'=>''),
        array('attribute'=>'special_to_date','null'=>true)
            ),'','left');

$collection->getSelect()->group('e.entity_id');

return $collection;

There are several ways you can do this, either create a new module that has its own controller, block, and model very similar to the Mage category module or you override the Mage category module to only run the above collection when the special category is selected by the customer. This can be easily configured in the system -> configuration of your module.

If you can spend few quid then I would recommend the following extension on Magento connect

For Magento 1 -:

http://www.magentocommerce.com/magento-connect/dynamic-sale-category.html (http://www.scommerce-mage.co.uk/magento-dynamic-sale-category.html)

For Magento 2 -:

https://www.scommerce-mage.com/magento2-dynamic-sale-category.html

Hope it helps!

Cheers S

0

I created an extension for Magento 2 which displays products on special under it's own controller with layered navigation. So no need for category or cron.

https://github.com/DominicWatts/Special

Hope this helps someone

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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