I’m using @meglio’s Twig Anything to do this, which can pull data from any JSON feed. I’ve tried just using https://meta.discourse.org/latest.json, but the issue is that it’s difficult to pull the user avatars, as the users array does not use User ID as a key.
@meglio recommended that I create a special SQL request in the Discourse Data Explorer plugin, and extract all fields needed in resulting rows.
Unfortunately I have no experience in writing SQL queries. Would anyone be able to help with this? (been told it should be a piece of cake).
Thanks for sharing the link. I just took a look through, and it seems like it is mostly about badges? Is there a specific post in particular that you’re referring to?
So I spent a few hours this afternoon learning SQL queries. Lol. Here’s where I got:
SELECT
t.last_post_user_id, ua.custom_upload_id, u.username, u.username_lower, t.title, t.slug, t.id, t.category_id, c.name, c.color, t.posts_count, t.last_posted_at
FROM
topics t
LEFT JOIN categories c ON c.id = t.category_id
LEFT JOIN users u ON u.id = t.last_post_user_id
LEFT JOIN user_avatars ua ON ua.user_id = t.last_post_user_id
WHERE
t.archetype = 'regular'
AND
t.last_posted_at IS NOT NULL
AND
t.visible IS TRUE
AND
t.deleted_at IS NULL
ORDER BY
t.last_posted_at DESC
LIMIT
10
And then using Twig Anything, am now displaying this on my site (haven’t stylized it yet):
The timestamp is tough. I can manually set a timezone, but I wish I could have it auto-set per user. Better yet, it would just say something like “8h” or “8 hours ago”. Not too sure how to do that.
I wanted to use Replies instead of Posts, but reply_count in the database doesn’t seem to be too accurate? It was showing a 1 instead of a 4 for the first topic. What does that field mean?