Data Explorer queries for user voting statistics

Here are a few Data Explorer queries that you can use for getting information about a user’s votes. For each of the queries, you will need to supply a username.

Vote Count

The voting plugin sets a vote limit for each trust level. You can run the following query to get the current vote count for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
COUNT(ucf.user_id) AS vote_count
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes'
AND ucf.value IS NOT NULL

Current Vote Topics

Returns the topic and creation date of current votes for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
ucf.value AS topic_id,
ucf.created_at AS voted_at
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes'
AND ucf.value IS NOT NULL

Archived Vote Topics

When a topic with votes is closed, auto-closed, or archived, a user’s votes are moved to a UserCustomField with the name votes_archive. This creates an archive of the users votes, and also resets the user’s vote count. The following query returns the topic and the creation date for the archived votes for a given username.

-- [params]
-- string :username

WITH sample_user AS (
SELECT
u.id
FROM users u
WHERE u.username = :username
)

SELECT
ucf.value AS topic_id,
ucf.created_at AS voted_at
FROM user_custom_fields ucf
WHERE ucf.user_id = (SELECT id from sample_user)
AND ucf.name = 'votes_archive'
AND ucf.value IS NOT NULL
10 Likes

Would someone please help me with a query which generates a list of users who belong to a group (we’ll call it VIP) which have not voted in a poll?

1 Like

This will give you the users who belong to a named group who have not voted in any open topics. You’ll need to supply the value for the group_name parameter.

-- [params]
-- string :group_name

WITH voters AS (
SELECT
user_id AS voter_id
FROM user_custom_fields
WHERE name = 'votes'
)

SELECT
u.id AS user_id
FROM users u
JOIN group_users gu
ON gu.user_id = u.id
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
AND u.id NOT IN (SELECT voter_id FROM voters)

This will give you the users who belong to a named group who have not voted in a given open topic. You’ll need to supply the group_name and topic_id.

-- [params]
-- string :group_name
-- int :topic_id

WITH voters AS (
SELECT
user_id AS voter_id
FROM user_custom_fields
WHERE name = 'votes'
AND value::int = :topic_id
)

SELECT
u.id AS user_id
FROM users u
JOIN group_users gu
ON gu.user_id = u.id
JOIN groups g
ON g.id = gu.group_id
WHERE g.name = :group_name
AND u.id NOT IN (SELECT voter_id FROM voters)
AND NOT (SELECT closed FROM topics WHERE id = :topic_id)
6 Likes

Wow, that’s an incredible query! I really like how it created those entry boxes, too… very slick Simon, thank you!

Great concept; I’ve just tried this query, but it doesn’t seem to be picking up the poll when I use it - I’m just getting a full list of the group.

Does it need to be modified to use Post ID as per the Poll Results Report instead?

Also, it would be extremely helpful if it also displayed the email address of the user. This would allow us to pester those who ignore Discourse messages effectively. I’m pretty sure I could hack that together but it would hurt my brain with its limited SQL skill!

1 Like

I have updated the last two queries in this topic. Those queries were using the old polls-votes post custom field. Since that field is no longer used by Discourse, the queries were returning a full list of all members of the group.

Now that votes are saved in the user_custom_field table the SQL is a lot more straightforward. For example, SELECT * FROM user_custom_fields WHERE name = 'votes'.

Thanks Simon for your time on that. However, I’m afraid that I’m still getting the full list of group members when I run the new query.

My absolute hack job of SQL (don’t laugh) does produce what we need (including email address), although it needs the post ID and the name which are annoying.

-- [params]
-- string :group_name
-- string :poll_name
-- int :post_id

WITH voters AS (
SELECT
  poll_votes.updated_at AS vote_time,
  poll_votes.poll_option_id AS vote_option,
  users.id AS voter_id,
  users.username,
  users.name,
  users.trust_level,
  poll_options.html AS vote_option_full
FROM
  poll_votes
INNER JOIN
  polls ON polls.id = poll_votes.poll_id
INNER JOIN
  users ON users.id = poll_votes.user_id
INNER JOIN
  poll_options ON poll_votes.poll_id = poll_options.poll_id AND poll_votes.poll_option_id = poll_options.id
WHERE
  polls.name = :poll_name AND
  polls.post_id = :post_id
  )
  
  SELECT
u.id AS user_id, email
FROM users u
JOIN group_users gu
ON gu.user_id = u.id
JOIN groups g
ON g.id = gu.group_id
JOIN user_emails
ON user_emails.user_id = u.id
WHERE g.name = :group_name
AND u.id NOT IN (SELECT voter_id FROM voters)
GROUP BY u.id, email

You are probably right, or our queries are not asking for the same set of data. I’ll take a look at this within the next few days. I’m not an SQL expert, so I wouldn’t laugh at anyone’s SQL. I do have a fairly good knowledge of the Discourse database structure though.

1 Like