Order SQL query on best match

database, mysql, php, sql

Solution

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

Where you want each criterion to have equal weight, in MySQL you can just add each boolean expression together (note that this is non-standard SQL—in other RDBMS you may have to use `CASE` to test the condition and yield a number):

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

If the criteria are not equally weighted, you can either 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 match on some subset should always appear first irrespective of the later results, you can divide your `ORDER BY` clause:

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

Should you want to see the relevance score in your results, you can similarly add any of the above expressions to your `SELECT` (and then use the resulting column in your `ORDER BY` clause so as to avoid writing it twice):

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

Note that should you want to obtain records which have a value closest to some target (e.g. `min_review` close to 40%, rather than 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 weight your expressions appropriately if/when combining with others in a single criterion.

Problem

I get a list of results returned from my query that I want to order on 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 and make it more clear. A user has already entered a list of settings, called `findsettings`. With these `findsettings` I am searching for products. This all goes well, until he should go sort out the best match. There are several fields such as `min_review, max_price, allows_pets, etc`. I want to order on. For example he needs to order first on products with a `min_review of 40%`, then `max_price = 10.00` and then `allows_pets = 0`. You can do that with an `OR`, but I want the results that don't quiet match also to show, only at the bottom of the list. So basically he should show the best match first, then the 2nd one, the 3rd one etc., until the products that matches the least. I'm not sure how to handle this, so I hope you can help me sort that out.

Original source