Order SQL query in best match

Question

I get a list of results returned from my query that I want to order in the best match. I will try to be as clear as I can be, but if something is not clear enough, let me know and I will try to make it more clear.

The user has already entered a list of settings called findsettings . Using these findsettings I am looking for products. It all goes well until he comes out for a better match.

There are several fields, such as min_review, max_price, allows_pets, etc

I want to order. For example, he must first order products with min_review of 40% , then max_price = 10.00 , and then allows_pets = 0 . You can do this with OR , but I want results that do not match to only appear at the bottom of the list. Therefore, basically it should show the best match first, then second, third, etc. Until the products match the smallest.

I'm not sure how to handle this, so I hope you can help me figure this out.

Answer 1

The basic principle is to create a relevance score for each record, and then sort by it.

If you want each criterion to have equal weight, in MySQL you can simply add each logical expression together (note that this is non-standard SQL - in other DBMSs you may have to use CASE to check the condition and profitability of a number):

 ORDER BY (review>=40/100) + (price<=10.00) + (allows_pets=0) DESC 

If the criteria are unevenly weighted, you can multiply each expression by its weight:

 ORDER BY 5*(review>=40/100) + 3*(price<=10.00) + 1*(allows_pets=0) DESC 

And / or if those that correspond to a specific subset should always be displayed first regardless of later results, you can split the ORDER BY :

 ORDER BY 5*(review>=40/100) + 3*(price<=10.00) DESC, 1*(allows_pets=0) DESC 

If you want to see the relevancy score in your results, you can also add any of the above expressions to your SELECT (and then use the resulting column in your ORDER BY to avoid writing it twice)

 SELECT *, (review>=40/100) + (price<=10.00) + (allows_pets=0) AS relevance, FROM my_table ORDER BY relevance DESC 

Please note that if you want to get records that have a value that is closest to a certain target (for example, min_review close to 40% and not exact), you can take the (absolute?) Difference between it and your target value:

 IF(review>=40/100, review-40/100, NULL) 

However, you must be careful to properly weigh your expressions if / when combining with others in the same criteria.