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?
Do you have the Data Explorer plugin?
5 Likes
Thanks. No I didn’t know about that cool. Now to write the correct query
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
5 Likes
Thanks. Have managed to install plug in. So will try this next. 
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
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
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
1 Like
Great idea @Mittineague !
I took your code and add some new code 
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
4 Likes
This topic was automatically closed after 2249 days. New replies are no longer allowed.