Complicated MySQL query?

Question

I have two tables as follows:

I have a RatingsTable that contains a ratingname and a bit whether it is a positive or negative rating:

RatingsTable
----------------------
ratingname  ispositive
----------------------
Good        1
Bad         0
Fun         1
Boring      0

And I have a FeedbackTable that contains feedback on things: the person rating, the rating and the thing rated. The feedback can be determined if it's a positive or negative rating based on the RatingsTable.

FeedbackTable
---------------------------------
username  thing        ratingname
---------------------------------
Jim       Chicken      Good
Jim       Steak        Bad
Ted       Waterskiing  Fun
Ted       Hiking       Fun
Nancy     Hiking       Boring

I am trying to write an efficient MySQL query for the following:

On a page, I want to display the the top 'things' that have the highest proportion of positive ratings. I want to be sure that the items from the feedback table are unique...meaning, that if Jim has rated Chicken Good 20 times...it should only be counted once. At some point I will want to require a minimum number of ratings (at least 10) to be counted for this page as well. I'll want to to do the same for highest proportional negative ratings, but I am sure I can tweak the one for positive accordingly.

Answer 1

SELECT *
FROM `feedback`
LEFT JOIN `ratings` ON `feedback`.`rating` = `rating`.`label`
ORDER BY `rating`.`value` DESC
GROUP BY `feedback`.`username`
LIMIT 10

The summary: join the ratings to your feedback table, but group by the username so you only get one username per result.

Answer 2

To get the "things" in order of proportion of good ratings you can use this query:

SELECT thing, SUM(ispositive) / COUNT(*) AS proportion_positive
FROM (SELECT DISTINCT username, thing, ratingname FROM FeedbackTable) T1
JOIN RatingsTable T2
ON T1.ratingname = T2.ratingname
GROUP BY thing
ORDER BY proportion_positive DESC

For your example data it returns this:

thing        proportion_positive
Chicken      1.0000
Waterskiing  1.0000
Hiking       0.5000
Steak        0.0000

To require at least 10 votes before displaying a thing in the results add this line after the GROUP BY:

HAVING COUNT(*) >= 10

To get the proportion of negative ratings change SUM(ispositive) to SUM(NOT ispositive).

Note: it might be better to add a unique constraint to your voting table instead of selecting only the disctinct values.