Solr 7.3 - Sort conditionally on multi-valued field

Question

I am new to Solr, using Solr 7.3 and I am trying to achieve the following - given a date, I need to retrieve all products which have contracts expiring beyond this date, sorted on the earliest expiring contract, but not considering the already expired contracts. I am not able to get the sort correct. In the below case, product_id "10002023" should have been the last in the list as it has a contract which is expiring last (2021-08-31), ignoring the already expired contract. Any help is greatly appreciated.

http://localhost/solr/catalogue/select?fl=product_id,%20contract_id,%20contract_valid_to&fq=contract_id:[*%20TO%20*]&fq=contract_valid_to:%20[2018-12-12%20TO%20*]&q=*:*&sort=field(contract_valid_to,%20min)%20asc

..."docs":[
{
  "product_id":"10002023",
  "contract_id":["1427",
    "1428"],
  "contract_valid_to":["2018-12-07 15:36:13.0",
    "2021-08-31 15:40:33.0"]},
{
  "product_id":"1012974",
  "contract_id":["867"],
  "contract_valid_to":["2019-05-16 15:58:01.0"]},
{
  "product_id":"1012985",
  "contract_id":["777"],
  "contract_valid_to":["2019-05-19 11:07:50.0"]},
{
  "product_id":"1012975",
  "contract_id":["787"],
  "contract_valid_to":["2019-05-24 12:02:18.0"]},
{
  "product_id":"1012997",
  "contract_id":["831"],
  "contract_valid_to":["2019-05-29 15:37:37.0"]}]

Those dates doesn't look like proper date fields - what's the field definition? — MatsLindh, Dec 16 '18 at 20:41
If I change `contract_valid_to` to type `date`, I get the following error: `Can't determine a Sort Order (asc or desc) in sort spec 'field(contract_valid_to, min) asc'` — Santhosh Korambil, Dec 17 '18 at 03:32

score 0 · Answer 1 · answered Dec 18 '18 at 12:15

Unfortunately, the definition that you're using for date field is incorrect

<dynamicField name="contract_*" type="string" multiValued="true" indexed="true" stored="true"/>

It is assuming that this is just a plain string, not different from anything else.

Also, regarding your second comment Solr supports dates of only 1 format - YYYY-MM-DDThh:mm:ssZ, so if you want to use date features you would need to do conversion to a proper format and use date type.

Solr 7.3 - Sort conditionally on multi-valued field

1 Answers1