ユーザーグループでトピックをフィルタリングする方法

データとレポート
1

投稿者の所属ユーザーグループに基づいて、フォーラムの既存トピックをフィルタリングする方法はありますか？

例えば、ユーザーグループを「クライアント」として定義した後、クライアントユーザーがまだ返信していないすべてのトピックをフィルタリングしたいとします。

何かお手伝いいただければ幸いです。

2

もしフォーラムにData Explorerプラグインがインストールされていれば、Discourseデータベースからこの情報を取得するためにSQLクエリを記述できます。このクエリは、トピックの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
「いいね！」 1
3

t.last_post_id はありますか？

「いいね！」 1
4

見つけてくれてありがとう。topics テーブルには last_post_id フィールドはありません。:face_with_spiral_eyes:

「いいね！」 2
5

心配いりません。編集されたもので動作すると思います。:+1: :slight_smile: 削除されたトピック/投稿、PM、およびウィスパー/スモールアクション投稿をフィルタリングするために、いくつか追加することも役立つことを付け加えておきます。たとえば次のようになります。

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。具体的には、クライアントが作成し、チームメンバーから返信があり、未解決のトピックということでしょうか？そのようなものでしょうか？

「いいね！」 4
6

こんにちは、Jammyさん。

これは、最新の返信が「client」ユーザーグループのユーザーによって作成されたトピックにすぎません。

トピックにクライアント以外のユーザー（つまり、私の同僚の1人）が返信した場合、返信した同僚が問題を解決し続けるため、そのトピックに集中する必要はありません。

「いいね！」 1
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) -- 'team' グループの group_id
  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) -- 'client' グループの group_id
  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

グループ名を使用するよりも、group_ids を知っている方がスムーズに動作します。グループのJSON（例: /g/admins.json）で確認できますが、次のようなものを使って簡単に検索することもできます。


-- [params]
-- string :group_name

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

また、パラメータ入力ボックスを認識させるために、クエリをデータエクスプローラーに初めてコピー＆ペーストしたときは、ページをリフレッシュする必要があります。:+1:

8

Jammyさん、ありがとうございます。試してみます。