ダッシュボードレポート - ユーザーノート

データとレポート
, ,
1

これは、ユーザーノートのダッシュボードレポートのSQLバージョンです。

:discourse: このレポートを表示するには、Discourse User Notes プラグインが有効になっている必要があります。

このダッシュボードレポートは、指定された期間内にスタッフユーザーによって作成されたユーザーノートを一覧表示します。ユーザーノートは、モデレーターまたは管理者がユーザーのプロフィールに追加する注釈またはコメントであり、ユーザーに関する行動、問題、または重要な情報の追跡によく使用されます。

-- [params]
-- date :start_date = 2024-01-07
-- date :end_date = 2024-02-08

WITH user_notes AS (
    SELECT
        REPLACE(key, 'notes:', '')::int AS user_id,
        notes.value->>'created_at' AS created_at,
        notes.value->>'raw' AS user_note,
        notes.value->>'created_by' AS created_by
    FROM plugin_store_rows,
    LATERAL json_array_elements(value::json) notes
    WHERE plugin_name = 'user_notes'
    ORDER BY 2 DESC
)
SELECT
    un.user_id,
    un.created_by AS moderator_user_id,
    un.created_at::date,
    un.user_note as html$user_note
FROM user_notes un
JOIN users u ON u.id = un.user_id
WHERE un.created_at BETWEEN :start_date AND :end_date
ORDER BY un.created_at ASC

SQLクエリの説明

このレポートは、user_notes プラグインによってJSON形式で保存されている plugin_store_rows テーブルからこれらのノートを抽出し、簡単に理解できる形式で表示します。

クエリはいくつかのステップで実行されます。

  • パラメータ:
    • クエリは、レポートの期間を指定するために、:start_date と :end_date の2つのパラメータを定義することから始まります。両方の日付パラメータは YYYY-MM-DD の形式を受け入れます。
  • 共通テーブル式 (CTE) - user_notes: クエリは user_notes という名前のCTEから始まり、plugin_store_rows テーブルから関連データを抽出し変換します。このテーブルは、キーが notes: で始まりユーザーIDが続くというキーと値の形式でさまざまなプラグインデータを保存します。CTEは次の操作を実行します。
    • plugin_name が 'user_notes' である行をフィルタリングし、ユーザーノートのデータのみが選択されるようにします。
    • LATERAL結合で json_array_elements 関数を使用して、value 列に格納されているJSON配列を個々のJSONオブジェクト（各ノートを表す）に展開します。
    • notes: プレフィックスを削除し、結果を整数にキャストして、キーからユーザーIDを抽出します。
    • JSONオブジェクトから、ノートの作成日、生のノートコンテンツ、およびノートを作成したユーザーのIDを抽出します。
  • メインクエリ:
    • user_notes CTE を users テーブルと結合して、既存のユーザーのノートのみが含まれるようにします。
    • created_at 日付に基づいてノートをフィルタリングし、指定された日付範囲（:start_date から :end_date）内のノートのみを含めます。
    • ユーザーID、モデレーターユーザーID（ノート作成者）、ノート作成日、およびノートコンテンツを選択します。
    • ノートを時系列で表示するために、ノート作成日を昇順で並べ替えます。

結果例

user moderator_user created_at user_note
user_1 staff_user_2 2024-01-10 example user note with HTML formatting
user_3 staff_user_4 2024-01-14 this is an example note about user_3
… … … …
「いいね！」 3