Total Views Error

The total views reported on the site UI is different than what’s showing in Discourse DB’s topic_views table(see below for context).

topic link(currently at 9.1k views): https://talk.fabfitfun.com/t/we-need-your-help-to-prepare-for-summer-edit-19/1170607

query(returning 5.7k views):
select topic_id, count(*) total_views
from public.topic_views
where topic_id=1170607
group by topic_id

The topic view data that is displayed in the UI comes from the views column of the topics table. The views column is incremented once per user_id or ip_address per topic_id every 8 hours. The 8 hour period can be changed by overriding the default value of the topic_view_duration_hours site setting.

The topic_views table only gets a single entry per topic_id per user_id or ip_address. If a user views a topic multiple times, there will only be a single entry for them for the topic in the topic_views table. The data from this table is used internally by Discourse to check if a user has met the trust level 3 requirements for topics entered.

You can test this yourself by running the following query with a :topic_id from your site:

select
COUNT(user_id) AS views_per_user
FROM topic_views
WHERE topic_id = :topic_id
GROUP BY user_id
ORDER BY views_per_user DESC

You’ll see that views_per_user is always 1. This means that you can expect topics.views to return a higher value than the sum of topic_views for a particular topic ID.

4 Likes