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