I am an admin for a discourse forum: forum.testproject.io and want to filter threads that have not yet been answered specifically by Staff members.
The https://forum.testproject.io/latest?max_posts=1 URL filters out only threads with 0 replies, however, I want to be able to filter any thread that has not received a reply by our staff (the thread might already have replies from the community, thus showing replies > 0, but those are not our replies).
Here’s an example from your forum - here’s a thread that someone asked a question and ppl from your community responded. But how would you (Discourse Staff) know that this thread is basically unanswered by you?
Hi @osioke,
Thanks for the quick response!
I personally do not have SQL skills, is there anyone that can help with that from your team? (being that we are a paying customer
The query below lists all topics that have not been responded to by the Staff, even if the topic has already received a response from the community.
--[params]
--date :start_date = 2021-01-01
--date :end_date = 2021-12-31
SELECT t.id topic_id, category_id
FROM topics t
WHERE t.deleted_at IS NULL
AND t.category_id IS NOT NULL
AND t.created_at::date BETWEEN :start_date AND :end_date
AND t.id NOT IN (
SELECT p.topic_id
FROM posts p
JOIN users u ON p.user_id = u.id
WHERE u.admin = 't' OR u.moderator = 't'
AND p.post_number > 1
)
ORDER BY topic_id DESC