Price filtering
In the realm of e-commerce, users expect to see prices that are personalized to their context: local currency for easy understanding, accurate selling prices from the correct price list, and timely offers that may only be valid during specific periods. Catering to these expectations with sophisticated database filtering not only enhances user experience but also streamlines the shopping process, boosting satisfaction and sales.
This chapter contains the description of evitaDB constraints that help you to control the price for sale selection and to filter products by price.
Quick guide to filtering by price
Typical usage of price constraints
In most scenarios, your query for entities with prices will look like this:
To correctly identify an appropriate selling price, you must specify all three constraints in logical disjunction:
- priceInCurrency - the currency of the price for sale
- priceValidIn - the date and time at which the price for the sale must be valid
- priceInPriceLists - the set of price lists for which the customer is eligible, sorted from most preferred to least preferred.
While it's technically possible to implement support for these tasks in evitaDB, they represent edge cases and there were more important scenarios to handle. Multiple combinations of these constraints will effectively stop finding a correct selling price and would only allow returning matching entities without a selling price.
The result set contains only products that have a valid price for sale in EUR currency:
Price for sale selection in a nutshell
For non-default price inner record handling modes, the price is calculated this way:
- FIRST_OCCURRENCE
- The sales price is selected as the lowest sales price calculated separately for blocks of prices with the same inner record id. If the price between constraint is specified, the price is the lowest selling price valid for the specified price range.
- SUM
- The sales price is calculated as the sum of the sales prices calculated separately for blocks of prices with the same inner record ID. If the price between constraint is specified, the sales price is valid only if the total is within the specified price range.
Price in currency
- argument:string!
A mandatory specification of the currency to which all prices targeted by the query must conform.
The currency code must be three-letter code according to ISO 4217.
The result set contains only products that have at least one price in EUR currency:
Price in price lists
- argument:string+
A mandatory specification of one or more price list names in order of priority from most preferred to least preferred.
The result set contains only products that have at least one price in one of the VIP price lists mentioned:
Price valid in
- argument:offsetDateTime!
- A mandatory argument of date and time (with offset) in the format yyyy-MM-ddTHH:mm:ssXXX, for example 2007-12-03T10:15:30+01:00.In Java language you can use directly OffsetDateTime
Now let's update the query to use a date and time in December:
As you can see, you'll get a somewhat different sale price because the Christmas prices have now been applied:
The prices for the sale in May were different, because the Christmas prices were not valid at that time:
Price valid in now
Price between
- argument:bigDecimal!
- A mandatory argument of the price range lower bound. The price range is inclusive, so the price must be greater than or equal to the lower bound. In the Java language you can use directly BigDecimal in plain text format, you must use the string representation of the number.
- argument:bigDecimal!
- A mandatory argument of the price range upper bound. The price range is inclusive, so the price must be lesser than or equal to the upper bound. In the Java language you can use directly BigDecimal in plain text format, you must use the string representation of the number.
The range is quite narrow, so the result set contains only a single product: