Total Views Error Discussion Summary
The discussion revolves around a discrepancy in the total views reported on the site UI and the topic_views table in the Discourse DB. ben.gerber noticed this issue with a specific topic, where the UI showed 9.1k views, but the DB query returned 5.7k views.
simon explained that the UI view count comes from the views column in the topics table, which increments once per user or IP address every 8 hours. In contrast, the topic_views table only records a single entry per user or IP address, resulting in a lower view count. This discrepancy is expected due to the different tracking methods used.
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.