MySQL网站评论系统

Question

I need help with a query involving a review system set up with the following two tables.

reviews
-------
id  date    user_id item_id rating  review
1   02-2012 40      456     3       'I like it'
2   03-2012 22      342     1       'I don't like it'
3   04-2012 45      548     0       'I hate it'

reviews_thumbs
--------------
review_id   user_id like
1           22      1
1           45      -1
2           40      -1
3           22      1

The "reviews_thumbs" table exists to keep track of upvotes and downvotes for the reviews, so that reviews can be rated by quality. In the 'like' column, a 1 is an upvote and a -1 is a downvote. (The rating column in the reviews table is a star system, unrelated.)

When loading reviews, I need to join the reviews_thumbs table in such a way that I know the following details (for each individual review as they are returned):

1.  The total number of upvotes
2.  The total number of downvotes
3.  Whether the current active user has upvoted or downvoted the review

I have accomplished this using the following query, which isn't sitting right with me:

SELECT `reviews`.*,
COUNT(upVoteTable.`user_id`) AS upVotes,
COUNT(downVoteTable.`user_id`) AS downVotes,
COUNT(userUpTable.`user_id`) AS userUp,
COUNT(userDownTable.`user_id`) as userDown

FROM `reviews`

LEFT JOIN `reviews_thumbs` AS upVoteTable
ON upVoteTable.`review_id` = `reviews`.`id`
AND upVoteTable.`like` = 1

LEFT JOIN `reviews_thumbs` AS downVoteTable
ON downVoteTable.`review_id` = `reviews`.`id`
AND downVoteTable.`like` = -1

LEFT JOIN `reviews_thumbs` AS userUpTable
ON userUpTable.`review_id` = `reviews`.`id`
AND userUpTable.`like` = 1
AND userUpTable.`user_id` = :userid

LEFT JOIN `reviews_thumbs` AS userDownTable
ON userDownTable.`review_id` = `reviews`.`id`
AND userDownTable.`like` = -1
AND userDownTable.`user_id` = :userid

WHERE `item_id`=:itemid

GROUP BY `reviews`.`id`

ORDER BY `date` DESC

(And binding the appropriate :userid and :itemid.)

So this query works perfectly and accomplishes what I need it to. But that is a lot of joining, and I'm almost positive there must be a better way to do this, but I can't seem to figure anything out.

Could someone please point me in the right direction on how to accomplish this in a cleaner way?

What I've Tried:

I've tried doing a GROUP_CONCAT, to list a string that contains all the user ids and likes, and to then run a regex to find the user's id to see if they've voted on the review, but this also feels really unclean.

Thank you in advance for any help you may provide.

Answer 1

You could modify reviews_thumbs to look more like this:

reviews_thumbs
--------------
review_id   user_id upvote downvote
1           22      1      0
1           45      0      1
2           40      0      1
3           22      1      0

This would effectively store duplicate information, but that's okay when you have a good purpose. You really have 2 things you want to know, and this gives you a quick sum on 2 columns (and a quick subtraction on those results) to get exactly what you are looking for. This cuts you down to querying the reviews_thumbs table to 2x, once for the totals, and once for the users specific action.