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