Community Sentiment and Toxicity queries

Data & reporting
,
4 
-- [params]
-- topic_id :topic_id

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
ORDER BY p.id

Ahh you pipped me to it. :slight_smile:

But here’s one for a specific post as well just to not feel like a wasted reply:

-- [params]
-- topic_id :topic_id
-- int :post_number

SELECT 
    cr.target_id as post_id,
    cr.model_used,
    cr.classification->'negative' as negative,
    cr.classification->'neutral' as neutral,
    cr.classification->'positive' as positive
FROM classification_results cr
  JOIN posts p ON p.id = cr.target_id
WHERE cr.model_used = 'sentiment'
  AND p.topic_id = :topic_id
  AND p.post_number = :post_number

The topic_id and post_number can both be found in the URL, so it’s quite user-friendly.

Topic_id:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

Post_number:

https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4

5 Likes