Ask Ben: Flagging Threaded Discussions As New Or Unread
My request is a tutorial or advice on how to work out the threads / comments on a forum that a forum user hasn't read yet or that were posted since the last time he logged in and marking them as such.
First off, we have to recognize that flagging "unread" threads and "new" threads are very different gestures. A thread is new based on a given point in time. A thread is unread based on a particular relationship that is specific to the current user and the thread in question.
By far, the easiest gesture here is the "new" thread. In order to denote threads as new, all you have to do is store the previous login date of the user. This doesn't even have to be a server-stored value; you could easily store this kind of non-mission-critical data as a cookie value just as easily as you could in a user record in the database. Once you have this value, any thread that was created or updated since the user's previous login date could be visually flagged as new.
The pseudo code for this might look something like (I have no way to test this):
<cfloop query="qThread">
<tr>
<td>
<!---
Check to see if this thread was created or updated since
the user last logged into the message board.
--->
<cfif (qThread.date_updated LT SESSION.User.DateLastLoggedIn)>
[NEW]
</cfif>
#qThread.name# (#qThread.reply_count# replies)
</td>
</tr>
</cfloop>
Denoting threads as unread is a more complicated gesture. Now, you have to be storing the user's view relationship with each thread. In order to do this, we need to have some sort of a data table whose job it is to record this data. Perhaps something like this:
thread\_view
---------------------
user\_id :: _int_
thread\_id :: _int_
date\_created :: _datetime_
Now, every time a user views a thread, you can insert a new record into this table for that given user and that given thread. If you want to make it more simple, you can remove the date_created field (the date the user viewed the thread) and use this table a binary-flag (viewed or did not view ) rather than a sort of audit trail. Once you have this data in place, you can join it to queries that collect thread information.
The pseudo code for this might look something like (I have no way to test this):
SELECT
t.id,
t.name,
<!---
Check to see if we have a view record for this
thread and this user. If we do not (key in joined
table is NULL), then this thread is UNREAD.
--->
(
CASE
WHEN
v.thread_id IS NULL
THEN
1
ELSE
0
END
) AS is_unread
FROM
thread t
LEFT OUTER JOIN
(
SELECT
thread_id
FROM
thread_view
WHERE
user_id = #SESSION.User.ID#
GROUP BY
thread_id
) AS v
ON
t.id = v.thread_id
As you can see, as we gather the thread data, we are checking to see if the given thread record has a "view" record in our thread_view table. Now that we have a query like this, the output might look something like this:
<cfloop query="qThread">
<tr>
<td>
<!--- Check to see if this thread is unread. --->
<cfif qThread.is_unread>
[UNREAD]
</cfif>
#qThread.name# (#qThread.reply_count# replies)
</td>
</tr>
</cfloop>
I am sorry if the code above has odd bugs; as this example is very contextual, it's hard for me to test. I hope, however that this can point you in the right direction or give you a little inspiration.
Want to use code from this post? Check out the license.
Reader Comments
Hi,
I think on some forums they do it another way around.
First, they assume only posts made since the last time you logged in should be marked as not viewed.
Then they make a list of posts that you HAVE viewed during this session and it is from this list that they can then show you which ones you have read, or assumed to have read or ignored from previous sessions.
This way they can keep a temporary record of the posts you have currently read in this session, and then when you leave the session they can dispose of the info and when you start the next session, the unread posts are the new ones with read ones being added to the read list.
hope that made sense??
cheers,
Mat
@Mat,
What is the relationship between sessions? You lost me a little bit there. When a new session starts, it assumes all threads since the last login are "new". Then you view several, and they keep that in the user's session and use it to update the display whenever the user returns to the listing page (in the same session). But then when the session closes, all we store is the last login date?
Ok, I think that makes sense. I guess it depends on how accurate it needs to be. My hunch is that in most cases, that is going to be accurate enough :)
@Ben,
Sorry I used sessions in a fairly fuzzy way there.
But your description was good.
I think it's just a more efficient way of providing that sort of functionality.
I've seen pretty massive forums, and with 10000 users and a million posts a table holding which posts have been looked at is gonna get pretty big.
As you said, it depends how accurate you want to be, and in reality I dont think people really need to know if they haven't read a thread from a couple of years ago.
Mat
@Mat,
100% agree.
http://pastebin.com/m37427566
Lets see if I can remember how I do it...
Basically, until a user looks in a topic, that topic remains new unless the last reply date is 14 days or older.
When the user looks in the topic, store the userid, date, and topic id.
Now, whenever a new reply for that topic id is added, remove all stored references of that topicid, so when the user comes back, we'll go back to square 1.
Each time the the system looks for a stored status, it'll trigger deleting anything older then 14 days, so like someone mentioned above the table doesnt get out of hand.
And of course adding things like locked, sticky, has the user replied in that topic, with new & old all in one status icon
If you want to see all the source for how I do it let me know, its a bunch of stuff... hmm also looks like I need to clean up the source in the pastebin.
@Don,
I like the idea of deleting anything older than 14 days. I agree that at that point flagging things as new simply loses value.