How to order search results for a search query first, and then in alphabetical order?

Question

I use NHibernate to search by item name. I get a paged list of items from the database, and I order it by name in ascending order.

So, if I search for a “term”, I return a results page that contains a “term” anywhere in the result, and the page is sorted alphabetically.

For example, the results might look like this:

a term d term g term j term p term s term term 1 term 2 v term z term 

Technically, this list is correct because it is sorted alphabetically.

However, technically correct is not enough for the client. They want the list to be ordered first by relevance of the searched word, and then in alphabetical order.

Thus, the result will be

 term 1 term 2 a term d term g term j term p term s term v term z term 

I do not know how to build this query, or if it is possible. If this could be done in the application, it would be much simpler, but because of the paging, this should be done in the database. This is what complicates the situation. Can someone please point me in the right direction?

Answer 1

If it's possible to write a function that returns an integer based on relevance, you can do

 SELECT MyField FROM MyTable WHERE MyField like '%term%' ORDER BY GetRelevance(MyField, SearchTerm) DESC 

Your function does not have to be very complicated. He could just see if the field will start with SearchTerm and return 2, and if it does not return 1. Then you can expand it if you have other relevance criteria.

Or if by relevance you mean that it starts with a search query, you can also split your database query into two and combine them together.

i.e.

 SELECT MyField FROM MyTable WHERE MyField LIKE 'term%' ORDER by MyField union SELECT MyField FROM MyTable WHERE MyField LIKE '%term%' AND NOT LIKE 'term%' ORDER BY MyField 

It will not be good for your work.

Answer 2

I have never seen anything complicated in SQL or Linq for relevance searches.

If this was my problem, I would like to create a custom helper class that implements IComparer. Then you can create any number of abnormal conditions in the code. Of course, this is not done on the server side.

I wonder if there is a third party .net library that can specifically determine relevance.

Answer 3

Thanks to Ray's suggestion, I am implementing the following:

 CREATE FUNCTION [dbo].[fnGetRelevance] ( @fieldName nvarchar(50), @searchTerm nvarchar(50) ) RETURNS int AS BEGIN if (@fieldName like @searchTerm + '%') -- starts with begin return 0 end else if ((@fieldName like '%' + @searchTerm + '%') and (@fieldName not like @searchTerm + '%')) -- contains, but doesn't start with begin return 1 end return 1 END GO 

This led to the following result for one of the larger queries: