NULL first_visited_at dates in the topic_users table

dev
5

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

Anyway, this hack to models/topic_user.rb “works”

    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.

4 Likes