[eluser]jedd[/eluser]
[quote author="kurucu" date="1251897795"]
With your message subtable, how do you propose to collect all the messages for displaying each thread?
[/quote]
Propose? Most of my basic core functionality is in play already.
Note that I share thread & message with my private-mail system (which is a kind of whispered thread variation to the whole forum system).
Here's my query to grab the messages for a given thread (it includes pagination support - ie. the LIMIT)
Code:
function get_list_of_messages ( $thread_id = 0 , $offset = 0 , $count = 10 ) {
$query_string = "SELECT
message.id,
message.msgdate,
member.handle AS author_name,
member.id AS author_id,
member.created AS author_created,
message_content.actual AS content
FROM
message
LEFT JOIN
member ON message.author=member.id
LEFT JOIN
message_content ON message.id=message_content.id
WHERE
message.thread_id=". $thread_id ."
ORDER BY
msgdate ASC
LIMIT ". $offset ." , ". $count ;
$results = $this->my_get_rows ($query_string);
// It'd be nice to do this in the main SQL call above, but can't work out how ...
if ($results)
foreach ($results as $result=>$message)
$results[$result]['author_posts'] = $this->get_number_of_posts_by_author($message['author_id']);
return $results;
} // end-method get_list_of_messages ()
I'm also not too sure about the last bit there - getting post-count-by-author. At the moment I'm running a pretty-much 3NF schema, but realise I may have to denormalise. The function cited there (get number of posts by author) actually does a count() against messages at the moment, but can easily swing back to a lookup to the member table.
Quote:I was actually thinking of doing something similar so that post editing history could be reviewed.
I'm not sure what to do with this.
I have my activity log, of course, and was wondering about just having a category in there for message edits .. but then it really blows out the (var) width of that table compared with the original intent of the log.
I thought about never deleting posts, just flagging them as previous instances and effectively hiding them - but then I'd end up with quite a messy two-way linked list within my messages structure, chronology would no longer reliably map to ID order, and I'd still have to work out a way to identify deltas. I toyed with having a separate log just for messages - so a snapshot after each edit gets dumped in there - and I have a joiner table to link message.id with message_snapshot.id say.
At the moment I'm thinking I might just flag that a message *has* been edited .. and leave it at that.
What's your current thinking on this one?
Quote:My problem was finding out where the post in question lies, rather than the total number of posts. I think I'll go with counting all posts with the wanted post's date or earlier for now. I wondered if there was a faster way to do it than perform several queries before the page generation query, or at least minimising them.
While I'm all for reducing the number of queries .. remember these are pretty simple and cheap ones, especially if you've indexed a few of the key (ha ha) fields. And these types of queries are perfect cache candidates.
Are your message numbers going to skew from date order? If they're not, it remains very simple. If they do, then the only obvious complication there is sorting by your date field instead .. yeah?
