Automated category sort by sale

Question

I would like to make automated category, where I want to have all catalog products sort by discount price percentage from the highest discount to the lowest.

Could anyone help me?

I tried many extensions and tried to change code, but nothing helped me.

Thank you very much.

Answer 1

This is not a full answer but it can get you on the right track.
There is how you can get all the products that have a discount

$collection = Mage::getModel('catalog/product')->getCollection();
$collection
    ->addAttributeToSelect(Mage::getSingleton('catalog/config')->getProductAttributes())
    ->addMinimalPrice()
    ->addFinalPrice()
    ->addTaxPercents()
    ->addUrlRewrite();

Mage::getSingleton('catalog/product_status')->addVisibleFilterToCollection($collection);
Mage::getSingleton('catalog/product_visibility')->addVisibleInCatalogFilterToCollection($collection);
$collection->getSelect()->where("`price_index`.price > price_index.min_price");

$collection->addExpressionAttributeToSelect('percentage', '(price_index.price - price_index.min_price) * 100 / price_index.price', array());
$collection->getSelect()->order('percentage DESC');

I'm not sure this works for other than simple products, and you might get in trouble if you have prices that are 0.00. So you might want to add to the code above:

$collection->addAttributeToFilter('type_id', 'simple'); // only simple products
$collection->getSelect()->where('price_index.price > ?', 0); //only price bigger than 0;

Now you have 2 options.
Either create a page with a block that renders the collection above. This way you have access real time to the discounted products. The downside is that you don't have the features offered by a category display and you have to implement them yourself (paging, layered navigation, ....).

The second option (I would go with this one) is to have a cron that runs every night (or whenever you want but after the price index is rebuilt) that gets the product collection as described above and assigns all the products in a category and adding their position in the order determined by the percentage discount.

Your cron can look like this:

$collection = ...//see the code above.  
$categoryId = 100; //replace with your category id
$category = Mage::getModel('catalog/category')->load($categoryId);

$products = array();
$position = 1;
foreach ($collection as $item) {
   $products[$item->getId()] = $position;
   $position++;
}
$category->setPostedProducts($products);
$category->save();

Good luck.

Answer 2

It just so happens that I released a module recently (well v2 of an existing module) - aptly named 'Magento Dynamic Category Products', that does exactly this : It can automate category product associations, which includes any product attribute, (thus special price can be used) Additionally it can also get all the products that are discounted via a Catalog Price Rule (at the time of viewing)

and much much more :)

Feel free to experiment via the demo site http://giftpromodemo.proxiblue.com.au/ (admin is under /admin with demo / dem0dem0 for login)

The module is located on my site : http://www.proxiblue.com.au/magento-dynamic-category-products.html