Acorel
Gratis demo

Working with date ranges in SOLR

Koen van de Leur, 05 mei 2021

When a user of an eCommerce website scrolls through a product overview page, a lot of product information is requested at once. To serve this many requests, many websites use indexed data, which is ‘flattened’ into JSON or XML (instead of spread over many database tables) and fast to query.

SAP Commerce ships with a SOLR search engine, and some out-of-the-box features to index (product) data. Most data is indexed as text (example: product name), numbers (for example: price), or date (for example: release date). There are some use cases where you’d like to index a range of dates, and query accordingly. For example, we used date ranges to be able to search, filter and show indexed data of products that have an active promotion at the moment, and have them stop showing up in the search results once the promotion ends.

SOLR supports indexing and querying date ranges, but SAP Commerce doesn’t out-of-the-box. In this blog post I will show how to set this up.

Indexing and querying

Here’s a small example on how indexing and querying works in SAP Commerce and SOLR. Let’s say we have a Vinyl King webshop selling vinyl records. We might have product data spread over two tables: ‘album’ and ‘price’

| id   | artist      | title                     | released   |
| 1234 | Johnny Cash | The legend of Johnny Cash | 05-10-2005 |

| id   | price |
| 1234 | 20.0  |

After indexing, the ‘SOLR document’ for this vinyl record may look like this:

{
"artist_text": "Johnny Cash"
"title_text" : "The legend of Johnny Cash",
"price_double": 20.0,
"released_date": "05-10-2005"
}

When performing a ‘full text search’, we may find this product in the search result if we use ‘Johnny Cash’ or ‘Cash’ as a search term, as it will have a full or partial hit on the indexed ‘artist’ or ’title’ field. SOLR also supports filtering: by adding a filter to our query ‘price:[10.0 TO 30.0]’ we could narrow the search result to vinyls with a price between 10.0 and 30.0.

When SOLR returns a collection of SOLR documents in a search result, its data can be used to fill the product tiles in a search lister page.

Indexing a range of dates

Aside from querying with a range, it’s also possible to index a range. Let’s say we have a promotion running on our vinyl, but it’s only active in a specific time window. During that time, we want to have the product show up in our Promotions overview page, allow users to filter promotion products with a facet checkbox, and have the product tile highlighted with a promotion label.

We want our SOLR document to look like this:

{
"artist_text": "Johnny Cash"
"title_text" : "The legend of Johnny Cash",
"price_double": 20.0,
"released_date": "05-10-2005"
"promo_daterange": "01-06-2021 TO 05-06-2021"
}

SOLR then allows us to filter, like with price, using a range query: ‘promo:[01-06-2021 TO 02-06-2021]’. We can specify three different relational predicates between the indexed data and the query data: ‘intersects’ (default), which means the two date ranges should intersect to get a hit, ‘contains’, which means the indexed range contains the entire query range and perhaps more, and ‘within’, which means the indexed range falls within the min/max of the query range. These can be specified as an ‘operation’ to be included in the query.

In our example, we could send a time range of ‘current time + 1 minute’ with a ‘contains’ operation to see which vinyls have an active promotion at that exact moment.

Store opening times are another possible use case of date ranges in a commercial environment. For an example, see https://cwiki.apache.org/confluence/display/solr/DateRangeField

For more info, see https://solr.apache.org/guide/6_6/working-with-dates.html

SAP Commerce implementation

Update the schema.xml that is used to configure SOLR in order to add the fieldType and the fields.

<schema name="hybris" version="7.5">
<types>
<!-- Basic Types -->
<fieldType name="double" class="solr.DoublePointField" docValues="true" sortMissingLast="true" />
<fieldType name="date" class="solr.DatePointField" docValues="true" sortMissingLast="true" />
<fieldType name="string" class="solr.StrField" docValues="true" sortMissingLast="true" />
<fieldType name="daterange" class="solr.DateRangeField"/>
</types>
<fields><dynamicField name="*_double" type="double" indexed="true" stored="true" />
<dynamicField name="*_double_mv" type="double" indexed="true" stored="true" multiValued="true" />
<dynamicField name="*_date" type="date" indexed="true" stored="true" />
<dynamicField name="*_date_mv" type="date" indexed="true" stored="true" multiValued="true" />
<dynamicField name="*_string" type="string" indexed="true" stored="true" />
<dynamicField name="*_string_mv" type="string" indexed="true" stored="true" multiValued="true" />
<dynamicField name="*_daterange_mv" type="daterange" indexed="true" stored="true" multiValued="true"/>
</fields></schema>

As you can see, it is also possible to index multivalue dateranges. Upon searching SOLR evaluates if any of the list values match with the query range in either ‘intersect’, ‘contains’ or ‘within’.

Now, we need to tell SAP Commerce about the new SOLR type

New Enum type in vinylkingcore-spring.xml:

<enumtype dynamic="true" generate="false" code="SolrPropertiesTypes" autocreate="false">
<value code="daterange" />
</enumtype>

Extend the index property types mapping in vinylkingcore-spring.xml to use the ‘Contains’ operator by default:

<bean id="vinylKingIndexPropertyTypeMapping" depends-on="indexPropertyTypeMapping" parent="mapMergeDirective" >
<property name="key" value="daterange" />
<property name="value" value="java.util.Date"/>
</bean>

<alias name="defaultDateRangeQueryOperators" alias="dateRangeQueryOperators"/>
<bean id="defaultDateRangeQueryOperators" class="de.hybris.platform.solrfacetsearch.solr.impl.DefaultSolrIndexedPropertyTypeRegistry.IndexPropertyTypeOperatorsMapping">
<property name="propertyType" value="daterange"/>
<property name="operators">
<util:set set-class="java.util.HashSet">
<value>CONTAINS</value>
</util:set>
</property>
</bean>

Index the SOLR property and provide a configuration for the DEFAULT search template

INSERT_UPDATE SolrIndexedProperty; solrIndexedType(identifier)[unique = true]; name[unique = true]; type(code); facet[default = true]; facetType(code); facetDisplayNameProvider ; displayName[lang = en]
; $solrIndexedType ; promo ; daterange ; ; MinMaxRange ; vinylKingPromoValueProvider ; "Promotion"

INSERT_UPDATE SolrSearchQueryProperty; indexedProperty(name, solrIndexedType(identifier))[unique = true]; facet[default = true]; facetType(code); includeInResponse[default = true]; searchQueryTemplate(name, indexedType(identifier))[unique = true][default = DEFAULT:$solrIndexedType];
; promo:$solrIndexedType ; ; MinMaxRange ; ;

In order to actually index the data, we need a vinylKingPromoValueProvider to determine per product whether, and if so between which dates a product has an active promotion. This can be implemented based on the out-of-the-box PromotionEngine.

Using the indexed time ranges, we can filter on products with an active promotion, provided the attribute is configured as a facet in SOLR (see above configuration ImpEx). For a ‘promotions’ page we could either add the filter query to the URL, or add the filter query in code at the controller that serves that page with product search data.

Of course there are more ways to provide an overview page of promotion products in SAP Commerce, for example by using the catalog & category structure, so extending the SOLR types with date ranges can be avoided. However, the above example does show that there is a lot of potential in SOLR that can be unlocked in SAP Commerce with relative ease by configuration.

Koen van de Leur

Read all my blogs

Receive our weekly blog by email?
Subscribe here:

More blogs