Is it possible to see who voted in polls?


(Tobias Eigen) #1

I just tested out polls for the first time in a while, and it works quite well and I like it.

But I would like to have an open poll so we can see who voted for which option. Is this possible? Is there any way to see who voted for what?


(Gerhard Schlager) #2

You can find the information in the database, but other than that it’s not possible to see who voted for what.
I suggested to make votes public, but that didn’t get a lot of positive feedback. :disappointed:

I hope we will get a better poll plugin soon. There’s hope…


(Tom Bartlett) #3

How would you find the data within the database for who voted on a particular poll? And what they voted for.


(Gerhard Schlager) #4

Take a look at the post_custom_fields table and all rows where the name column starts with the value polls.

The following query lists all votes made in a poll named “poll” (the default name) that are associated with post_id 14765.

CREATE TYPE poll_option AS (id VARCHAR, html VARCHAR);

SELECT votes.option_id, votes.user_id, users.username, options.html
FROM json_populate_recordset(NULL::poll_option, (
  SELECT value::JSON->'poll'->'options'
  FROM post_custom_fields
  WHERE post_id = 14765 AND name = 'polls' AND value::JSON->'poll'->>'name' = 'poll'
)) options
  JOIN (
         SELECT
           replace(name, 'polls-votes-', ''):: INTEGER AS user_id,
           json_array_elements_text(value::JSON->'poll') AS option_id
         FROM post_custom_fields
         WHERE post_id = 14765 AND name LIKE 'polls-votes-%'
       ) votes ON (options.id = votes.option_id)
  JOIN users ON (votes.user_id = users.id);

Where does the polls data live? Can it be exported for offline processing?
How to enable polls?
Free form field for Polls
Polls - how to see order of votes
(Tobias Eigen) #5

Thanks for contributing this sql query - I am just taking a look at it now (!!) and am running into a bit of trouble. Does this query work for you in the data explorer? When I run it, replacing the post ID with my post ID, I get this error:

PG::SyntaxError: ERROR:  syntax error at or near "CREATE"
LINE 8: CREATE TYPE poll_option AS (id VARCHAR, html VARCHAR)

Would appreciate any guidance.


(Régis Hanol) #6

I recently changed the way votes are stored. @gerhard, you might want to update that query :wink:


(Gerhard Schlager) #7

OK, here’s a query that works with the changes made in v1.5.0.beta3.
And it doesn’t need the CREATE TYPE poll_option anymore, so it should be easier to use with the data explorer.

The following query lists all votes made in a poll named “poll” (the default name) that are associated with post_id 14765.

SELECT votes.option_id, votes.user_id, users.username, options.html AS option_html
FROM (
       SELECT
         value ->> 'id'   AS id,
         value ->> 'html' AS html
       FROM (
              SELECT json_array_elements(value :: JSON -> 'poll' -> 'options') AS value
              FROM post_custom_fields
              WHERE post_id = 14765 AND name = 'polls' AND value :: JSON -> 'poll' ->> 'name' = 'poll'
            ) option_values
     ) options
  JOIN (
         SELECT
           key :: INTEGER                                    AS user_id,
           json_array_elements_text(value :: JSON -> 'poll') AS option_id
         FROM json_each((
                          SELECT value :: JSON
                          FROM post_custom_fields
                          WHERE post_id = 14765 AND name LIKE 'polls-votes'
                        ))
       ) votes ON (options.id = votes.option_id)
  JOIN users ON (votes.user_id = users.id);

(Kane York) #8

Changed it to use bound params for the data explorer:


(Tobias Eigen) #9

embarrassed to admit I can’t get this to work - where do I put the post_id parameter?


(Régis Hanol) #10

A new field should show up in the UI.


(Tobias Eigen) #11

whoa - that’s better. :slight_smile: I see it!

But now I get this error:

PG::UndefinedFunction: ERROR:  function json_array_elements_text(json) does not exist
LINE 25:            json_array_elements_text(value :: JSON -> 'poll')...
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

(Gerhard Schlager) #12

Oh, I didn’t notice that the Docker image is still using Postgres 9.3 - I was testing it with 9.4.
Here’s the query that should work with the Data Explorer plugin when used with the current Docker image.

-- [params]
-- post_id :post_id
-- text :poll_name = poll

SELECT votes.user_id, users.username, votes.option_id, options.html AS option_html
FROM (
       SELECT
         value ->> 'id'   AS id,
         value ->> 'html' AS html
       FROM (
              SELECT json_array_elements(value :: JSON -> :poll_name -> 'options') AS value
              FROM post_custom_fields
              WHERE post_id = :post_id AND name = 'polls' AND value :: JSON -> :poll_name ->> 'name' = :poll_name
            ) option_values
     ) options
  JOIN (
         SELECT
           key :: INTEGER                                                      AS user_id,
           trim(json_array_elements(value :: JSON -> :poll_name) :: TEXT, '"') AS option_id
         FROM json_each((
                          SELECT value :: JSON
                          FROM post_custom_fields
                          WHERE post_id = :post_id AND name LIKE 'polls-votes'
                        ))
       ) votes ON (options.id = votes.option_id)
  JOIN users ON (votes.user_id = users.id)

I added a second parameter for the poll name which usually is poll (@riking default values for params would be a nice feature ;-))


SQL query to identify which users have not voted yet in which open polls
Poll participants in Discourse API?
Poll problem - exact vote numbers needed
Consolidate poll data from multiple topics
Is it possible to keep the votes history of a poll?
How to Create Polls
(Tobias Eigen) #13

it works! thanks so much. :fireworks:

is there a query for quickly identifying the post ID containing polls? I used the .json URL method but it was surprisingly cumbersome to find the right post within it.


(Kane York) #14

Default values are specified like this:


(Rodrigo Emmanuel Santana Borges) #15

Thanks for all the help. Unfortunately, I couldn’t set this up yet.

I tried @gerhard 's query on my installation, and probing with two test polls I got, the results are always empty, even though both have 2 votes.

Has anything changed that invalidated that query?

I’m using docker image version v1.6.0.beta1 +27


(Gerhard Schlager) #16

I tested it with v1.6.0.beta1 +38. It still works.


(Rodrigo Emmanuel Santana Borges) #17

Thanks for the feedback, @gerhard . I’ll try to discover what am I doing wrong.

UPDATE:

I checked directly my database and I found my mistake. I had only mixed post and topic ids.