Hi all,
I just wrote a post here on how our users mis-use (abuse?) the poll-feature to create their own pub quiz.
I created a small query that gets the results of a post with N polls, including which user voted for which poll option.
This is that query, perhaps it’s useful for others as well. Please note that the link contains more information on how we set up the quiz and how we get the results.
-- [params]
-- int :topic_id
-- int :post_number
SELECT polls.name AS "Poll name", poll_options.html AS "Answer", poll_votes.user_id AS "User ID", users.username AS "Username"
FROM poll_options
INNER JOIN poll_votes ON poll_options.id=poll_votes.poll_option_id
INNER JOIN polls ON polls.id=poll_votes.poll_id
INNER JOIN users ON users.id=poll_votes.user_id
WHERE poll_options.id IN (
SELECT id FROM poll_options WHERE poll_options.poll_id IN (
SELECT id FROM polls WHERE post_id IN (
SELECT id FROM posts WHERE topic_id=:topic_id AND post_number=:post_number )
)
)
ORDER BY polls.name, html