Order / group search query results based on relevancy

Hello,
I am currently working on a search user function by name, which returns the ids of people whose name matches the text the user typed in. This by itself is not difficult:

 SELECT meta().id userID, firstName || ' ' || lastName fullName
FROM bucketName 
WHERE docType = 'user'
AND LOWER(firstName || ' ' || lastName) like '%text%'

User document:

userID1 : {
firstName: 'someFirstName',
lastName: 'someLastName' 
}

However, I want to order / group the result based on how relevant their name are to the text the user typed in. For example, someone whose full name perfectly matches the text (search ‘my name’, got ‘My Name’) would be first, then to those whose firstName / lastName matches the text (Search ‘my’ or ‘name’, got ‘My Name’), then to those whose firstName / lastName starts with the text (Search ‘nam’, got ‘My Name’), then finally those whose first/lastName contains the text (Search ‘am’, got ‘My Name’). I can also do the categorization on the server code after getting the results, but I was trying to see if I could do everything I need inside a query, and whether it would be better to do so. Thank you so much in advance.

You can try the following if it satisfies your requirements

SELECT meta().id userID, firstName || ' ' || lastName fullName
FROM bucketName 
WHERE docType = 'user'
AND LOWER(firstName || ' ' || lastName) like '%my name%'
ORDER BY (CASE WHEN LOWER(firstName) = "my" TEHN 1 ELSE 0 END) DESC,
          (CASE WHEN LOWER(lastName) = "name" TEHN 1 ELSE 0 END) DESC;

Thank you a lot, I did not think of case for ordering like that!

So I tried implementing the search function with this new query, and compare it to the code in which I use the query to get all users, and then do the search and categorize and so on. Overall, aside from minor differences in ordering, the two implementations take roughly the same amount of time to execute.
This might be outside of N1ql topic, but is it better to do the searching inside of query, or getting every user and then do the search?

the rough code for searching inside of query:

func SearchUser(input string) {
  query = {
      // get all user
      // where firstName || ' ' || lastName like '%input'
      // order correspondingly
  }
  for each response of query:
     add response to userList
  return userList
}

Rough code for getting all users and then search for result:

func SearchUser(input string) {
  query = {
      // get all user
  }
  for each response of query:
     if the response.name satisfy the search
     add to userList
  return userList
}
In SQL 
      Pros:  If you are going to do pagination  or only need few documents also  then no need to transfer data to client.
      Cons: Based on sorting criteria things can go really complex.

In application:
     Pros: You can implement your own complex sort
    Cons: You need to get all possible qualified data, even if you need few documents
1 Like