Filter topics not answered by Staff

data & reporting
1

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?

Thank you :slight_smile:
Karen

1 Like
2

You would need to create a Data Explorer query for this and to write the query, you’d need SQL skills or know how to write SQL queries.

If Data Explorer doesn’t sound familiar to you, please see:

If you still have questions about how to write a Data Explorer query, you can create a new support topic and tag it with data-explorer.

1 Like
3

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 :slight_smile:

Thank you,
Karen

7

Hello Karen,

Our lower-tier plans are usually self-serve, but we can help with this. My colleague will reply with something shortly.

If you’ll need some more queries, we have a wonderful marketplace to help with this.

1 Like
8

Hi Karen,

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
5 Likes