SQL query in Data Explorer to pull latest topics?

data & reporting
6

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):

image.png898×371 32.2 KB

Almost there. Some issues I’ve run into:

  • 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?
1 Like