• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Forum system - how to track already read vs new messages, per user

#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 ()


Messages In This Thread
Forum system - how to track already read vs new messages, per user - by El Forum - 09-04-2009, 09:09 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.