Smart Search API Where Condition

Tomasz Czado asked on June 13, 2019 15:25

Hello,

I'm using Kentico API to search for some results. The problem is that something is not working as expected. For one of my custom page types I added new field called ProjectCompletion (data type: Date). Field is optional, so in database you may have a date, null or empty string. This field is also set as Searchable and smart search index has been rebuilded.

Then, I tried to udpate SearchSort by adding (CASE WHEN ProjectCompletion IS NULL THEN 1 ELSE 0 END) DESC to where condition:

ProjectIsFeatured DESC, 
(CASE WHEN ProjectCompletion IS NULL THEN 1 ELSE 0 END) DESC, 
ProjectCompletion DESC, 
ProjectSchedule DESC, 
NodeOrder, 
NodeID

but this seems to not work at all. When I use the same query to search directly in DB, it works fine, but not here. I know, that we're looking for results in Lucene, so maybe that where condition is incorrect? But how can I write it and make it work with Lucene?

Recent Answers

Mike Wills answered on June 13, 2019 17:59

Hi Tomasz,

You're right. The sort syntax that works in SQL doesn't work with Lucene. In Lucene syntax, you can specify a comma-delimited list of fields to sort by, with the optional "desc" modifier, like this:

annualfees, enrollmentfee desc

There are a couple of ways to achieve what you're looking for -- to have items with no ProjectCompletion date on top.

If you are already using custom code to build the index, it would be easy to add a custom Lucene document field, like HasProjectCompletionDate. Then you could sort by that before sorting by ProjectCompletion.
Another approach is to create a custom Lucene FieldComparator. It's more involved, but might be best if you are working with several such date fields. Here's a good discussion about it here: Custom sorting of null values in Lucene.Net 3.0.3.

This sounds interesting. I'd love to hear how it goes.

Mike

0 votesVote for this answer Mark as a Correct answer