SQL query in Data Explorer to pull latest topics?

Community Building Data & reporting

AI-generated summary

The discussion revolves around creating a SQL query in Discourse’s Data Explorer to pull the latest topics, which will be displayed on a Wordpress homepage using the Twig Anything plugin.

sunnyt7 is seeking help with writing the SQL query, as they have no experience with it. pfaffman shares a link to a thread with SQL examples, but it’s mostly about badges. sunnyt7 then shares their progress with the SQL query, which pulls the necessary fields, including user avatars.

The query is as follows:

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

sunnyt7 then encounters issues with formatting the timestamp and displaying replies instead of posts. pfaffman suggests using a Rails function to format the timestamp, while meglio recommends using a Twig filter or formatting the date directly in the SQL query.

sunnyt7 eventually figures out how to format the timestamp using Twig syntax. The final result is a displayed list of latest topics on their Wordpress homepage, with formatted timestamps.

1

Hey there,

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

image.png1130×417 40.9 KB

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!

2

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

3

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?

4

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

5

Badges are granted based on SQL queries typically.

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?
7

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.

8

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.

9

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:

image.png883×354 29.3 KB

10

Where is this being shown?

11

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