I am implementing a search for products whose prices are calculated based on rules based on the quantity requested.
For example:
- 1 to 5 units -> price = units * X + Y
- 6 to 10 units $ 8 -> price = units * Z
- 11 to 15 units $ 6 -> price = units * W
(X, Y, Z, W can be constants or formulas)
I tried to use painlessly to do the price calculation in the query time, but it was very expensive and time consuming to deliver the result.
The second strategy I applied was to define a base price for each product, store it in elasticSearch, and then apply the price formula to each product (in nodejs) considering the requested quantity, this approach was better in terms of performance.
the difficulty I have now is that I need to implement a filter for prices with minimum and maximum value, but I cannot filter the products since I calculate the price after obtaining them. I can't assign price at indexing time either because it depends on the requested quantity (n)
Do you know of a quick and reliable solution that can be applied to these cases where the price of a product depends on the quantity ordered and the price rules that apply from that quantity?