in my Magento shop should be a page called sale with sub pages including 10% sale, 20% sale, 30% sale …
So now I need to get a product collection with all products that contain at least a special price (final price including price rule discounts would be nicer but if too difficult the special price would be enough for filtering the discounted products).
Well basically I know how I can get a product collection and I know how to set a filter if there is any special price or special price is higher than a specific amount.
But in this case I would need to do something like "$_discountPercentage = round((($_actualPrice-$_convertedFinalPrice)/$_actualPrice)*100);" first and than filter the selection < than 10 or < 20 or < 30 and so on.
So of course I could load the whole collection and just display the matching products within the for each in php but I think this is not necessary because it would load a lot of overhead that is not needed. If this would be just sql I could do this job within 5 minutes. But I do not know how to filter a product collection per discount in percent.
So any tips how I could get a product collection in Magento with products containing a discount higher than 20% of the original price for example?
Thank you very much
Well as often I was faster than the community and solved my problem on my own :) but if anybody is looking for a similar solution … here is the part of code what will do the job.
$_productCollection->getSelect()
->where('ROUND(((price_index.price - price_index.final_price)/price_index.price)*100,0) >= '.$nMinimumDiscountPercentage)
Sure you can cancel the formula. However, this will give you a product collection with all products containing a discount (special price or price rule) at least as high as the specified minimum percentage.
But you may help me with an additional question. I added this over XML by "Custom Layout Update" and a custom .phtml. Now I got (0) products on all sub categories in the sidebar what is no surprise at all.
Is there a easy way to alter this phtml that my custom selection in any sub category is setting the correct catalog count for the parent category? I would like to keep the counter in the sidebar for each sub if possible because I like this feature.
Thank you so far ;)