Report Dashboard - Note Utente

Dati e reportistica
, ,
1

Questo è una versione SQL del report della dashboard per le note utente.

:discourse: Questo report richiede che il plugin Discourse User Notes sia abilitato.

Questo report della dashboard elenca le note utente create dagli utenti dello staff all’interno di un intervallo di date specifico. Le note utente sono annotazioni o commenti aggiunti dai moderatori o dagli amministratori al profilo di un utente, spesso utilizzati per tracciare il comportamento, i problemi o le informazioni importanti sull’utente.

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

Spiegazione della query SQL

Questo report estrae queste note dalla tabella plugin_store_rows, dove sono memorizzate in formato JSON dal plugin user_notes, e le presenta in un formato facilmente digeribile.

La query opera in diversi passaggi:

  • Parametri:
    • La query inizia definendo due parametri, :start_date e :end_date, per specificare il periodo di tempo per il report. Entrambi i parametri di data accettano il formato AAAA-MM-GG.
  • Common Table Expression (CTE) - user_notes: La query inizia con una CTE denominata user_notes che estrae e trasforma i dati pertinenti dalla tabella plugin_store_rows. Questa tabella memorizza vari dati dei plugin in un formato chiave-valore, dove la chiave per le note utente è preceduta da notes: seguito dall’ID utente. La CTE esegue le seguenti operazioni:
    • Filtra le righe in cui plugin_name è 'user_notes', assicurando che vengano selezionati solo i dati delle note utente.
    • Utilizza la funzione json_array_elements in un join LATERAL per espandere l’array JSON memorizzato nella colonna value in singoli oggetti JSON, ognuno dei quali rappresenta una nota.
    • Estrae l’ID utente dalla chiave rimuovendo il prefisso notes: e convertendo il risultato in un intero.
    • Estrae la data di creazione della nota, il contenuto grezzo della nota e l’ID dell’utente che ha creato la nota dall’oggetto JSON.
  • Query principale:
    • Esegue un join tra la CTE user_notes e la tabella users per garantire che vengano incluse solo le note per gli utenti esistenti.
    • Filtra le note in base alla data created_at per includere solo quelle all’interno dell’intervallo di date specificato (:start_date a :end_date).
    • Seleziona l’ID utente, l’ID utente moderatore (il creatore della nota), la data di creazione della nota e il contenuto della nota.
    • Ordina i risultati in base alla data di creazione della nota in ordine crescente per presentare le note cronologicamente.

Risultati di esempio

user moderator_user created_at user_note
user_1 staff_user_2 2024-01-10 esempio di nota utente con formattazione HTML
user_3 staff_user_4 2024-01-14 questa è una nota di esempio sull’utente_3
… … … …
3 Mi Piace