MySQL ORDER SELECT based on a score

MySQLorder-by

I have a table 'member' and want to be able to search the table and order results by relevance. I have a single string to search on and want to search multiple fields. Example, given_name and family_name (like the Facebook member search).

The fields in in the member table are strings so I think a full test search will not be the best option.

Table Member

+----+------------+-------------+----------------+
| id | given_name | family_name | mutual_friends | 
+----+------------+-------------+----------------+
|  1 | Levi       | Putna       | 4              |
|  1 | Levi       | Levi        | 1              |
|  2 | Leviother  | Somename    | 4              |
|  3 | Boo        | Somelevi    | 4              |
|  4 | Lala       | Smith       | 4              |
|  5 | Assem      | Jones       | 4              |
+----+------------+-------------+----------------+

I have been trying to build a query that creates a score based on matching some criteria and then ordering by the score. I am using a case to do this.

For this example we will use the search query of 'levi'

SELECT id, given_name, family_name,
CASE
    WHEN given_name = 'levi' THEN score + 5
    WHEN given_name like 'levi%' THEN score + 3
    WHEN family_name = 'levi' THEN score + 4
    WHEN family_name like 'levi%' THEN score + 3
    WHEN given_name like '%levi%' THEN score + 1
    WHEN family_name like '%levi%' THEN score + 1
END AS score
FROM (select *, 0 AS score FROM member) AS member_search

ORDER BY score DESC

This seems like a bad way to do this and will only score based on the first match in the case. How would I need to rewrite this query so that it could continue to add to the score based on multiple matches of case? Is their a better way to manage this type of query?

Best Answer

To respond to your question, if you want to validate multiple conditions for a case statement, you can use coalesce or nest case statements.

However judging by what you want to do, it might be best to handle this within your application.
SQL Server is not made for computing, it's a database, inherently meant to store and produce data. As such it's been highly optimized for just those types of tasks (storing and handling data) and not for handling the data after it's been found.

Quick edit to link MySQL case nesting after noticing you're talking about MySQL not MSSQL.

Related Question