title: MySQL Review System for Website
body: <p>I need help with a query involving a review system set up with the following two tables.</p>
  
  <pre>
  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'
  </pre>
  
  <pre>
  reviews_thumbs
  --------------
  review_id   user_id like
  1           22      1
  1           45      -1
  2           40      -1
  3           22      1
  </pre>
  
  <p>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 <code>rating</code> column in the reviews table is a star system, unrelated.)</p>
  
  <p>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):</p>
  
  <pre>
  1.  The total number of upvotes
  2.  The total number of downvotes
  3.  Whether the current active user has upvoted or downvoted the review
  </pre>
  
  <p>I have accomplished this using the following query, which isn't sitting right with me:</p>
  
  <pre>
  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
  </pre>
  
  <p>(And binding the appropriate :userid and :itemid.)</p>
  
  <p>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.</p>
  
  <p>Could someone please point me in the right direction on how to accomplish this in a cleaner way?</p>
  
  <p><strong>What I've Tried:</strong></p>
  
  <p>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.</p>
  
  <p>Thank you in advance for any help you may provide.</p>