# Community Sentiment and Toxicity queries
**URL:** https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741
**Category:** Data & reporting
**Tags:** ai, sql-query
**Created:** [May 12, 2023, 6:58pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741 "2023-05-12T18:58:55Z")
**Posts on this page:** 15
**Page:** 1
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [May 12, 2023, 6:58pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/1 "2023-05-12T18:58:55Z")
For those wondering how to view the classification results database for the `Community Sentiment` and `Toxicity` modules, this can be done using the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin, and the `classification_results` table.
This is useful for seeing how the AI plugin is functioning on your site and classifying posts.
#### AI Sentiment
```sql
SELECT target_id as post_id,
model_used,
classification->'negative' as negative,
classification->'neutral' as neutral,
classification->'positive' as positive
from classification_results
WHERE model_used = 'sentiment'
order by id desc
```
#### AI Emotion:
```sql
SELECT target_id as post_id,
model_used,
classification->'neutral' as neutral,
classification->'sadness' as sadness,
classification->'surprise' as surprise,
classification->'fear' as fear,
classification->'anger' as anger,
classification->'joy' as joy,
classification->'disgust' as disgust
from classification_results
WHERE model_used = 'emotion'
order by id desc
```
#### AI Toxicity:
```sql
SELECT target_id as post_id,
classification->'toxicity' as toxicity,
classification->'severe_toxicity' as severe_toxicity,
classification->'obscene' as obscene,
classification->'identity_attack' as identity_attack,
classification->'insult' as insult,
classification->'threat' as threat,
classification->'sexual_explicit' as sexual_explicit
From classification_results
WHERE classification_type = 'toxicity'
order by id desc
```
---
### Author:  [@Samantha\_O](https://meta.discourse.org/u/Samantha_O)
#### Post date: [September 19, 2023, 7:46pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/2 "2023-09-19T19:46:29Z")
Is there a way to add a filter to this query to get the posts from a particular thread? Right now, it seems it is getting posts across the entire Community, which is nice, but also cumbersome to find a particular post/thread you are looking for, not to mention you will run into the row limit for this.
---
### Author:  [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [September 19, 2023, 8:01pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/3 "2023-09-19T20:01:49Z")
Hi @Samantha_O đź‘‹
Does this work for specific topics? you will have to specify the topic ID in the params.
```sql
-- [params]
-- int :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 cr.id DESC
```
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 19, 2023, 8:04pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/4 "2023-09-19T20:04:47Z")
```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
```
Ahh you pipped me to it. 🙂
But here’s one for a specific post as well just to not feel like a wasted reply:
```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
```
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
---
### Author:  [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [September 19, 2023, 8:05pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/5 "2023-09-19T20:05:53Z")
I think this should target category id:
```sql
-- [params]
-- int :category_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
JOIN topics t ON t.id = p.topic_id
WHERE cr.model_used = 'sentiment'
AND t.category_id = :category_id
ORDER BY cr.id DESC
```
---
### Author:  [@Samantha\_O](https://meta.discourse.org/u/Samantha_O)
#### Post date: [September 19, 2023, 8:27pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/6 "2023-09-19T20:27:54Z")
Thank you both! When I run these, it doesn’t find older topics/topic ids. I wonder - does the sentiment analysis only happen on new posts?
_edited for clarity_
---
### Author:  [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [September 19, 2023, 8:49pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/7 "2023-09-19T20:49:22Z")
@JammyDodger would know better, but yes I think the sentiment analysis works on new posts. I think in order to get it to use the old posts would likely require some rails commands to process them through the sentiment model, and into the `classification_results` table (where the new posts’ analyses results are).
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [September 19, 2023, 9:29pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/8 "2023-09-19T21:29:01Z")
It works on new posts once it’s turned on. 👍 I’m not sure if there is a backfill option for it?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [September 20, 2023, 12:43am UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/10 "2023-09-20T00:43:21Z")
Only new posts from now. We can consider backfilling that information _after_ we start reporting on the data and we validate it.
---
### Author:  [@Samantha\_O](https://meta.discourse.org/u/Samantha_O)
#### Post date: [November 8, 2023, 9:49pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/11 "2023-11-08T21:49:08Z")
Would one of you (or another brilliant query-ist) be able to help me fine-tune this a bit? I would like to have params for dates and those dates would correspond to the post date. The end goal here would be to be able to look at changes over time while waiting to see if the dashboard can do that in the future 🤞
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [November 9, 2023, 1:15am UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/12 "2023-11-09T01:15:19Z")
Hi Samantha,
If you wanted to refine those queries to include start and end date parameters, you could use a query like this:
```sql
-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
SELECT
p.created_at,
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.created_at BETWEEN :start_date AND :end_date
ORDER BY cr.id DESC
```
Example Results:
| created\_at | post | model\_used | negative | neutral | positive |
| --- | --- | --- | --- | --- | --- |
| 2023-11-08T21:21:23.913Z | post\_id | sentiment | 58 | 38 | 2 |
The important part added here is joining the `posts` table with the `classification_results` table so that we can add:
```plaintext
AND p.created_at BETWEEN :start_date AND :end_date
```
To the `WHERE` statement to filter the posts by their `created_at` date.
Since the `emotion` and `toxicity` classification queries are similar, you could modify those queries with this addition to include start and end date parameters as well. 🙂
---
### Author:  [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [February 15, 2024, 12:58pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/13 "2024-02-15T12:58:51Z")
> [@Dashboard Report - Overall Sentiment](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428):
>
> This is an SQL version of the Dashboard Report for Overall Sentiment. discourse This report requires the [Discourse AI](https://meta.discourse.org/t/discourse-ai/259214) plugin and [Sentiment Analysis](https://meta.discourse.org/t/enable-sentiment-analysis/259599) to be enabled. This dashboard report compares the number of posts classified either positive or negative with the “Sentiment” AI, over a specified period. By leveraging sentiment analysis, the report categorizes posts as either positive or negative based on a defined threshold, which can be adjusted. This categorization is then used to calcula…
---
### Author:  [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [February 16, 2024, 1:05am UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/14 "2024-02-16T01:05:14Z")
Adding this one here as well:
> [@Dashboard Report - Post Emotion](https://meta.discourse.org/t/dashboard-report-post-emotion/295553):
>
> This is an SQL version of the Dashboard Report for Post Emotion. discourse This report requires the [Discourse AI](https://meta.discourse.org/t/discourse-ai/259214) plugin and [Sentiment Analysis](https://meta.discourse.org/t/enable-sentiment-analysis/259599) to be enabled. This dashboard report shows the number of posts classified with one of following emotions, group by poster trust level, within a specified date range: Sadness Surprise Fear Anger Joy Disgust Admiration Amusement Annoyance Approval Caring Confusion Curiosity Desire Disappointment Disapproval Embarrassment Excitement Gratitude Grief L…
---
### Author:  [@sonpage](https://meta.discourse.org/u/sonpage)
#### Post date: [November 13, 2024, 4:20pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/15 "2024-11-13T16:20:27Z")
I tried using this query (our sentiment setting is set up) but it’s not returning any results. Anything I should try to troubleshoot?
---
### Author:  [@Falco](https://meta.discourse.org/u/Falco)
#### Post date: [November 13, 2024, 4:26pm UTC](https://meta.discourse.org/t/community-sentiment-and-toxicity-queries/275741/16 "2024-11-13T16:26:21Z")
I’ve updated the guides at [Dashboard Report - Overall Sentiment](https://meta.discourse.org/t/dashboard-report-overall-sentiment/295428) and [Dashboard Report - Post Emotion](https://meta.discourse.org/t/dashboard-report-post-emotion/295553), can you try again with the updated queries?