Как фильтровать темы по группе пользователей

Создание сообщества Данные и отчетность
1

Есть ли способ отфильтровать существующие темы на форуме в зависимости от того, к какой группе пользователей принадлежит автор последнего ответа?

Например, после того как я определю группу пользователей как «клиенты», я хочу отфильтровать все темы, в которых не было ответов от пользователей этой группы.

Спасибо за любую помощь.

2

Если на вашем форуме установлен плагин Data Explorer, вы можете написать SQL-запросы для получения этой информации из базы данных Discourse. Этот запрос пытается извлечь ID и заголовок тем, а также ID пользователя, сделавшего последний пост в теме. Он выбирает только те темы, где последний пост был сделан пользователем из группы «clients».

Это лишь пример.

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
3

Я не уверен, что у нас есть t.last_post_id?

4

Спасибо, что заметили. В таблице topics нет поля last_post_id. :face_with_spiral_eyes:

5

Без проблем. Думаю, отредактированный вариант работает. :+1: :slight_smile: Только на заметку: также может быть полезно добавить несколько условий для фильтрации удалённых тем/сообщений, личных сообщений, а также постов «Шёпот»/«Малые действия». Например:

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

Однако, я не думаю, что это позволит отловить темы, где автор темы (OP) снова ответил (например, чтобы сказать «спасибо» и т. п.), поэтому, возможно, потребуется другой подход в зависимости от того, что именно вы ищете, @kenjdarhan. Речь именно о темах, созданных клиентами, которые получили ответ от члена команды и при этом остались нерешёнными? Что-то вроде этого?

6

Привет, Jammy,

Речь просто о темах, в которых последнее сообщение оставил пользователь из группы «клиенты».

Если в тему ответил не клиент (например, один из моих коллег), мне не нужно уделять ей особое внимание, так как коллега, оставивший ответ, продолжит решать проблему в этой теме, если она ещё не решена.

7

Похоже, вам нужно что-то немного другое. :slight_smile: Каждый форум немного отличается, поэтому решения могут потребовать адаптации, но я бы описал вашу задачу как «темы, созданные пользователями из группы «client», без ответов от пользователей из группы «team»». Если это так, то вы можете адаптировать этот запрос, указав правильные group_ids, и он должен сделать то, что вам нужно:

-- [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 вашей группы «team»
  AND post_number <> 1
  AND deleted_at IS NULL
  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 вашей группы «client»
  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 IS NULL
  AND t.archetype = 'regular'
GROUP BY t.id
ORDER BY 1 ASC

Это работает более гладко, если вы знаете group_ids, а не используете названия групп. Их можно найти в JSON-файле группы (например, /g/admins.json), хотя также можно использовать что-то вроде этого для быстрого поиска:


-- [params]
-- string :group_name

SELECT name,
       id
FROM groups
WHERE name LIKE LOWER (:group_name)

Также вам нужно будет обновить страницу в первый раз после копирования и вставки запросов в ваш Data Explorer, чтобы появились поля ввода параметров. :+1:

8

Спасибо, Джамми. Я попробую.