In Discourse, you have the ability to enhance the appearance of your Data Explorer query results.
Formatting table results in the Data Explorer can be done by using special syntax in your SQL queries, which allows you to display your data in a more intuitive and visually appealing manner, directly within the Discourse interface.
Formatted Table Results
Here’s a quick overview of the syntax and data fields you can use to format your query results:
SELECT
-- To display a quote, user avatar/username, and a link to the post, use:
id AS post_id,
-- To display the topic title, the count of posts, and a link to the topic, use:
topic_id,
-- To display time in a relative format (hover for full date), use:
updated_at as reltime$updated_at,
-- To display a user avatar/username, and a link to the activity page, use:
user_id,
-- To display a group name and a link to the group, use:
(SELECT id FROM groups WHERE id = 12) AS group_id,
-- To display a category badge, name, and a link to the category, use:
(SELECT id FROM categories WHERE id = 22) AS category_id,
-- To display a badge icon, name, and a link to the badge, use:
(SELECT id FROM badges WHERE id = 2) AS badge_id,
-- To suppress special formatting and use only text, use:
topic_id AS text$topic_id_text,
-- To render a value as HTML, use:
'<h2>hello</h2>' AS html$html,
-- To treat a column as a URL (if named `xyz_url`), and name the link using the format `name,https://example.com`, use:
'Meta,https://meta.discourse.org' AS somecolumnname_url
FROM posts
WHERE topic_id = 32566
LIMIT 1
Below is an example of how your query results might look when formatted using the above syntax:
Individual Examples
Let’s discuss some of these features in more detail:
Post and Topic Links
You can display a post_id as a quote, user avatar/username, and a link to the post using AS post_id. You can also display a topic_id as a topic title, the count of posts, and a link to the topic using AS topic_id.
Examples:
SELECT
p.id AS post_id
FROM
posts p
SELECT
t.id AS topic_id
FROM
topics t
Relative Time
The AS reltime$updated_at statement will display the a timestamp in a relative format. If you hover over a timestamp displayed in this format, you’ll also see the full date.
Example:
SELECT
id,
updated_at AS reltime$updated_at
FROM
posts
ORDER BY updated_at DESC
User, Group, Category, and Badge Links
You can display user avatars/usernames, group names, category names, and badges icons/names, all linked to their respective pages by using AS user_id, AS group_id, AS category_id, and AS badge_id.
Examples:
SELECT
u.id AS user_id
FROM
users u
SELECT
g.id AS group_id
FROM
groups g
SELECT
c.id AS category_id
FROM
categories c
SELECT
b.id as badge_id
FROM
badges b
Text and HTML Rendering
You can display HTML results as rendered HTML by using AS html$html. You can also suppress special formatting to use only text by using AS text$column_name
Example:
SELECT
topic_id,
cooked AS html$html,
topic_id AS text$topic_id_text
FROM
posts
GROUP BY topic_id, cooked
URL Rendering
If you name a column as xyz_url, it will be treated as a URL. You also can name a URL link using the format name,https://example.com.
Example:
SELECT
u.username,
'https://example_site.com/u/' || u.username || '/summary' AS profile_url,
'Profile Page,https://example_site.com/u/' || u.username || '/summary' AS profile_page_url
FROM
users u
WHERE u.username LIKE 'anon%'
Automatic Formatting
There are also certain SQL fields in the Data Explorer that are automatically formatted. These include:
Links
Any field that contains a URL will be automatically formatted as a clickable link.
Example:
SELECT
id,
remote_url
FROM remote_themes
IDs
IDs (like post IDs, topic IDs, user IDs, etc.) are often automatically linked to their respective entities when referenced in foreign tables.
Example:
SELECT
user_id
FROM
topics
