Welcome Guest, Not a member yet? Register   Sign In
Forum system - how to track already read vs new messages, per user
#31

[eluser]jedd[/eluser]
xwero - I should learn to type faster. Smile

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.
#32

[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.
#33

[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)
    );
#34

[eluser]xwero[/eluser]
Why use a blob for text?
#35

[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.
#36

[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.
#37

[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.
#38

[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! Smile 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 ()
#39

[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? Smile
#40

[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. Wink




Theme © iAndrew 2016 - Forum software by © MyBB