Dashboard-Bericht - Benutzernotizen

Dies ist eine SQL-Version des Dashboard-Berichts für Benutzerhinweise.

Für diesen Bericht muss das Plugin Discourse User Notes aktiviert sein.

Dieser Dashboard-Bericht listet Benutzerhinweise auf, die von Staff-Benutzern innerhalb eines bestimmten Datumsbereichs erstellt wurden. Benutzerhinweise sind Anmerkungen oder Kommentare, die von Moderatoren oder Administratoren zum Profil eines Benutzers hinzugefügt werden und oft zur Verfolgung des Verhaltens, von Problemen oder wichtigen Informationen über den Benutzer verwendet werden.

-- [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

Erklärung der SQL-Abfrage

Dieser Bericht extrahiert diese Hinweise aus der Tabelle plugin_store_rows, wo sie vom Plugin user_notes im JSON-Format gespeichert werden, und präsentiert sie in einem leicht verständlichen Format.

Die Abfrage läuft in mehreren Schritten ab:

• Parameter:
  • Die Abfrage beginnt mit der Definition von zwei Parametern, :start_date und :end_date, um den Zeitraum für den Bericht festzulegen. Beide Datumsparameter akzeptieren das Format JJJJ-MM-TT.
• Common Table Expression (CTE) - user_notes: Die Abfrage beginnt mit einer CTE namens user_notes, die die relevanten Daten aus der Tabelle plugin_store_rows extrahiert und transformiert. Diese Tabelle speichert verschiedene Plugin-Daten im Schlüssel-Wert-Format, wobei der Schlüssel für Benutzerhinweise mit notes: gefolgt von der Benutzer-ID präfixiert ist. Die CTE führt die folgenden Operationen durch:
  • Filtert Zeilen, bei denen plugin_name 'user_notes' ist, um sicherzustellen, dass nur Benutzerhinweise ausgewählt werden.
  • Verwendet die Funktion json_array_elements in einem LATERAL JOIN, um das in der Spalte value gespeicherte JSON-Array in einzelne JSON-Objekte zu erweitern, die jeweils einen Hinweis darstellen.
  • Extrahiert die Benutzer-ID aus dem Schlüssel, indem das Präfix notes: entfernt und das Ergebnis in einen Integer umgewandelt wird.
  • Extrahiert das Erstellungsdatum des Hinweises, den rohen Inhalt des Hinweises und die ID des Benutzers, der den Hinweis erstellt hat, aus dem JSON-Objekt.
• Hauptabfrage:
  • Verknüpft die user_notes-CTE mit der users-Tabelle, um sicherzustellen, dass nur Hinweise für vorhandene Benutzer enthalten sind.
  • Filtert Hinweise basierend auf dem created_at-Datum, um nur diejenigen innerhalb des angegebenen Zeitraums (:start_date bis :end_date) einzuschließen.
  • Wählt die Benutzer-ID, die Moderator-Benutzer-ID (den Ersteller des Hinweises), das Erstellungsdatum des Hinweises und den Inhalt des Hinweises aus.
  • Sortiert die Ergebnisse nach dem Erstellungsdatum des Hinweises in aufsteigender Reihenfolge, um die Hinweise chronologisch darzustellen.

Beispielergebnisse