SQL query in Data Explorer to pull latest topics?

Hey there,

I’m looking to display something like this on my Wordpress homepage:

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!

You might check out this thread What cool badge queries have you come up with?

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?

No. It’s just a trove of sql examples.

Badges are granted based on SQL queries typically.

1 Like

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

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

The "8 hours ago " thing gets done by a rails function. There is probably some function you can find that’ll do that for you.

Yep, with SQL queries you can extract about any data that you need from Discourse.

Re date formatting, I might need to add an ago filter, like this one.

Without AGO-ing, you can still format dates with using native date filter.

You can also format dates directly in you SQL query.

2 Likes

Wohoo, just figured out how to get the “8 hours ago” using the Twig Syntax:

{% set difference = date("now"|date("M j, g:ia T")).diff(date(row[11]|date("M j, g:ia T"))) %}
{% set leftDays = difference.days %}
{% set leftHours = difference.h %}
{% set leftMinutes = difference.i %}
{% if leftDays > 1 %}
  {{ leftDays }} days
{% elseif leftDays == 1 %}
  {{ leftDays }} day
{% elseif leftHours > 1 %}
  {{ leftHours }} hours {{ leftMinutes }} min
{% elseif leftHours == 1 %}
  {{ leftHours }} hour {{ leftMinutes }} min
{% else %}
  {{ leftMinutes }} min
{% endif %}

Now looking something like:

3 Likes

Where is this being shown?

Using @meglio’s Twig Anything (https://twiganything.com/), on Wordpress.

4 Likes