I am developing a forum style rails app (my first one). Everything has been wonderfully simple and clean so far. However I have got to the point, which seems inevitable whenever I use SQL, where a seemingly simple query threatens to derail me (no pun intended). Say I have this simple schema:
create_table :posts do |t| t.column :user_id, :integer t.column :subject, :string t.column :body, :text t.column :created_at, :datetime end
create_table :comments do |t| t.column :user_id, :integer t.column :body, :text t.column :created_at, :datetime end
I want my forum post list to be ordered by 'last activity', which would be the time of a post or its last comment. This means that a new comment will move a post to the top of the list.
This seems to be a simple problem without a simple solution. I would like to investigate all avenues since it seems that each will have its drawbacks. Here are the solutions I have been looking at:
1) Create a large, complicated query, using either aggregation or a subquery, to get the correct ordering of posts. Can't think of how to do this off the top of my head but it should be possible. Also what would the performance be like? If I have 50,000 posts would I still be using a raw query, or would I be caching queries, or would I be caching further up the rails stack?
2) Refactor my design. Remove posts.body and use a comment instead for the post body. This could be done either by making Post inherit from Comment, using a separate 1-1 relationship between Post and Comment for the post body, or using the first comment as the post body. Of theses solutions only the latter will simplify my listing query. Seems wrong to change my design for a query.
3) Add a last_activity column to posts. This would be a datetime. It would be initialized to Time.now() when a post is created, and updated when a new comment is added. Solves the query problem. The column is redundant though. What's the best way to implement this? Seems like it should be done in the model. Right now my comments are created like this:
c = Comment.new(params[:comment]) c.user = @current_user c.post = @post c.save ...
Seems like I could put a callback in to the Comment model to update the last_activity column in the Post, but then I would have to call Post#save. Are there any implications to this? Also I understand that a comment could be associated with a Post by doing @post.comments << c. What would my last_activity code do then?
Any thoughts would be greatly appreciated. TIA Tim