# 社区情绪与有害内容查询

**URL:** <https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741>\
**Category:** Data & reporting\
**Tags:** ai, sql-query\
**Created:** [2023年五月12日 18:58 UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741 "2023-05-12T18:58:55Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![JammyDodger](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jammydodger/32/254611_2.png) [@JammyDodger](https://meta.discourse.org/u/JammyDodger)\
**Post date:** [2023年九月19日 20:04 UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4 "2023-09-19T20:04:47Z")

</div>

```sql
-- [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

```

啊，你抢先一步了。 🙂

不过，这里还有一个针对特定帖子的，以免显得回复浪费了：

```sql
-- [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

```

`topic_id` 和 `post_number` 都可以在 URL 中找到，所以非常用户友好。

Topic\_id:

> [@Community Sentiment and Toxicity queries](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/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: -…

Post\_number:

> [@Community Sentiment and Toxicity queries](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/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: -…

---

_[View the full topic](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741)._