Reporte de Dashboard - Notas de Usuario

Este es un informe de panel de control en versión SQL para Notas de Usuario.

Este informe requiere que el plugin Discourse User Notes esté habilitado.

Este informe de panel de control enumera las notas de usuario creadas por usuarios del personal dentro de un rango de fechas específico. Las notas de usuario son anotaciones o comentarios agregados por moderadores o administradores al perfil de un usuario, que a menudo se utilizan para rastrear el comportamiento, los problemas o la información importante sobre el usuario.

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

Explicación de la consulta SQL

Este informe extrae estas notas de la tabla plugin_store_rows, donde se almacenan en formato JSON por el plugin user_notes, y las presenta en un formato fácil de digerir.

La consulta opera en varios pasos:

• Parámetros:
  • La consulta comienza definiendo dos parámetros, :start_date y :end_date, para especificar el período de tiempo del informe. Ambos parámetros de fecha aceptan el formato YYYY-MM-DD.
• Expresión Común de Tabla (CTE) - user_notes: La consulta comienza con una CTE llamada user_notes que extrae y transforma los datos relevantes de la tabla plugin_store_rows. Esta tabla almacena varios datos de plugins en un formato de clave-valor, donde la clave para las notas de usuario tiene el prefijo notes: seguido del ID del usuario. La CTE realiza las siguientes operaciones:
  • Filtra las filas donde plugin_name es 'user_notes', asegurando que solo se seleccionen los datos de notas de usuario.
  • Utiliza la función json_array_elements en una unión LATERAL para expandir la matriz JSON almacenada en la columna value en objetos JSON individuales, cada uno representando una nota.
  • Extrae el ID del usuario de la clave eliminando el prefijo notes: y convirtiendo el resultado a un entero.
  • Extrae la fecha de creación de la nota, el contenido de la nota sin procesar y el ID del usuario que creó la nota del objeto JSON.
• Consulta Principal:
  • Une la CTE user_notes con la tabla users para garantizar que solo se incluyan las notas de usuarios existentes.
  • Filtra las notas según la fecha created_at para incluir solo aquellas dentro del rango de fechas especificado (:start_date a :end_date).
  • Selecciona el ID del usuario, el ID del usuario moderador (el creador de la nota), la fecha de creación de la nota y el contenido de la nota.
  • Ordena los resultados por la fecha de creación de la nota en orden ascendente para presentar las notas cronológicamente.

Resultados de ejemplo