SQL query in Data Explorer to pull latest topics?


(Sunny T) #1

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!


(Jay Pfaffman) #2

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


(Sunny T) #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?


(Jay Pfaffman) #4

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


(Robby O'Connor) #5

Badges are granted based on SQL queries typically.


(Sunny T) #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):

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?

(Jay Pfaffman) #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.


(Anton) #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.


(Sunny T) #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:


(Robby O'Connor) #10

Where is this being shown?


(Sunny T) #11

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