SQL query in Data Explorer to pull latest topics?

data & reporting
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 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.

1 Like
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
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.

2 Likes
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

3 Likes
10

Where is this being shown?

11

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

4 Likes