# NULL first\_visited\_at dates in the topic\_users table
**URL:** https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996
**Category:** Development
**Created:** [2016年六月18日 09:17 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996 "2016-06-18T09:17:15Z")
**Posts on this page:** 6
**Page:** 1
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016年六月18日 09:17 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/1 "2016-06-18T09:17:15Z")
While investigating the topic users table last month
[Mysterious entries in topic\_users table](https://meta.discourse.org/t/mysterious-entries-in-topic-users-table/44080):
I noticed quite a few rows had NULL first\_visited\_at dates even though the last\_visited\_at date was not null.
Not certain whether or not it might be due to my dev set-up I ran this query to clean up the cruft.
```plaintext
UPDATE topic_users
SET first_visited_at = last_visited_at - interval '1 hour'
WHERE last_visited_at IS NOT NULL
AND first_visited_at IS NULL
```
(\* I didn’t think to give a total\_msecs\_viewed - a query for another day)
Shortly after more of the mysterious NULL first\_visited\_at fields appeared.
I’ve finally narrowed it down to Category Watching.
i.e. when a member is Watching a Category a row is created in the topic\_users table. The first\_visited\_at date is NULL when the member hasn’t visited the topic yet.
Unfortunately, when the member does get around to visiting the topic the first\_visited\_at field does not get populated.
These two queries should give much the same result
```plaintext
SELECT COUNT(id)
FROM topic_users
WHERE topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL
```
```plaintext
SELECT COUNT(topic_users.user_id)
FROM topic_users
JOIN topics
ON topics.id = topic_users.topic_id
WHERE topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL
AND topic_users.notification_level = 3
AND topics.category_id IS NOT NULL
AND topics.category_id IN( SELECT category_users.category_id
FROM category_users
WHERE category_users.notification_level = 3 )
AND topic_users.user_id IN( SELECT category_users.user_id
FROM category_users
WHERE category_users.notification_level = 3 )
```
---
### Author:  [@DeanMarkTaylor](https://meta.discourse.org/u/DeanMarkTaylor)
#### Post date: [2016年六月19日 01:09 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/2 "2016-06-19T01:09:49Z")
关于 `last_visited_at` 的问题也已在此处报告/详细说明：
> [@Is \`topic\_users.last\_visited\_at\` supposed to be updated on every visit?](https://meta.discourse.org/t/is-topic-users-last-visited-at-supposed-to-be-updated-on-every-visit/42164/):
>
> Am I completely misunderstanding entirely what these values are for? I’ve been using [Data Explorer](https://meta.discourse.org/t/32566?silent=true) to review activity and noted that topic\_users.last\_visited\_at didn’t seem to be updating / showing topics I have recently visited. Is topic\_users.last\_visited\_at supposed to be updated on every visit to a topic? Here is the Query I was using with [Data Explorer](https://meta.discourse.org/t/32566?silent=true). -- [params] -- int :user\_id = 1 SELECT topic\_id, last\_visited\_at, first\_visited\_at from topic\_users WHERE user\_id = :u…
具体而言，我在 4 月 7 日得出结论，该表并未正确更新。
我不确定自那以后此领域是否有任何代码更新。
我没有在 #Contribute > Bug 类别中报告此问题，因为在 Discourse 代码库中，这实际上并不是一个“问题”……
……然而，数据存储方式不一致，导致其他插件和自定义用户报告在运行时出现不一致。
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016年六月19日 04:48 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/3 "2016-06-19T04:48:22Z")
> [@DeanMarkTaylor](#):
>
> nowhere in Discourse code base where this was an actual “problem”
Not that I can see. I was thinking about [quote=“Mittineague, post:1, topic:45996”]
total\_msecs\_viewed
[/quote]
But when I checked earlier not a single row had Null or the default 0
The oddest were 5000 msec from the eviltrout seed posts. But they are seed posts so that isn’t too alarming.
Most of my coding experience is JavaScript, PHP and MySQL
Many other languages and databases too, but not to any great extent.
I was wondering if there might be something with Ruby time ↔ Postgres time,
But from what I’ve found online it seems they should play well together.
I’ve also noticed
```plaintext
TopicUser.create(attrs.merge!(user_id: user_id, topic_id: topic_id, first_visited_at: now ,last_visited_at: now))
```
and thought that maybe merge was discarding any second identical value.
But I have rows where both first\_visited\_at and last\_visited\_at had the same value, so unless it happens during a certain path I don’t think that’s it either.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016年六月27日 01:10 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/4 "2016-06-27T01:10:20Z")
I’ve done some more testing.
It definitely has to do with tracking state.
When a member is either Tracking or Watching a category, a row is inserted into the topic\_users table when a topic is created in that category.
A row is not inserted into the topic\_users table when a topic is created if a member has that category tracking state set to Mute or Normal.
And the row may not be created depending on other of the members Preference settings, if they post a reply in the topic etc. i.e. one of
```plaintext
created_topic: 1,
user_changed: 2,
user_interacted: 3,
created_post: 4,
auto_watch: 5,
auto_watch_category: 6,
auto_mute_category: 7,
auto_track_category: 8,
plugin_changed: 9
```
When a member visits a topic the logic tests for the presence of a row in the table.
If there is no row an INSERT is done.
If there is a row, an UPDATE
I think the solution would involve checking for the presence of a NULL first\_visited\_at field in someway somewhere. But I don’t know what would be the best way to go about it yet.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016年六月29日 23:07 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/5 "2016-06-29T23:07:56Z")
Well, that was fun. To easily test hacked Core code I ended up setting up a Ubuntu VM in my Windows 10.
Then inside the Ubuntu, I set up another VM per
> [@Install Discourse on Ubuntu or Debian for Development](https://meta.discourse.org/t/beginners-guide-to-install-discourse-on-ubuntu-for-development/14727):
>
> warning This guide covers installation instructions in a development environment. For a production guide see: [Install Discourse in production with the official supported instructions](https://meta.discourse.org/t/how-to-install-discourse-in-production/142537) So you want to set up Discourse on Ubuntu or Debian to hack on and develop with? We’ll assume that you work locally and don’t have Ruby/Rails/Postgres/Redis installed on your Ubuntu or Debian system. Let’s begin! Requirements We suggest having at least 4 GB RAM and 2 CPU cores. Current compatibility: O…
Anyway, this hack to models/topic\_user.rb “works”
```diff
UPDATE_TOPIC_USER_SQL = "UPDATE topic_users
SET
last_read_post_number = GREATEST(:post_number, tu.last_read_post_number),
highest_seen_post_number = t.highest_post_number,
total_msecs_viewed = LEAST(tu.total_msecs_viewed + :msecs,86400000),
notification_level =
case when tu.notifications_reason_id is null and (tu.total_msecs_viewed + :msecs) >
coalesce(uo.auto_track_topics_after_msecs,:threshold) and
coalesce(uo.auto_track_topics_after_msecs, :threshold) >= 0 then
:tracking
else
tu.notification_level
end
+ , first_visited_at = COALESCE(tu.first_visited_at, tu.last_visited_at, :now)
FROM topic_users tu
join topics t on t.id = tu.topic_id
join users u on u.id = :user_id
join user_options uo on uo.user_id = :user_id
WHERE
tu.topic_id = topic_users.topic_id AND
tu.user_id = topic_users.user_id AND
tu.topic_id = :topic_id AND
tu.user_id = :user_id
RETURNING
topic_users.notification_level, tu.notification_level old_level, tu.last_read_post_number
"
```
The logic is that when a row is INSERTed into the topic\_users table because the member has set a category to Watch / Track and a topic is created in that category, various fields will be NULL.
\* this is how it now is
With the new line of code in place, when a member gets around to actually visiting the topic and the UPDATE query runs.
- use the same first\_visited\_at timestamp if it exists
- else use the last\_visited\_at timestamp if it exists
- else use :now as a fallback
My testing indicates that this works great for topics that were created after the hack was in place.
Either NULLs will be left in place if the member does not visit the topic (as so it should be)
Or the “last” will be either the same or later than the “first” if a member visits the topic.
A possible concern is how to handle topics that were created before the new line of code was there.
The easy way would be to leave it if this data is not all that important - i.e. used elsewhere by other code.
But if having accurate and correct “first” values is important, this alone won’t do.
It would be possible to grab the first post of a member in a topic.
But I don’t feel as though the place for that is in this file running a more complex query all the time.
That is, I think if any “correction” were to be done it should be a one-time query.
More intensive resource use to run through the entire database, but it should hopefully need to be done only once.
---
### Author:  [@Mittineague](https://meta.discourse.org/u/Mittineague)
#### Post date: [2016年七月2日 22:27 UTC](https://meta.discourse.org/t/null-first-visited-at-dates-in-the-topic-users-table/45996/6 "2016-07-02T22:27:51Z")
I have the feeling that when I first looked into the weird topic id problem I may have opened a can of worms.
I’ve put together some queries that can help find the earliest “known” first\_visited\_at timestamps.
```plaintext
SELECT topic_users.topic_id
, topic_users.user_id
, topic_users.last_visited_at
FROM topic_users
WHERE ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
```
```plaintext
SELECT topic_users.topic_id
, topic_users.user_id
, MIN(posts.created_at)
FROM topic_users
JOIN posts
ON posts.topic_id = topic_users.topic_id
AND posts.user_id = topic_users.user_id
WHERE topic_users.posted
AND ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
GROUP BY topic_users.topic_id, topic_users.user_id
```
```plaintext
SELECT tu.topic_id
, tu.user_id
, MIN(pa.created_at)
FROM ( SELECT topic_users.topic_id
, topic_users.user_id
FROM topic_users
WHERE ( topic_users.first_visited_at IS NULL
AND topic_users.last_visited_at IS NOT NULL )
) AS tu
JOIN LATERAL ( SELECT posts.id
FROM posts
WHERE posts.topic_id = tu.topic_id
) AS pt
ON true JOIN LATERAL ( SELECT post_actions.created_at
FROM post_actions
WHERE post_actions.post_id = pt.id
AND post_actions.user_id = tu.user_id
) AS pa
ON true
GROUP BY tu.topic_id, tu.user_id
```
But I’m wondering just how important getting a more accurate first\_visited\_at timestamp really is.
I’ve noticed that when a member is mentioned or invited, a row in the topic\_users table is created.
Both the first\_visited\_at and last\_visited\_at fields are given :now even if the member _never visited_ the topic.