This is an SQL version of the Dashboard Report for Post Edits.
This dashboard report shows posts that were edited over a specific date range, and includes information about the editor, author of the post, and the reason the post was edited. This report is designed to help administrators monitor and understand the editing activity occurring within their forums, particularly focusing on cases where users other than the original authors make edits to posts.
-- [params]
-- date :start_date = 2023-12-16
-- date :end_date = 2024-01-16
SELECT
pr.created_at::date as day,
pr.post_id as post_id,
pr.user_id AS editor_user_id,
p.user_id AS author_user_id,
p.edit_reason
FROM post_revisions pr
JOIN posts p ON p.id = pr.post_id
WHERE pr.created_at BETWEEN :start_date AND :end_date
AND pr.user_id > 0
AND pr.user_id <> p.user_id
ORDER BY pr.created_at ASC
SQL Query Explanation
- Parameter Declaration:
- The query accepts two parameters,
:start_date
and:end_date
, which define the date range for the report. Both date parameters accept the date format ofYYYY-MM-DD
.
- The query accepts two parameters,
- Data Selection: The
SELECT
statement specifies the columns that will appear in the final report. These include:pr.created_at::date as day
: The date a post revision was created, and it’s cast to adate
type to remove the time part.pr.post_id
: The ID of the post that was edited.pr.user_id AS editor_user_id
: The ID of the user who made the edit. This is aliased aseditor_user_id
to differentiate it from the author of the original post.p.user_id AS author_user_id
: The ID of the user who authored the original post, aliased asauthor_user_id
.p.edit_reason
: The reason provided for the edit, if any was given.
- Joining Tables: The
FROM
clause includes aJOIN
statement that combines thepost_revisions
table (pr
) with theposts
table (p
). The join is done on the condition thatpost_revisions.post_id
matchesposts.id
, thus linking a revision to the original post details. - Filters: The
WHERE
clause applies several filters to the data:pr.created_at BETWEEN :start_date AND :end_date
: Only includes post revisions created within the date range specified by the parameters.pr.user_id > 0
: Ensures that the editor is an actual user and not a system operation (a user_id of 0 usually indicates a system operation).pr.user_id <> p.user_id
: Filters out revisions made by the post’s original author, focusing only on edits made by different users.
- Ordering Results: The
ORDER BY pr.created_at ASC
clause orders the results from oldest to newest based on the timestamp of when each post revision was made.
Example Results
day | post | editor_user | author_user | edit_reason |
---|---|---|---|---|
2023-12-20 | post_abc | user1 | user2 | Example Edit Reason |
2023-12-21 | post_dfg | user3 | user4 | Example Edit Reason |
2023-12-24 | post_xyz | user5 | user6 | NULL |