How to filter topics by user group

Data & reporting
2

If your forum has the Data Explorer Plugin installed, you could write SQL queries to fetch this information from the Discourse database. This query attempts to fetch the ID and title of topics, and the user ID of the last post in the topic. It only selects topics where the last post was made by a user in the ‘clients’ group.

This is just an example.

SELECT 
    t.id AS topic_id, 
    t.title AS topic_title, 
    (SELECT user_id FROM posts WHERE topic_id = t.id ORDER BY created_at DESC LIMIT 1) AS last_post_user_id
FROM 
    topics t
JOIN
    posts p ON p.topic_id = t.id
JOIN 
    group_users gu ON gu.user_id = p.user_id
JOIN 
    groups g ON g.id = gu.group_id
WHERE 
    g.name = 'clients'
GROUP BY 
    t.id
HAVING 
    MAX(p.user_id) = last_post_user_id
ORDER BY 
    MAX(p.created_at) DESC
1 Like