The discussion revolves around finding and tracking censored posts in a Discourse community. rachel-statsnz wants to notify admins when posts are censored and count the number of times a user uses a bad word or phrase.
Nesha suggests using advanced search filters to list censored or flagged posts and shares a list of search filters for various post statuses. rachel-statsnz provides a simple SQL query to find censored posts and mentions a more sophisticated query being constructed to find the exact position of censored words. However, rachel-statsnz later mentions that the query was too complex and opted for manual scanning instead.
I am trying to get my community to self-regulate and hence chucked most swear words/sensitive information (RegEx phrases) into the censored watched words.
However, I noticed, it does not notify admins when posts are censored. Ideally we want to make a count of the number of times a User has used a bad word/phrase so we can silence them if need be for too many inappropriate postings.
Does discourse count censored posts per User/ how can admins get pinged for censored posts?
Or from another angle, is it possible to use advanced search filters to list censored or flagged posts?
And yes, we did our search first:
We compiled our own list of what (kinda) works:
List all archived topics:
https://datalabcommonstrial.discourse.group/latest?status=archived
List all closed topics:
https://datalabcommonstrial.discourse.group/latest?status=closed
List all deleted topics:
https://datalabcommonstrial.discourse.group/latest?status=deleted
List all bumped topics:
https://datalabcommonstrial.discourse.group/latest?status=bumped
List all deferred topics:
https://datalabcommonstrial.discourse.group/latest?status=deferred
List all unlisted topics:
https://datalabcommonstrial.discourse.group/latest?status=unlisted
List all assigned topics:
https://datalabcommonstrial.discourse.group/latest?status=assigned
List all unanswered topics:
https://datalabcommonstrial.discourse.group/latest?max_posts=1
? visible or invisible?
More:
This can be used in the Search box:
You can do this in search with in:pinned and status:archived
Even more:
How to search/filter untagged topics
advanced_filter(/^status:open$/) do |posts|
advanced_filter(/^status:closed$/) do |posts|
advanced_filter(/^status:archived$/) do |posts|
advanced_filter(/^status:noreplies$/) do |posts|
advanced_filter(/^status:single_user$/) do |posts|
advanced_filter(/^in:first|^f$/) do |posts|
advanced_filter(/^in:pinned$/) do |posts|
advanced_filter(/^in:unpinned$/) do |posts|
advanced_filter(/^in:(likes|bookmarks)$/) do |posts, match|
advanced_filter(/^in:posted$/) do |posts|
advanced_filter(/^in:seen$/) do |posts|
advanced_filter(/^in:unseen$/) do |posts|
advanced_filter(/^in:wiki$/) do |posts, match|
advanced_filter(/^posts_count:(\d+)$/) do |posts, match|
advanced_filter(/^min_post_count:(\d+)$/) do |posts, match|
advanced_filter(/^badge:(.)$/) do |posts, match|
advanced_filter(/^with:images$/) do |posts|
advanced_filter(/^#([\p{L}0-9-:=]+)$/) do |posts, match|
advanced_filter(/^group:(.+)$/) do |posts, match|
advanced_filter(/^user:(.+)$/) do |posts, match|
advanced_filter(/^@([a-zA-Z0-9_-.]+)$/) do |posts, match|
advanced_filter(/^before:(.)$/) do |posts, match|
advanced_filter(/^after:(.*)$/) do |posts, match|
advanced_filter(/^tags?:([\p{L}0-9,-+]+)$/) do |posts, match|
advanced_filter(/^-tags?:([\p{L}0-9,-+]+)$/) do |posts, match|
advanced_filter(/^filetypes?:([a-zA-Z0-9,-_]+)$/) do |posts, match|
FYI: We constructed a relatively simple query which is at the moment good enough for our needs:
SELECT
id, user_id, topic_id, post_number, cooked, raw
FROM posts p
WHERE cooked ~* ‘.*■’
With results:
One of SQL Wizzzards of Ours is constructing a far more sophisticated query, that finds exact position of censored word. It kinda works, but he keeps on grumbling that raw and cooked are not of the same length…which gives us a new appreciation of just how difficult is to store and especially to manage the results of what was found by watched words.
Did you manage to refine this in the end?
Nah, I think it was just too much of a nightmare to find the censored word in the markdown version vs the live version and it was just easier to scan with your eyes (at the moment for our new platform). If someone else has found a way, do let me know!
