How can I sort event items by closest date in SOLR?

Question

I have an event listing API, which takes as input start and end dates and returns a list of events. The requirement for sort, is to show closest to farthest events by start date in the past, followed by closest to farthest event by end date in the future.

Essentially the following logic (which was done on a list previously).

    List<EventModel> newList = listEvent.Where(e => e.startDT <= DateTime.Today).OrderByDescending(e => e.startDT).ToList();
    newList.AddRange(listEvent.Where(e => e.startDT > DateTime.Today).OrderBy(e => e.endDT));
    listEvent = newList;

How can I achieve this on an IQueryable to execute in my SOLR search?

I see that concat isn't supported with Linq to Sitecore https://doc.sitecore.com/developers/90/sitecore-experience-management/en/linq-to-sitecore.html

Any other way to do this?

Answer 1

Consider researching Solr functions. My experience has used a hard-coded field name with solr field extension. Perhaps you can figure out how to make this more dynamic.

First you can start by adding a new field to your CustomSearchResultItem class.

[IndexField("_val_")]
public string _val_ { get; set; }

Second you can write a query using the new field.

var query = PredicateBuilder.True();
query = query.And(x => x._val_.Equals("recip(abs(ms(NOW/HOUR,startdate_tdt)),3.16e-11,4,.4)"));

What this query should do is rank the results higher when the date is close to today, and then rank lower the further away it is.

Here is a simple demonstration while using Sitecore PowerShell Extensions against Solr.

$criteria = @(
    @{Filter = "Equals"; Field = "_val_"; Value = "recip(abs(ms(NOW/HOUR,__smallcreateddate_tdt)),3.16e-11,4,.4)"}
)
$props = @{
    Index = "sitecore_master_index"
    Criteria = $criteria
}

Find-Item @props | Select-Object Name,CreatedDate

I used CreatedDate and so I don't expect any future dates.