In a discussion titled “Is it possible to see the topics a user has viewed?” under the Data & reporting category with the tag sql-query, metadiscourseuser seeks detailed logs of user topic views beyond basic stats available in the Admin panel. After installing the Data Explorer plugin without success, ditatompel provides two SQL queries: one to show which logged-in users viewed a specific topic, and another to list the last 100 topics viewed by a specific user. SidV praises the queries and notes they’ve been added to a GitHub repository, encouraging further contributions. metadiscourseuser confirms the solution worked perfectly and expresses gratitude. The discussion highlights how SQL queries via Data Explorer can uncover granular user activity data not visible in standard admin interfaces.
I’m analysing the user stats & activity on our forum.
I’d like to see the specific topics a user has viewed. So far the most detailed activity I can find about the user is just the number of “Topics Viewed” & “Posts Read” in the Admin → Users grids.
I’ve installed the Data Explorer plugin to see if there’s more data, but haven’t been able to find anything useful in terms of detailed user activity logs & topic/post access.
-- [params]
-- int :topic_id = 1
SELECT
title,
viewed_at,
tv.user_id
FROM topics t
LEFT JOIN topic_views tv
ON t.id = tv.topic_id
WHERE category_id IS NOT NULL
AND tv.user_id IS NOT NULL
AND t.id = :topic_id
ORDER BY viewed_at DESC
LIMIT 1000
Last 100 topic view by user
-- [params]
-- int :user_id = 1
SELECT
tv.user_id,
title,
viewed_at,
views,
t.user_id
FROM topics t
LEFT JOIN topic_views tv
ON t.id = tv.topic_id
WHERE category_id IS NOT NULL
AND tv.user_id = :user_id
ORDER BY viewed_at DESC
LIMIT 100