Rapport de tableau de bord - Notes utilisateur

Données et rapports
, ,
1

Ceci est une version SQL du rapport de tableau de bord pour les notes d’utilisateur.

:discourse: Ce rapport nécessite que le plugin Discourse User Notes soit activé.

Ce rapport de tableau de bord répertorie les notes d’utilisateur créées par les utilisateurs du personnel dans une plage de dates spécifique. Les notes d’utilisateur sont des annotations ou des commentaires ajoutés par les modérateurs ou les administrateurs au profil d’un utilisateur, souvent utilisés pour suivre le comportement, les problèmes ou les informations importantes concernant l’utilisateur.

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

Explication de la requête SQL

Ce rapport extrait ces notes de la table plugin_store_rows, où elles sont stockées dans un format JSON par le plugin user_notes, et les présente dans un format facilement digestible.

La requête fonctionne en plusieurs étapes :

  • Paramètres :
    • La requête commence par définir deux paramètres, :start_date et :end_date, pour spécifier la période du rapport. Les deux paramètres de date acceptent le format AAAA-MM-JJ.
  • Expression de table commune (CTE) - user_notes : La requête commence par une CTE nommée user_notes qui extrait et transforme les données pertinentes de la table plugin_store_rows. Cette table stocke diverses données de plugin dans un format clé-valeur, où la clé pour les notes d’utilisateur est préfixée par notes: suivi de l’ID de l’utilisateur. La CTE effectue les opérations suivantes :
    • Filtre les lignes où plugin_name est 'user_notes', garantissant que seules les données de notes d’utilisateur sont sélectionnées.
    • Utilise la fonction json_array_elements dans une jointure LATERAL pour développer le tableau JSON stocké dans la colonne value en objets JSON individuels, chacun représentant une note.
    • Extrait l’ID de l’utilisateur de la clé en supprimant le préfixe notes: et en convertissant le résultat en entier.
    • Extrait la date de création de la note, le contenu brut de la note et l’ID de l’utilisateur qui a créé la note à partir de l’objet JSON.
  • Requête principale :
    • Joint la CTE user_notes avec la table users pour s’assurer que seules les notes des utilisateurs existants sont incluses.
    • Filtre les notes en fonction de la date created_at pour inclure uniquement celles comprises dans la plage de dates spécifiée (:start_date à :end_date).
    • Sélectionne l’ID de l’utilisateur, l’ID de l’utilisateur modérateur (le créateur de la note), la date de création de la note et le contenu de la note.
    • Trie les résultats par date de création de la note dans l’ordre croissant pour présenter les notes chronologiquement.

Exemple de résultats

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 « J'aime »