Forum: Ruby on Rails forum style 'last post' query

Announcement (2017-05-07): is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see and for other Rails- und Ruby-related community platforms.
5344316646d91078bfe5321f3a98801c?d=identicon&s=25 Tim (Guest)
on 2007-06-05 11:25
(Received via mailing list)

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

    create_table :comments do |t|
        t.column :user_id, :integer
        t.column :body, :text
        t.column :created_at, :datetime

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 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])
  c.user = @current_user = @post

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.
21f7ed21f11a809050594c82eab11d67?d=identicon&s=25 Robert Walker (Guest)
on 2007-06-05 20:57
(Received via mailing list)
If it were me I would simplify your query by adding a last_activity
field to your posts table (your option 3).

As far as updating this field I would avoid coupling the Post and
Comment models.  I would take care of updating the last_activity on
post in the create action on the comments_controller.

You might also consider using an after_filter to update the field in
case other activities should also update the last_activity, such as
edits to comments using the update action on comments_controller.

Something like this:

after_filter update_last_activity, :only => [ :create, :update ]

def create
  @comment =[:comment])

def update_last_activity =  # <--- I'm not sure if this is necessary

This is just one possible solution (totally untested) off the top of
my head.  There may be better approaches, but I still believe the
last_activity column on Post is the most efficient way to do what you
This topic is locked and can not be replied to.