Is it possible to see the topics a user has viewed?

AI-generated summary

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.

TIA

Maybe something like:

Who (logged in user) view specific topic

-- [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

Great queries !

I just upgraded the query’s list in github with your code.
If you have more queries, don’t hesitate to contribute there. :+1:

Awesome! Thanks very much, it worked like a charm!