How to improve keyword searching in NopCommerce

Keyword searching in NopCommerce by default didn’t seem to work as well as we would have expected. Our main issues were:

  1. It doesn’t handle plurals (e.g. searching for “dinosaurs” doesn’t find anything with “dinosaur” in it)
  2. Results aren’t ranked in order of relevance

We’ve made some relatively simple modifications which in our opinion make it work much more effectively. All the necessary code is in the stored procedure ProductLoadAllPaged.

1. Ranking results

SQL fulltextsearch has a Rank feature which creates a score for a match, enabling you to order results by this value. The higher the rank the more relevant it is (in theory). Its a complicated algorithm which I wont… OK cant explain, but the point is it seems to work pretty well.

In order for this to work you need to use CONTAINSTABLE rather than CONTAINS as NopCommerce does by default. I expect the reason it uses CONTAINS is that CONTAINSTABLE can only be used on single tables, you cant as easily join queries to search on multiple tables. Because NopCommerce is multi lingual, it needs to also search on the LocalizedProperty table for multi language variations. We don’t need this in our current setup so for us it was simple enough, just remove this lookup. For those of you who do need multiple joins, this is possible using a schema bound indexed view with a fulltextindex created on that. This can be a bit fiddly to setup, and its slightly annoying moving forward because if you ever make any schema changes to the tables involved you need to recreate the index each time…. however it does work.

All you then need to do is add in the fields you want to search on and it should work just fine.

One other point on our code is you’ll notice we’ve not included the default NOP logic to build the fields to search on based on variables like “IF @SearchDescriptions = 1”. This is simply because we don’t need this functionality to be CMS driven, we’re happy to hard code this. It should be easy enough to modify the code should you need this.

2. Plurals

In order to handle plurals you need to use for example FORMSOF (INFLECTIONAL, “dinosaurs”).

One additional requirement we had was for it to also match on partial strings, so for example if I enter “dino” it needs to find “dinosaurs”. This is because we use an auto complete search in the header.
What this meant was we needed to build up 2 separate strings to search on, and use an OR operator to split these:
i.e.
1. FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)
2. “dino*” AND “action*”
Resulting in (FORMSOF (INFLECTIONAL, action) AND FORMSOF (INFLECTIONAL, dino)) OR (“dino*” AND “action*”)

Because we use the rank feature to order results it naturally means the more relevant results come first.

You can see the resulting code in action here: http://www.mulberrybush.co.uk. The client certainly considered it to be an improvement.

Code can be provided if requested.