Forum, get last post/topic

Question

Basically, I'm writing a small forum script for an intranet.

I've got 3 mysql (MySQLi) tables for the forum:

forum_answer - holds replies forum_question - holds first posts forum_categories - holds names and description of categories.

Ok, so on the forum index, I've got a table to fetch all the categories, however, I've also one column to fetch the last post made in that category/forum.

Do you have any ideas on how I could get the last post made in that forum?

I was thinking of maybe adding a new column on the forum_categories table, and updating it each time a post is made, however, that could get messy.

Thanks :)

Answer 1

You are actually thinking in the right direction.

You should definitely "cache" the last post for a question and the last post for a forum and store them in your [forum_question] and [forum_categories] tables respectively. "Cache" meaning update them each time somebody adds a new post or delete a post.

When your forum content amount reaches some threshold, the live calculations of last posts will be terribly slow. By updating the "cache" each time a new post is submitted you divide this huge one-time calculation work into small updates distributed onto many requests and thus almost unnoticeable.

The only hit you get when you delete a post, then you need to update cache for the question and the forum. But this is a rare event, so the price can be afforded.

Answer 2

Query-wise, you can get it with your query to get the list of categories using something similar to:

 select forum_categories.id, forum_categories.name, max( forum_answer.id ) as 
 from forum_categories
 left join forum_questions on forum_questins.category_id = forum_categories.category_id
 left join forum_answers on forum_answers.question_id = forum_questions.question_id
 group by forum_categories.id, forum_categories.name

How expensive an operation it is depends on the nature of your forums. Updating a column on the category every time someone posts might more expensive if people are posting frequently. If people are loading the list of categories frequently, the joins might be the more expensive operation.

You might also find it beneficial to create a view to pull your category list from:

 create view category_list as
 select forum_categories.id, forum_categories.name, max( forum_answer.id ) as latest_asnwer_id
 from forum_categories
 left join forum_questions on forum_questins.category_id = forum_categories.category_id
 left join forum_answers on forum_answers.question_id = forum_questions.question_id
 group by forum_categories.id, forum_categories.name

Which you could then access as though it were a table.