# SQL query in Data Explorer to pull latest topics?
**URL:** https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [June 4, 2017, 3:47pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908 "2017-06-04T15:47:22Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [June 4, 2017, 3:47pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/1 "2017-06-04T15:47:22Z")
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](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](https://meta.discourse.org/t/32566?silent=true) 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!
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [June 4, 2017, 4:35pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/2 "2017-06-04T16:35:47Z")
You might check out this thread [[Superseded] What cool badge queries have you come up with?](https://meta.discourse.org/t/what-cool-badge-queries-have-you-come-up-with/18978)
---
### Author:  [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [June 4, 2017, 6:17pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/3 "2017-06-04T18:17:47Z")
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?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [June 4, 2017, 7:06pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/4 "2017-06-04T19:06:55Z")
No. It’s just a trove of sql examples.
---
### Author:  [@robbyoconnor](https://meta.discourse.org/u/robbyoconnor)
#### Post date: [June 4, 2017, 7:07pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/5 "2017-06-04T19:07:37Z")
> [@pfaffman](#):
>
> No. It’s just a trove of sql examples.
Badges are granted based on SQL queries typically.
---
### Author:  [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [June 5, 2017, 3:21am UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/6 "2017-06-05T03:21:42Z")
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?
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [June 5, 2017, 2:31pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/7 "2017-06-05T14:31:42Z")
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.
---
### Author:  [@meglio](https://meta.discourse.org/u/meglio)
#### Post date: [June 5, 2017, 7:29pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/8 "2017-06-05T19:29:20Z")
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](https://github.com/KnpLabs/KnpTimeBundle).
Without AGO-ing, you can still format dates [with using native date filter](https://twig.sensiolabs.org/doc/2.x/filters/date.html).
You can also [format dates directly in you SQL query](https://www.postgresql.org/docs/9.6/static/functions-datetime.html).
---
### Author:  [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [June 6, 2017, 3:20am UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/9 "2017-06-06T03:20:30Z")
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:
---
### Author:  [@robbyoconnor](https://meta.discourse.org/u/robbyoconnor)
#### Post date: [June 6, 2017, 3:24am UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/10 "2017-06-06T03:24:32Z")
Where is this being shown?
---
### Author:  [@sunnyt7](https://meta.discourse.org/u/sunnyt7)
#### Post date: [June 6, 2017, 3:25am UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/11 "2017-06-06T03:25:24Z")
Using @meglio’s Twig Anything ([https://twiganything.com/](https://twiganything.com/)), on Wordpress.
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [November 21, 2022, 6:29pm UTC](https://meta.discourse.org/t/sql-query-in-data-explorer-to-pull-latest-topics/63908/14 "2022-11-21T18:29:33Z")