The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

And that's what I suggest. Technically you could go through all the posts, look at their last_post_time and float that up to the parent, but to me I don't think that's very worthwhile just to try to keep it normalized. If you let users sort 30 topics per page, each topic having 50 posts each, that's like, one metric crap ton of extra overhead than just adding one extra field to Topic that makes it one-query-able. You might not even need to add an extra field... just cram the last_post_time into a Topic's "updated_at" field and re-save the Topic when a new Post is submitted. That could make sense if you enjoy the idea that a Topic is "updated" if someone posts to it. You could also argue that that is a stupid idea. I think I might like the updated_at idea, but depends on the coder, I think.

I would suggest using a counter_cache which would knock out two birds with one stone; keep track of how many posts there are in a topic (and how many topics in a category) and use the power of Rails' automatic timestamping to keep track of the last post. Try this:

You'll also need to add an integer column named "topics_count" to your categories migration and another one named "posts_count" to your topics migration. Lastly, add an index to your topics and posts migrations:

They'll already be sorted by date of last post. The counter cache updates the "topics_count" column in the category table upon each create/destroy which in turn updates the "updated_at" timestamp which you're sorting by in the model.

The counter_cache also lets you know how many topics are in a category by using

Code Ruby:

@category.topics_count

Instead of

Code Ruby:

@category.topics.length

That gives you the added speed/efficiency bonus of not having to make an extra DB query. Same goes for the amount of posts in a topic.

@lo0ol: You are right, going through all posts of all topics in a category would be way too much overhead. Normally I would like to have a normalised schema - but sometimes it`s not worth it. Anyway, I tried realmadrid`s approach - and it works like a charm. I just had to do a minor correction:

Code Ruby:

:counter_cache=>true# instead of:counter_cache=> :true

Beside that the fields topics_count and posts_count need a default value of 0.