# Discourse Disorder

**URL:** https://meta.discourse.org/t/discourse-disorder/246587
**Category:** Plugin
**Tags:** deprecated
**Created:** [December 16, 2022, 9:10pm UTC](https://meta.discourse.org/t/discourse-disorder/246587 "2022-12-16T21:10:01Z")
**Posts on this page:** 1
**Showing post:** 25

<div class="post-metadata">

### Author: ![kinetiksoft](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/kinetiksoft/32/229578_2.png) [@kinetiksoft](https://meta.discourse.org/u/kinetiksoft)
#### Post date: [March 1, 2023, 12:45pm UTC](https://meta.discourse.org/t/discourse-disorder/246587/25 "2023-03-01T12:45:10Z")

</div>

I have also modified your query to display scoring in a more convenient way using [Data Explorer](https://meta.discourse.org/t/32566?silent=true).  
Credits go to ChatGPT and [PostgreSQL clues by Leonardo](https://meta.discourse.org/t/discourse-data-explorer/32566/263):

```sql
SELECT
  json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS toxicity,
  json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS severe_toxicity,
  json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS obscene,
  json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS identity_attack,
  json_extract_path_text(pcf.value::json, 'classification', 'insult') AS insult,
  json_extract_path_text(pcf.value::json, 'classification', 'threat') AS threat,
  json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS sexual_explicit,
  json_extract_path_text(pcf.value::json, 'model') AS model,
  pcf.created_at,
  p.raw
FROM
  post_custom_fields AS pcf
INNER JOIN
  posts AS p ON p.id = pcf.post_id
INNER JOIN
  topics AS t ON t.id = p.topic_id
WHERE
  pcf.name = 'disorder' 
  AND t.archetype = 'regular'
ORDER BY created_at DESC

```

> **And this modification will return those rows, where any of classification values is bigger than 50 (or whatever you set)**
>
> ```sql
> -- [params]
> -- int :threshold = 50
> SELECT DISTINCT ON (p.id, pcf.created_at)
> json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS toxicity,
> json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS severe_toxicity,
> json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS obscene,
> json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS identity_attack,
> json_extract_path_text(pcf.value::json, 'classification', 'insult') AS insult,
> json_extract_path_text(pcf.value::json, 'classification', 'threat') AS threat,
> json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS sexual_explicit,
> json_extract_path_text(pcf.value::json, 'model') AS model,
> p.id as post_id,
> pcf.created_at,
> p.raw
> FROM
> post_custom_fields AS pcf
> INNER JOIN
> posts AS p ON p.id = pcf.post_id
> INNER JOIN
> topics AS t ON t.id = p.topic_id
> WHERE
> pcf.name = 'disorder' 
> AND t.archetype = 'regular'
> GROUP BY p.id, pcf.value, pcf.created_at
> HAVING 
> CAST(json_extract_path_text(pcf.value::json, 'classification', 'toxicity') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'severe_toxicity') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'obscene') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'identity_attack') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'insult') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'threat') AS FLOAT) > :threshold 
> OR CAST(json_extract_path_text(pcf.value::json, 'classification', 'sexual_explicit') AS FLOAT) > :threshold
> ORDER BY pcf.created_at DESC, p.id
> 
> ```
> 
> You can also modify it by introducing several more parameters to be able to set different thresholds to report on using [Data explorer](https://meta.discourse.org/t/32566?silent=true).

Please note: this will return Public posts only, without accessing private messages.

---

_[View the full topic](https://meta.discourse.org/t/discourse-disorder/246587)._