Poll: Export poll results in CSV for quiz creator?

feature
5

Hi there!

The new poll implementation changed stuff a bit, so I had to figure out how to get this sorted.

Please note that we’re sort of mis-using the poll-feature to build a quiz. That also means that we’re not really using the poll to identify the correct answer - all we want is an overview of the voting results, so we can export those to CSV and do some further manual processing to see who actually won the quiz.

Some assumptions:

  • I know nothing about SQL. My querie can probably be optimised, rewritten to make eyes bleed less etc. But at least it works. For me. For now. In other words: Here be dragons.
  • A quiz, the way our users built it, is nothing more than a topic with in that topic a post. And in that post the quizmaster creates a number of polls - one poll for each question.
  • Closing a quiz means that the polls close. The quizmaster will then, after the polls close, publish the correct answers in another post. There is therefore no direct link between quiz questions and the correct answer, other than ‘read both posts’.

The workflow to run the quiz and collect the answers therefore is nothing more than:

  1. Create quiz: Create topic, create post in topic with N poll objects, (one per question). Make sure to mark the results as public, and make sure to mark the polls as auto-close on a specific time and date (‘tonight at 22:00’).
  2. Have people answer the questions by voting for the options.
  3. As administrator, run the query to collect the raw answers. Create CSV, send over to quizmaster for further processing
  4. As quizmaster: Remove all incorrect answers from spreadsheet, count the number of times each name of each contestant pops up. The name that comes up the most has the most correct answers and therefore wins the quiz.

Like I said: Fanatic mis-use of a great feature, and soooo many things in here that could make people cringe. However: I love the enthusiasm of the forum members - they came up with this all by themselves, and the quiz is very much enjoyed by all!

So, for the query: One of the asumptions is that there is one post in a topic which contains all the answers. The query requires you to get the topic id and the post number for the post containing the quiz. This can easily be found by clicking on the timestamp of the post in the upper left corner of the post (the one that says ‘Aug 9’ or ‘2d’ or ‘5m’ or so). That link will give you an url with somwhere at the end of the url the number of the topic followed by the post id.
Take your post:
https://meta.discourse.org/t/poll-export-poll-results-in-csv-for-poll-creator/94265/4?u=janjoost

See that bit at the end? The 94265/4? 94265 is the topic id, the 4 is the post number.

Then run the query below in Data Explorer, and get the results back in 4 columns:

  • Poll name
  • Poll answer option
  • User ID for the forum member that chose this answer option
  • Forum name of that member

[ninja-edit]: I forgot to mention that Discourse will translate the user id into the avatar of the user - when you download the results in CSV, you will get the actual user id and not the image.

As said: it ain’t pretty, but it works for me :slight_smile:

Good luck!

-- [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
7 Likes