Last 7 days of topics


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


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


(adamprocter) #5

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


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

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


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