This is an SQL version of the Dashboard Report for User Notes.
This report requires the Discourse User Notes plugin to be enabled.
This dashboard report lists user notes created by staff users within a specific date range. User notes are annotations or comments added by moderators or administrators to a user’s profile, often used to track behavior, issues, or important information about the user.
-- [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
SQL Query Explanation
This report extracts these notes from the plugin_store_rows
table, where they are stored in a JSON format by the user_notes
plugin, and presents them in an easily digestible format.
The query operates in several steps:
- Parameters:
- The query starts by defining two parameters,
:start_date
and:end_date
, to specify the time period for the report. Both date parameters accept the format ofYYYY-MM-DD
.
- The query starts by defining two parameters,
- Common Table Expression (CTE) -
user_notes
: The query begins with a CTE nameduser_notes
that extracts and transforms the relevant data from theplugin_store_rows
table. This table stores various plugin data in a key-value format, where the key for user notes is prefixed withnotes:
followed by the user’s ID. The CTE performs the following operations:- Filters rows where
plugin_name
is'user_notes'
, ensuring only user notes data is selected. - Uses the
json_array_elements
function in a LATERAL join to expand the JSON array stored in thevalue
column into individual JSON objects, each representing a note. - Extracts the user ID from the key by removing the
notes:
prefix and casting the result to an integer. - Extracts the note’s creation date, raw note content, and the ID of the user who created the note from the JSON object.
- Filters rows where
- Main Query:
- Joins the
user_notes
CTE with theusers
table to ensure that only notes for existing users are included. - Filters notes based on the
created_at
date to include only those within the specified date range (:start_date
to:end_date
). - Selects the user ID, moderator user ID (the note creator), note creation date, and the note content.
- Orders the results by the note creation date in ascending order to present the notes chronologically.
- Joins the
Example Results
user | moderator_user | created_at | user_note |
---|---|---|---|
user_1 | staff_user_2 | 2024-01-10 | example user note with HTML formatting |
user_3 | staff_user_4 | 2024-01-14 | this is an example note about user_3 |
… | … | … | … |