Magento2 filter by "sales products" on a category list page

Question

I'd like to add a filter in categories lists to display only products on sales. For now I use a filtered attribute like "marketing" and I add manually all products in sales define by a Catalog Rule.

Is there a way to programmatically check with a cron if a rule exist and add the "sales" value for the "marketing" attribute to products that match this rule?

Or maybe add a custom filter?

Answer 1

Ofcourse you can create a cron job which allows you to match the products in catalog price rules table i.e. catalogrule_product using the following select statement

select distinct product_id from catalogrule_product

For the above selected products you can set 'marketing' attribute to whatever value you want to show.

The above way solves the problem you want to solve but it is not an elegant way to show your customers sale products because they need to browse through each and every category to find sale products. I would rather create a sale or offer category and show sale products in one place.

There are several extensions which allows you to do that but for our site we have used the following extensions

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

Hope it helps. Thanks