+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Forum system - how to track already read vs new messages, per user (/showthread.php?tid=17292)
Forum system - how to track already read vs new messages, per user - El Forum - 04-02-2009
[eluser]jedd[/eluser]
xwero - I should learn to type faster.
I like the idea of bookmarks - as a kind of flag to attach to forum threads, or even messages?
As I hinted in my last message I want to look at using common tags for forum and wiki (as well as blog, articles, news, etc - who knows what I might have going on).
I see these as evolutionary steps, so shall make yet another @TODO note in my code.
Forum system - how to track already read vs new messages, per user - El Forum - 04-02-2009
[eluser]xwero[/eluser]
As long as you are adding todos. i think the whispered conversations the vanilla forum offers are better than private messages. Then you can see the message in context.
Forum system - how to track already read vs new messages, per user - El Forum - 04-02-2009
[eluser]jedd[/eluser]
Okay.. that reminded me to fix up my mail handling, as I'd done some dodgy dual-use stuff with one of the fields in my 'one size fits all' message table. And I hate dodgy dual-use fields - I always forget one of the uses.
I'm doing private messages as threads that have a forum ID of 0 - this is special (not keen on specials, but it'll work for now) that indicates the thread belongs to mail, not forum. I then have a separate table to denote who has access to a thread. This means I don't really need the special of '0', but will reconsider this later. The bulk of threads will not have an entry in my thread_acl table - only private messages will, and they'll require two rows (sender, receiver). This means I can possibly cater for semi-private forums & threads in the future.
In any case, mail will be more forum-like, as I agree with your suggestion on this aspect. There is now no concept of an 'addressee' for a message. The addressee is effectively the other person you invite to your private thread at message compose time.
Anyway, I present my current messaging schema for completeness and comments.
One comment and question up front - I've separated out the message_content (4k BLOB) into its own table on the expectation I'll get better performance. Do we think that this is true? I will probably go VARCHAR on that actual message content column. I expect that I won't be hitting message very often without hitting message_content at at the same time (LEFT JOIN to grab the actual content). It's definitely a 1:1 relation, which is why I feel a bit uneasy about separating it into two tables.
Code:
# forum
#
# Likely only a few dozen of these.
#
# @TODO - consider having parent_id in here, to allow for sub-forums, or forum headings
# so perhaps a BOOL flag to denote if it's a grouping or a real forum.
#
# For now - flat structure - every forum is an island.
CREATE TABLE forum (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR (100) UNIQUE NOT NULL, # Eg. 'Seed saving'
description CHAR (250), # Eg. 'To discuss the saving of seeds'
PRIMARY KEY (id),
INDEX (name)
);Code:
# thread
#
# Threads - for forums and private mail
CREATE TABLE thread (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
forum_id INT UNSIGNED NOT NULL, # FK to forum.id
sticky BOOL, # To show at the top of every forum, or not
views SMALLINT, # We don't care about tracking > 65k views
subject CHAR (99), # Eg. 'Problem with drying out tomato seeds'
PRIMARY KEY (id),
INDEX (forum_id),
INDEX (subject)
);Code:
# message
#
# All private messages and public (forum) messages will live in this one table. I think.
CREATE TABLE message (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
thread_id INT UNSIGNED DEFAULT 0, # FK to thread.id (every message is part of a thread)
author INT UNSIGNED DEFAULT 0, # FK to member.id (every message has an author)
msgdate DATETIME, # Date of message creation
message_content_id INT UNSIGNED NOT NULL, # FK to message_content.id
PRIMARY KEY (id),
INDEX (thread_id),
INDEX (author)
);Code:
# message_content
#
# The actual content / body / text - variable size, might be quite huge, single index for now
#
# @TODO - will need to look at requirements for indexing the .text column
CREATE TABLE message_content (
id INT UNSIGNED NOT NULL UNIQUE, # Same as message.id
actual TEXT(4096), # Gives us 4k per message
PRIMARY KEY (id)
);Code:
# thread_acl
#
# Rights to threads - will probably only ever be used for mail/private messages,
# so two rows per private thread (author/addressee).
CREATE TABLE thread_acl (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
thread_id INT UNSIGNED NOT NULL, # FK to thread.id
member_id INT UNSIGNED NOT NULL, # FK to member.id
PRIMARY KEY (id),
INDEX (thread_id),
INDEX (member_id)
);Code:
# message_member
#
# Tracks who has read what messages - or rather, where a member is up to
# in a particular thread.
#
# NOTE: only ever one row for any given [ thread_id , member_id ] pairing.
CREATE TABLE message_member (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
member_id INT UNSIGNED NOT NULL, # FK to member.id
thread_id INT UNSIGNED NOT NULL, # FK to thread.id
message_id INT UNSIGNED NOT NULL, # FK to message.id
PRIMARY KEY (id),
INDEX (member_id),
INDEX (thread_id),
INDEX (message_id)
);Forum system - how to track already read vs new messages, per user - El Forum - 04-03-2009
[eluser]xwero[/eluser]
Why use a blob for text?
Forum system - how to track already read vs new messages, per user - El Forum - 04-03-2009
[eluser]jedd[/eluser]
Fixed.
If that was the only problem you could find, I'm cracking this dodgy bottle of Peruvian red and starting Saturday early.
Forum system - how to track already read vs new messages, per user - El Forum - 04-03-2009
[eluser]TheFuzzy0ne[/eluser]
OK, I've been thinking. Hopefully I'm on the right track here, because the last two pages of this thread have confused the bejesus out of me...
I still think a library should exist for this reason. The only problem I can see, is that the library would have to be aware of the forum structure to some extent, in order to mark child forums as being read.
To facilitate this, I think the library can contain functions that need to be overridden in order to return the data necessary. For example:
Code:
function get_child_forums($forum_id=0)
{
# The user will override this function, which will return the child forums
# of the specified forum. It's up to the user how they do this, the overridden
# method can call the database, or a cached array, a file - anything!
}And in case it's not already clear, the library will actually be a model, and the forum model should extend this model. Basically, the user is able to describe their forum structure to the library.
Now, I know I need to think this through a lot more, and perhaps have the forum structure represented by an array or something, but I think this is the key to creating a portable library - by bridging the gap between the library and the users' set-up.
Forum system - how to track already read vs new messages, per user - El Forum - 09-04-2009
[eluser]TheFuzzy0ne[/eluser]
Hi, everyone. I've spent several months working on the most efficient way to track read posts, but I still can't figure it out... xweros idea is pretty flawless, however, where I'm stuck, is when it comes to displaying the actual forums which contain posts that have not been read. I have no idea on the most database efficient method to show when a forum contains unread topics/posts. I'd appreciate any input anyone may have to offer. I used to have a full head of hair, and to say it's thinning out a bit is putting it mildly.
Many thanks in advance.
Forum system - how to track already read vs new messages, per user - El Forum - 09-04-2009
[eluser]jedd[/eluser]
Similarly, I have no idea if I've done this in the most database-efficient way (and I'll defend the query-within-a-loop below as it was proof of concept stuff that I haven't gone back to optimise yet!
but here's what I've got to so far. My schema is pretty much as previously described in this thread, with the additional mention of my tracking table.Code:
// message() model extract ...
// ------------------------------------------------------------------------
/**
* Get list of forum threads
*
* @access public
* @param $forum_id forum.id
* @param $offset where to start
* @param $count how many to return
* @return array
*/
function get_list_of_threads ( $forum_id = 0 , $offset = 0 , $count = 10 ) {
$query = $this->db->query ("SELECT
id, subject, locked, view_count
FROM
thread
WHERE
forum_id=". $forum_id ."
ORDER BY
thread.id ASC
LIMIT ". $offset ." , ". $count
);
if ($query->num_rows() == 0)
return FALSE;
$threads = $query->result_array();
// Go through each of the threads, and grab supplemental information
for ( $x = 0 ; $x < sizeof($threads) ; $x++ ) {
// Count threads in the forum
$query = $this->db->query ("SELECT
COUNT(*) as count
FROM
message
WHERE
thread_id=". $threads[$x]['id']
);
$count = $query->row_array();
$threads[$x]['total_messages'] = $count['count'];
if ($count['count'] > 0) {
// Grab first message in thread to get date and author
$query = $this->db->query ("SELECT
msgdate, member.handle
FROM
message, member
WHERE
thread_id=". $threads[$x]['id'] ."
AND member.id=message.author
ORDER BY msgdate ASC
LIMIT 1"
);
$first = $query->row_array();
$threads[$x]['first_post_date'] = $first['msgdate'];
$threads[$x]['first_post_author'] = $first['handle'];
// Grab last message in thread to get most recent post date and author
$query = $this->db->query ("SELECT
message.id AS message_id,
msgdate,
member.handle
FROM
message, member
WHERE
thread_id=". $threads[$x]['id'] ."
AND member.id=message.author
ORDER BY msgdate DESC
LIMIT 1"
);
$last = $query->row_array();
$threads[$x]['last_post_date'] = $last['msgdate'];
$threads[$x]['last_post_author'] = $last['handle'];
$threads[$x]['last_post_id'] = $last['message_id'];
// Assess if there are new messages for the user in this thread
$last_read_message_id = $this->get_last_read_message_in_thread ($this->my_id , $threads[$x]['id']);
if ($last_read_message_id)
$threads[$x]['new_messages_exist'] = ($last_read_message_id == $threads[$x]['last_post_id'] ) ? FALSE : TRUE;
else
$threads[$x]['new_messages_exist'] = TRUE; // by defintion
// Flag whether this is a watched-thread or not
$query_string = "SELECT
watched
FROM
member_message
WHERE
thread_id = ". $threads[$x]['id'] ."
AND
member_id = ". $this->my_id;
$threads[$x]['watched']= $this->my_get_field($query_string , "watched");
}
else {
$threads[$x]['watched'] = FALSE;
$threads[$x]['new_messages_exist'] = FALSE;
$threads[$x]['first_post_date'] = "n/a";
$threads[$x]['first_post_author'] = "n/a";
$threads[$x]['last_post_date'] = "n/a";
$threads[$x]['last_post_author'] = "n/a";
}
}
return $threads;
} // end-method get_list_of_threads ()Forum system - how to track already read vs new messages, per user - El Forum - 09-04-2009
[eluser]jedd[/eluser]
To track where a user is up to in a thread, I use this table:
Code:
# NOTE: only ever one row for any given [ thread_id , member_id ] pairing.
CREATE TABLE member_message (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
watched BOOLEAN DEFAULT FALSE, # Is the member watching /subscribed to this thread?
member_id INT UNSIGNED NOT NULL, # FK to member.id
thread_id INT UNSIGNED NOT NULL, # FK to thread.id
message_id INT UNSIGNED NOT NULL, # FK to message.id
PRIMARY KEY (id),
INDEX (member_id),
INDEX (thread_id),
INDEX (message_id)
);The table above is what is referred to in the get_last_read_message_in_thread() function in the previous post. Sorry I had to split things up after the forums got confused about the message length.
In my forum controller, well, the previous message's stuff is called from the /forum/threads/ method, but when I start to actually read messages, I have this bit in the messages controller to update the 'last read message' info:
Code:
// extract from forum controller, message() method
$last_message_id_shown_on_this_page = $data['messages'][$last_array_element]['id'];
$this->Message->set_last_read_message_in_thread ( $this->my_id , $thread_id , $last_message_id_shown_on_this_page );I reasoned it was more sensible to control this update from the controller, partly because of the complexity introduced by pagination, and the fact that my model functions might not always be doing their calls for the current logged-in user. Of course, you can grab full source on that URL I mentioned earlier if you want more context.
I'm also thinking that whenever a user goes to read a thread that is older (most recent message's age) than a given age - probably 1 month - it'll lock that thread. Thread-locking will involve going through the above table and rows with that thread #.
Periodic cron-style jobs can be run to catch everything else.
Any thread that is locked will appear as read (the default case). Any thread that isn't locked, and does not appear in this table, will be considered to be unread / new.
Thread-watching is something else entirely, and I made a mistake early of trying to hybridise the two features. I had a 'watched' flag in the above table, and just took it out as I think of it as a throwback, but I'm still actually undecided on how to handle watched threads that are locked. For storage reasons, it makes sense to cull them. But it's a trivial amount of space, I guess, and a later-unlocked thread would be of interest to any ex-watchers ... arguably. In any case, watching is a separate function.
So, how far is all this from being useful to you?
Forum system - how to track already read vs new messages, per user - El Forum - 09-05-2009
[eluser]TheFuzzy0ne[/eluser]
Thanks for your input, Jedd. Unfortunately, I'm not quite sure how this ties in with my original question. I've been looking at PHPBBs code, but I just can't seem to figure out what it's doing and how. As I mentioned, tracking the posts is not the problem; the problem is how I can efficiently indicate that the forum "container" contains unread threads. Also, this needs to take into account any subforums, too. I hope this makes a bit more sense. I regret that I am not as well versed in the English language as you clearly are.
