Update the Application

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 score

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";