Data Explorer queries for user voting statistics

data-explorer

(Simon Cossar) #1

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

Exporting data-- is it possible?
(John Waltrip) #2

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?


What cool data explorer queries have you come up with?
(Simon Cossar) #3

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

-- [params]
-- string :group_name

WITH voters AS (
SELECT
DISTINCT jsonb_object_keys(value::jsonb)::int AS voter_id
FROM post_custom_fields
WHERE name = 'polls-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)
GROUP BY u.id

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

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

WITH voters AS (
SELECT
DISTINCT jsonb_object_keys(pcf.value::jsonb)::int AS voter_id
FROM post_custom_fields pcf
JOIN posts p
ON p.id = pcf.post_id
JOIN topics t
ON t.id = p.topic_id
WHERE pcf.name = 'polls-votes'
AND t.id = :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)
GROUP BY u.id

(John Waltrip) #4

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