איך לסנן נושאים לפי קבוצת משתמשים

סיכום מבוסס בינה מלאכותית

In a discussion titled “How to filter topics by user group” under the “Data & reporting” category with the tag “sql-query”, kenjdarhan seeks a way to filter forum topics where the latest reply is from a user in a specific “clients” group. olivia suggests using SQL via the Data Explorer plugin, providing an initial query that identifies topics where the last post is authored by a client. JammyDodger notes the absence of a last_post_id field, which olivia confirms. JammyDodger then refines the query to exclude deleted topics/posts and filter for regular topics, while also noting it may miss cases where the original poster replies again. kenjdarhan clarifies the need: topics where the latest reply is from a client — meaning no team member has replied yet, as those would be handled by colleagues. JammyDodger adapts the solution to find topics created by clients with no replies from the “team” group, using group IDs for efficiency, and provides a helper query to look up group IDs. kenjdarhan thanks Jammy and plans to test the solution.

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. :face_with_spiral_eyes:

No worries. I think the edited one works. :+1: :slight_smile: 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. :slight_smile: 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. :+1:

Thanks Jammy. I will try that.