Let’s make some updates to our voting system to make sure a user can have only a single vote registered per book. We are going to update our book list as well to show the way the user voted by changing the arrow color.
Breaking Down the Query
Let's break down this query now that it has become more complex.
First we select all columns from the books table and get the sum total of votes for a book and saving that column as
SELECT books.*, sum(votes.value) as score, "
Then, we have a nested select statement to get our vote for the book. This is a basic select from votes where the book_id on the votes table is the same as the current book from the query, and we are getting only the vote where the user_id is our id. We then save that as myVote.
. " (SELECT value FROM votes " . " WHERE votes.book_id=books.id " . " AND votes.user_id=:userId) as myVote"
The main table used in the select.
. " FROM books "
To get the score used in the first line, we do a LEFT JOIN of the votes table where the id of the current book equals the book_id's from the votes table.
. " LEFT JOIN votes ON (books.id = votes.book_id) "
Then, I want to group everything by the book id so I only have 1 row per book
. " GROUP BY books.id "
Finally we order the books by the score DESC so the highest voted book is at the top and the lowest scored book is at the bottom.
. " ORDER BY score DESC";
You need to sign up for Treehouse in order to download course files.Sign up