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?
You can run the following query to get a list of users who voted and their selected poll options.
Please choose the appropriate query for your Discourse version.
Query for Discourse v2.2.0.beta5 and newer
-- [params]
-- post_id :post_id
-- text :poll_name = poll
SELECT u.id AS user_id, u.username, v.poll_option_id AS option_id, o.html AS option_html
FROM polls p
JOIN poll_votes v ON (p.id = v.poll_id)
JOIN poll_options o ON (o.id = v.poll_option_id)
JOIN users u ON (v.user_id = u.id)
WHERE p.post_id = :post_id AND p.name = :poll_name
ORDER BY u.username, v.poll_option_id
Query for older versions of Discourse up to v2.2.0.beta4
-- [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 value1 ->> 'id' AS id,
value1 ->> 'html' AS html
FROM (
SELECT json_array_elements(value :: JSON -> :poll_name -> 'options') AS value1
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)
ORDER BY users.username, votes.option_id
How to find post_id
You can use the following query if you need to find the right post_id.
-- [params]
-- topic_id :topic_id
-- int :post_number = 1
SELECT id
FROM posts
WHERE topic_id = :topic_id AND post_number = :post_number
12 Likes
it works! thanks so much. 
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.
Default values are specified like this:
4 Likes
I updated the query. It’s now a lot simpler. 
4 Likes
Is there a way to filter out the results based in account age?
For example, if an account is less than 60days old it would not show in the results? Or perhaps there is a way to build a poll that limits who can vote based on account age?
We are trying to prevent possible ballot stuffing by newly created accounts for an important vote coming up.
5 Likes
That is a very interesting use case. I do think we should have an option to prevent TL0 users from voting in a poll @zogstrip!
6 Likes
Is there a parameter that I could add to the search string by @gerhard that would not show the users below a certain trust level or account age in the query?
Even if I had to manually tally votes based on the results it would be faster than having to manually check every accounts age and thier votes.
Here’s a query to set a minimal trust level for the users.
-- [params]
-- post_id :post_id
-- text :poll_name = poll
-- integer :min_trust_level
SELECT u.id AS user_id, u.username, v.poll_option_id AS option_id, o.html AS option_html
FROM polls p
JOIN poll_votes v ON (p.id = v.poll_id)
JOIN poll_options o ON (o.id = v.poll_option_id)
JOIN users u ON (v.user_id = u.id)
WHERE p.post_id = :post_id AND p.name = :poll_name
AND u.trust_level >= :min_trust_level
ORDER BY u.username, v.poll_option_id
And here’s a query to show only users who registered a certain amount of days ago.
-- [params]
-- post_id :post_id
-- text :poll_name = poll
-- integer :days_ago = 30
SELECT u.id AS user_id, u.username, v.poll_option_id AS option_id, o.html AS option_html
FROM polls p
JOIN poll_votes v ON (p.id = v.poll_id)
JOIN poll_options o ON (o.id = v.poll_option_id)
JOIN users u ON (v.user_id = u.id)
WHERE p.post_id = :post_id AND p.name = :poll_name
AND u.created_at < NOW() - INTERVAL ':days_ago days'
ORDER BY u.username, v.poll_option_id
6 Likes
Note that this query is now included in the stock Data Explorer queries that are automatically installed, so you don’t need to enter this code yourself!
Another way to find the post ID, especially if it’s the first post is to look at <topic_url>.json (e.g., Is it possible to see who voted in polls?). It’s pretty easy to find the post id in the beginning of that json.
3 Likes
@nbianca can you add to your list to add a new poll parameter min_trust_level that will allow polls creator to restrict who can vote based on their trust level?
The option should be available in the poll builder UI and there should be a nice message explaining why some users can’t vote on that poll.
7 Likes
You don’t even need to find the post_id as there is an export button on polls for staff now.
1 Like
This has been implemented, but a little bit different than requested. Polls accept a groups option, not a min_trust_level, which accepts a list of group names, comma separated (i.e. trust_level_2,staff to allow TL2+ and all staff members).
https://github.com/discourse/discourse/commit/07222af7ab8fe5b6637256f3a5a86382ab2d5564
8 Likes
I am sorry but this is too confusing to me can you break this down for me?
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.