A summary of votes by topic, including tags

Community Building Data & reporting
,

AI-generated summary

In a discussion titled ‘A summary of votes by topic, including tags’ under the Data & reporting category, CarrieSeltzer requested a SQL query to display topics in a category along with their vote counts, replies, tags, and closed status. JammyDodger provided a working query using the discourse_voting_topic_vote_count table and a CTE to aggregate tags. CarrieSeltzer asked how to include the topic title in the export, which JammyDodger addressed by adding t.title to the SELECT clause. CarrieSeltzer then asked where to insert code to include a clickable topic URL, and JammyDodger clarified its placement in the SELECT statement, offering the example 'https://meta.discourse.org/t/' || t.slug || '/' || t.id AS topic_url. The final query includes topic creation date, ID, title, URL, votes, reply count, tags, and closed status, filtered by category and ordered by date. All solutions are tailored for use in Discourse’s Data Explorer.

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.

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

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)?

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,
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?

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