Last 7 days of topics

Product Support

AI-generated summary

In a support discussion titled “Last 7 days of topics,” adamprocter asked how to quickly list topics created or updated in the last 7 days. HAWK suggested using the Data Explorer plugin, which adamprocter hadn’t known about. SidV provided a SQL query to achieve this, which adamprocter successfully installed and tested. adamprocter then asked about the “limit” parameter and how to include URLs in results. SidV clarified that “limit” controls the number of returned results and shared an updated query using topic_users to include URLs. Mittineague suggested formatting results as clickable links using CONCAT with HTML, which SidV implemented and tested successfully, publishing the improved query as v1.1. The final query returns topic IDs, clickable titles, and creation timestamps for topics updated in the last week, excluding private messages, ordered by recency and limited by user-defined count.

1

Is there a way I get a quick list of topics that have been either created or updated in the last 7 days on my own discourse please?

2

Do you have the Data Explorer plugin?

3

Thanks. No I didn’t know about that cool. Now to write the correct query

4

Did you create one?

If not, check this query:

--[params]
--string :interval = 1 week
--int :limit
SELECT id, title, created_at as posted
FROM topics
WHERE age(created_at) < interval :interval
AND archetype != 'private_message'
GROUP BY id
ORDER BY created_at DESC
LIMIT :limit

List updated :rocket:

5

Thanks. Have managed to install plug in. So will try this next. :+1:

6

thank you, this seems to be working, I assume limit is the number of results returned ? in case there are 1000’s ?

How could I include URL in the results? i see this is not in posts and there are a few different one

7

Limit is the number of results returned and you must SET the limit on your query before run it.
See this:

image
image615Ă—594 25.1 KB

Try this:

--[params]
--string :interval = 1 week
--int :limit
SELECT tu.topic_id, t.title, t.created_at as posted
FROM topics t, topic_users tu
WHERE t.id = tu.topic_id 
AND age(t.created_at) < interval :interval
AND t.archetype != 'private_message'
GROUP BY tu.topic_id, t.title, t.created_at
ORDER BY t.created_at DESC
LIMIT :limit
8

Something like this should display a link in the results if you want that instead of the URL.

CONCAT('<a href = "/t/', topics.id, '">', topics.title) 
   AS html$topic
9

Great idea @Mittineague !

I took your code and add some new code :wink:

I tested, works like a charm

--[params]
--string :interval = 1 week
--int :limit
SELECT id, 
CONCAT('<a href = "/t/', id, '">', title, '</a>') 
   AS html$topic,
   created_at as posted
FROM topics
WHERE age(created_at) < interval :interval
AND archetype != 'private_message'
GROUP BY id
ORDER BY created_at DESC
LIMIT :limit

v1.1 added :rocket: