Informe del panel - Banderas

Datos e informes
, ,
1

Este es una versión SQL del Informe del Panel para Banderas

Este informe proporciona un recuento de publicaciones marcadas dentro de una categoría específica opcional (y opcionalmente sus subcategorías) durante un rango de fechas determinado. Esta información puede ser útil para los gerentes de comunidad y moderadores para comprender el volumen de banderas planteadas en ciertas partes del foro, lo que puede ayudar a identificar tendencias y áreas que pueden requerir atención o moderación adicional.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01
-- null int :category_id = 0
-- boolean :include_subcategories = false

WITH RECURSIVE subcategories AS (
    SELECT id FROM categories WHERE id = :category_id
    UNION ALL
    SELECT c.id FROM categories c
    INNER JOIN subcategories sc ON c.parent_category_id = sc.id
)

SELECT
    DATE(r.created_at) AS flag_date,
    COUNT(r.id) AS flags_count
FROM reviewables r
LEFT JOIN categories c ON r.category_id = c.id
WHERE r.type = 'ReviewableFlaggedPost'
    AND r.created_at::DATE BETWEEN :start_date AND :end_date
    AND (:category_id = 0 OR (
        :include_subcategories
        AND r.category_id IN (SELECT id FROM subcategories)
        OR NOT :include_subcategories
        AND r.category_id = :category_id
    ))
GROUP BY flag_date
ORDER BY flag_date

Explicación de la consulta SQL

La consulta SQL está diseñada para contar el número de banderas (que están representadas por registros ReviewableFlaggedPost en la tabla reviewables) creadas en cada fecha dentro del rango de fechas y categoría especificados. Utiliza una Expresión de Tabla Común (CTE) recursiva para incluir opcionalmente subcategorías de la categoría elegida.

Parámetros

  • :start_date: El inicio del rango de fechas para el cual contar las banderas.
  • :end_date: El final del rango de fechas para el cual contar las banderas.
  • :category_id: El ID de la categoría para la cual contar las banderas. Si se establece en 0, el filtro por categoría no se aplica.
  • :include_subcategories: Un valor booleano que determina si se incluyen las subcategorías de la categoría especificada en el recuento.

Desglose de la consulta

  1. CTE Recursiva (subcategories):
    • Esta CTE comienza seleccionando la categoría con el ID proporcionado por el parámetro :category_id.
    • Luego, selecciona recursivamente todas las subcategorías de esa categoría uniéndose a la tabla categories con la CTE subcategories en el campo parent_category_id.
    • El resultado es una lista de todas las subcategorías (incluida la categoría inicial) que se utilizarán para filtrar.
  2. Consulta Principal:
    • La consulta selecciona dos columnas: la fecha de creación de la bandera (flag_date) y el recuento de banderas (flags_count) para esa fecha.
    • Une la tabla reviewables con la tabla categories para asociar cada bandera con su categoría.
  3. Filtros:
    • La consulta filtra los registros de reviewables para incluir solo aquellos de tipo ReviewableFlaggedPost, que representa las publicaciones marcadas.
    • Asegura que la fecha created_at de las banderas caiga dentro del rango de fechas especificado (:start_date a :end_date).
    • Aplica un filtro condicional basado en los parámetros :category_id y :include_subcategories:
      • Si :category_id es 0, no se aplica ningún filtro de categoría.
      • Si :include_subcategories es true, la consulta incluye banderas de la categoría inicial y todas sus subcategorías.
      • Si :include_subcategories es false, la consulta incluye banderas solo de la categoría inicial.
  4. Agrupación y Ordenación:
    • Los resultados se agrupan por la fecha de creación de la bandera para proporcionar un recuento por día.
    • Los resultados se ordenan por fecha para presentar los datos en una secuencia cronológica.

Resultados de ejemplo

flag_date flags_count
2023-11-19 2
2023-11-22 1
2023-11-23 1
2023-11-24 2
2023-11-26 1
… …
1 me gusta