Lithium have this method called GetMostSoldProducts(), That am guessing is using a consolidated product sales counter?
I would like to add a GetTrendingProducts() or a time-decayed aggregate type of query. Where new products get in on top but need sales to stay there. Like so:
Order = S / (T+2)^G
where,
S = number Of sales
T = time since publishing (in days or weeks)
G = Gravity
I only want the top, 50 but this would require querying all products to calculate this and we have 100k products, how would you go about handling this? I am not asking for code, I just want some input. Right now am leaning towards creating a few custom db tables and using some crons but if there is a “standard” way to handle this type of querys I would like that more.
The GetMostSoldProducts is fetching data from the ECommerce_StatisticMostSoldArticle/ ECommerce_StatisticSoldArticleRelations that will be populated with an scheduled task that executes the ECommerce_CalculateMostSoldArticle/ ECommerce_CalculateSoldArticleRelations based on the information for on the placed orders.
In the Accelerator we also reading out this information and storing this in the search index together with the other product data to be able to do fast searches. See MostSoldIndexingProviderPreProcessor in accelerator code how we connect the data into search index.
Your thinking is what I should have as suggestion to not need to recalculate the values all the time. Performance wise I think it should be good if the calculation can be in one SQL statement (or multiple but storing results from the different statements in temporary tables).