This is an SQL version of the Dashboard Report for Flags
This report provides a count of flagged posts within an optional specific category (and optionally its subcategories) over a given date range. This information can be useful for community managers and moderators to understand the volume of flags raised in certain parts of the forum, which can help in identifying trends and areas that may require additional attention or moderation.
-- [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
SQL Query Explanation
The SQL query is designed to count the number of flags (which are represented by ReviewableFlaggedPost
records in the reviewables
table) created on each date within the specified date range and category. It uses a recursive Common Table Expression (CTE) to optionally include subcategories of the chosen category.
Parameters
:start_date
: The beginning of the date range for which to count flags.:end_date
: The end of the date range for which to count flags.:category_id
: The ID of the category for which to count flags. If set to 0, the filter on category is not applied.:include_subcategories
: A boolean value that determines whether to include subcategories of the specified category in the count.
Query Breakdown
- Recursive CTE (
subcategories
):
- This CTE starts by selecting the category with the ID provided by the
:category_id
parameter. - It then recursively selects all subcategories of that category by joining the
categories
table with thesubcategories
CTE on theparent_category_id
field. - The result is a list of all subcategories (including the initial category) that will be used for filtering.
- Main Query:
- The query selects two columns: the date of flag creation (
flag_date
) and the count of flags (flags_count
) for that date. - It joins the
reviewables
table with thecategories
table to associate each flag with its category.
- Filters:
- The query filters
reviewables
records to only include those of typeReviewableFlaggedPost
, which represents flagged posts. - It ensures that the
created_at
date of the flags falls within the specified date range (:start_date
to:end_date
). - It applies a conditional filter based on the
:category_id
and:include_subcategories
parameters:- If
:category_id
is 0, no category filter is applied. - If
:include_subcategories
istrue
, the query includes flags from the initial category and all its subcategories. - If
:include_subcategories
isfalse
, the query includes flags only from the initial category.
- If
- Grouping and Ordering:
- The results are grouped by the date of flag creation to provide a count per day.
- The results are ordered by the date to present the data in a chronological sequence.
Example Results
flag_date | flags_count |
---|---|
2023-11-19 | 2 |
2023-11-22 | 1 |
2023-11-23 | 1 |
2023-11-24 | 2 |
2023-11-26 | 1 |
… | … |