Is there any way that I can filter the existing topics on the forum based on which user group the author of the latest reply belong to?
For example, after I define a group of users as “clients”, I want to filter all the topics in which the client users have not been responded to.
Thanks for any help.
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
I’m not sure we have a t.last_post_id?
Thanks for finding that, there isn’t a last_post_id field in the topics table. 
No worries. I think the edited one works. 
Just to note, it can also be useful to put a few extras in to filter deleted topics/posts, PMs, and Whispers/Small Action posts too. Something like:
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'
AND t.deleted_at ISNULL
AND p.deleted_at ISNULL
AND p.post_type = 1
AND t.archetype = 'regular'
GROUP BY t.id
HAVING MAX(p.user_id) = last_post_user_id
ORDER BY MAX(p.created_at) DESC
Though I don’t think this would catch any topic where the OP replied again (eg. to say ‘thanks’ etc), so it may need a different method depending on what you’re after @kenjdarhan. Would it be specifically client created topics which had received a reply from a team member and were also unsolved? Something like that?
Hello Jammy,
It is simply the topics in which the latest reply is created by a user from the “client” user group.
If a topic has been replied by a non-client user (i.e., one of my colleagues), I will not need to focus on that topic because the colleague who replied will continue to solve the problem in the topic if the problem has not been solved.
This sounds like you need something a little different. Every forum is slightly different, so solutions may need to be adapted, but I would describe this brief as “topics created by users in the ‘client’ group, with no reply from a user in the ‘team’ group”. If that’s the case then you could adapt this query with the correct group_ids and it should do what you want:
-- [params]
-- date :start_date = 01/01/2023
-- date :end_date = 01/06/2023
WITH team_replies AS (
SELECT topic_id
FROM posts
WHERE user_id IN (SELECT user_id FROM group_users WHERE group_id = 3) --group_id of your 'team' group
AND post_number <> 1
AND deleted_at ISNULL
AND post_type = 1
GROUP BY topic_id
)
SELECT t.created_at::date,
t.id AS topic_id,
MIN(p.id) AS first_post_id
FROM topics t
JOIN posts p ON p.topic_id = t.id
WHERE t.user_id IN (SELECT user_id FROM group_users WHERE group_id = 45) -- group_id of your 'client' group
AND t.id NOT IN (SELECT topic_id FROM team_replies)
AND t.created_at BETWEEN :start_date AND :end_date
AND t.deleted_at ISNULL
AND t.archetype = 'regular'
GROUP BY t.id
ORDER BY 1 ASC
This does work smoother if you know the group_ids rather than using group names. You can find them in the group JSON (eg. /g/admins.json), though you can also use something like this as a cheeky lookup:
-- [params]
-- string :group_name
SELECT name,
id
FROM groups
WHERE name LIKE LOWER (:group_name)
And you’ll also need to refresh the page the very first time you copy paste the queries into your data explorer to pick up the parameter input boxes.
Thanks Jammy. I will try that.