"Only discounted products" Virtual category rule is not reading "special from and to dates" in discounted products to load products

[indunie]

Preconditions

Magento ver. 2.4.6-p7 and ElasticSuite Open Source ver. 2.11.9 (on elasticsearch ver. 8.15.1).
in both production and developer modes

Steps to reproduce

1. Set up a discount for a product with a future date in the Special Price From and To fields.
2. Create a virtual category with the virtual rule "Only discounted products".
3. Verify if the product remains in your virtual category after the date in Special Price From has passed.
4. Wait until the end of the sale period (as defined in the To field), and confirm that the product is no longer in your virtual category.

Expected result

1. The product should appear in the category after the Special Price From date and disappear from the category after the To date

Actual result

1. Discounted product is not disappear after the "special to date" passed.
   
   It seems like Special Price From and To are not being used to verify if a discount is active.
   
   

The below issue is related to this but I couldn't find it as resolved.
#2928

[vahonc]

Hello @indunie,

I tried to reproduce your issue according to your steps. So, I create a virtual category with the virtual rule Only discounted products and set up a discount for some products (in my case three first) with a future date in the Special Price From and To fields, as from 8 to 11 November.

Indeed, when the "special to date" passed the discounted products did not disappear from the virtual category and the prices didn't change in the database. But after running reindex this issue was fixed, the products disappeared from the category, and catalog_product_index_price table was updated.

So you may need to check the Magento cron job to make sure that the price index updating is configured correctly.

Also, clarify what exactly version of Magento you are using, I mean OS, CE or EE.

BR,
Vadym

[indunie]

Hello @vahonc ,

Thank you for checking the issue.

The version I am using is Magento CE 2.4.6-p7.

Reindexing crons are working well, but it doesn't fix the issue.

[vahonc]

@indunie,

Reindexing crons are working well, but it doesn't fix the issue.

Have you tried to run a manual re-index?

This is very strange because I couldn't reproduce your issue on the fresh Magento, at least it was fixed by manual re-index, and products with the passes "special to date" disappeared from the virtual category.

Could you provide more details about your Magento instance, especially if you are using any third-party extension?

Also please provide an extract of the queries that are being sent to ES when you are browsing categories. You can get it from the system.log file if you activate the Debug mode of Elasticsuite in: Stores -> Configuration -> Elasticsuite -> Base settings -> Elasticsearch Client -> Enable Debug Mode.

BR,
Vadym

[indunie]

Hello @vahonc ,

Thank you for your attension on this issue.

I am having multiple stores and special prices have been set store wise.

Please see the below example and let me know if I am having any wrong data inserted in my database related to the below example.

Ex : Arapahoe Boot
https://ridgelineclothing.com.au/new-sale
I am displaying one of simple products from this Arapahoe Boot example in the category page. (Please note that I am not displaying any parent products in my category views)
SKU : RLBARONG-07

Check the 8th row which was related to my AU store product. All dates are expired and it will not removed from the category with mannual reindex either.

Virtual category configurations as below.

Could you please review these configurations and let me know if I did anything wrong?

Also please find the extracted query from the system.log file for the category load.
system log in the category load.txt

Thank you.

BR,
Indunie Florence

[vahonc]

@indunie

How many virtual categories are you using in your store? Are all child categories of the Sale category from the screenshots is virtual?

And what about assigning your test product in websites?

BR,
Vadym

[indunie]

Hello @vahonc ,

There are 5 child categories set for the "Sale" parent category.
3 of them are virtual with different conditions and there are 2 non virtual categories.

I don't think it has more than 5 virutal categories for the store.

Let me know if you need further information.
Thank you

BR,
Indunie Florence

[indunie]

Hello @vahonc ,

I just figure out all the products which don't have special prices set which were displayed with the virtual category have been come from the non virtual subcategory of the "Sale" parent category. Once I detach that non virtual subcategory from the parent issue was fixed.

i really appreciate your support and cooperation in this matter.

Thank you.

Best regards,
Indunie Florence