Relatório do Painel - Notas do Usuário

Dados e relatórios
, ,
1

Este é um relatório de painel SQL para Notas do Usuário.

:discourse: Este relatório requer que o plugin Discourse User Notes esteja ativado.

Este relatório de painel lista as notas do usuário criadas por usuários da equipe dentro de um intervalo de datas específico. As notas do usuário são anotações ou comentários adicionados por moderadores ou administradores ao perfil de um usuário, frequentemente usados para rastrear comportamento, problemas ou informações importantes sobre o usuário.

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

Explicação da Consulta SQL

Este relatório extrai essas notas da tabela plugin_store_rows, onde são armazenadas em formato JSON pelo plugin user_notes, e as apresenta em um formato de fácil digestão.

A consulta opera em várias etapas:

  • Parâmetros:
    • A consulta começa definindo dois parâmetros, :start_date e :end_date, para especificar o período de tempo do relatório. Ambos os parâmetros de data aceitam o formato AAAA-MM-DD.
  • Expressão de Tabela Comum (CTE) - user_notes: A consulta começa com uma CTE chamada user_notes que extrai e transforma os dados relevantes da tabela plugin_store_rows. Esta tabela armazena vários dados de plugin em formato de chave-valor, onde a chave para notas do usuário é prefixada com notes: seguido pelo ID do usuário. A CTE realiza as seguintes operações:
    • Filtra linhas onde plugin_name é 'user_notes', garantindo que apenas os dados de notas do usuário sejam selecionados.
    • Usa a função json_array_elements em um join LATERAL para expandir o array JSON armazenado na coluna value em objetos JSON individuais, cada um representando uma nota.
    • Extrai o ID do usuário da chave removendo o prefixo notes: e convertendo o resultado para um inteiro.
    • Extrai a data de criação da nota, o conteúdo bruto da nota e o ID do usuário que criou a nota do objeto JSON.
  • Consulta Principal:
    • Faz um join da CTE user_notes com a tabela users para garantir que apenas notas de usuários existentes sejam incluídas.
    • Filtra as notas com base na data created_at para incluir apenas aquelas dentro do intervalo de datas especificado (:start_date a :end_date).
    • Seleciona o ID do usuário, o ID do usuário moderador (o criador da nota), a data de criação da nota e o conteúdo da nota.
    • Ordena os resultados pela data de criação da nota em ordem crescente para apresentar as notas cronologicamente.

Exemplo de Resultados

user moderator_user created_at user_note
user_1 staff_user_2 2024-01-10 exemplo de nota do usuário com formatação HTML
user_3 staff_user_4 2024-01-14 esta é uma nota de exemplo sobre user_3
… … … …
3 curtidas