A summary of votes by topic, including tags

Data & reporting
,
1

I’d love to have a Data Explorer Query for all of the topics in a category and the number of votes on each. I am new to SQL queries and couldn’t find anything existing that looked close enough for me to tweak.

Ideally, it would look like this:

topic votes replies tags closed
example topic 1 85 62 web, ios, android FALSE
example topic 2 79 45 web TRUE

Can someone help me with this? Apologies if this is the wrong place. It seemed like the closest existing topic.

1 Like
2

Hello and welcome @CarrieSeltzer :slight_smile:

Would something like this work for you?

-- [params]
-- int :category_id 

WITH tag_names AS (
    SELECT t.id topic_id,
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
    JOIN topic_tags tt ON tt.topic_id = t.id
    JOIN tags ON tags.id = tt.tag_id
    GROUP BY t.id
)

SELECT t.created_at::date,
       dvtvc.topic_id,
       votes_count,
       t.posts_count,
       tn.tags,
       t.closed
FROM discourse_voting_topic_vote_count dvtvc
JOIN topics t ON t.id = dvtvc.topic_id
LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id
WHERE t.category_id = :category_id
AND votes_count > 0
ORDER BY t.created_at DESC

image
image1122×415 14 KB

1 Like
3

Yes, thank you! This is great! I just need one minor tweak—how do I get it to display the name of the topic in the csv export (not just the topic id)?

1 Like
4

I think adding t.title to the SELECT should include that:

-- [params]
-- int :category_id 

WITH tag_names AS (
    SELECT t.id topic_id,
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
    JOIN topic_tags tt ON tt.topic_id = t.id
    JOIN tags ON tags.id = tt.tag_id
    GROUP BY t.id
)

SELECT t.created_at::date,
       dvtvc.topic_id,
       t.title,
       votes_count,
       t.posts_count,
       tn.tags,
       t.closed
FROM discourse_voting_topic_vote_count dvtvc
JOIN topics t ON t.id = dvtvc.topic_id
LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id
WHERE t.category_id = :category_id
AND votes_count > 0
ORDER BY t.created_at DESC

There is also an option to include a working link to the topic in the export too if you fancy it, using eg:

'https://meta.discourse.org/t/' || t.slug || '/' || t.id AS topic_url,
2 Likes
5

I feel silly asking this, but I honestly can’t figure out where I’d add this part in the code above. Where would it go?

2 Likes
6

That’s okay. :slight_smile: It would go in this bit here: (just change it to your own site address instead of Meta)

-- [params]
-- int :category_id 

WITH tag_names AS (
    SELECT t.id topic_id,
    string_agg(tags.name, ', ' ORDER BY tags.name) AS "tags"
    FROM topics t
    JOIN topic_tags tt ON tt.topic_id = t.id
    JOIN tags ON tags.id = tt.tag_id
    GROUP BY t.id
)

SELECT t.created_at::date,
       dvtvc.topic_id,
       t.title,
       'https://meta.discourse.org/t/' || t.slug || '/' || 
       t.id AS topic_url,
       votes_count,
       t.posts_count,
       tn.tags,
       t.closed
FROM discourse_voting_topic_vote_count dvtvc
JOIN topics t ON t.id = dvtvc.topic_id
LEFT JOIN tag_names tn ON dvtvc.topic_id = tn.topic_id
WHERE t.category_id = :category_id
AND votes_count > 0
ORDER BY t.created_at DESC
2 Likes