# Forum style 'last post' query

**URL:** <https://www.ruby-forum.com/t/forum-style-last-post-query/105991>\
**Category:** Rails\
**Created:** [June 5, 2007, 11:25am UTC](https://www.ruby-forum.com/t/forum-style-last-post-query/105991 "2007-06-05T11:25:59Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![tim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ce73a5/32.png) [@tim](https://www.ruby-forum.com/u/tim)\
**Post date:** [June 5, 2007, 11:25am UTC](https://www.ruby-forum.com/t/forum-style-last-post-query/105991/1 "2007-06-05T11:25:59Z")

</div>

Hi

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

---

<div class="post-metadata">

**Author:** ![tim](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ce73a5/32.png) [@tim](https://www.ruby-forum.com/u/tim)\
**Post date:** [June 5, 2007, 8:57pm UTC](https://www.ruby-forum.com/t/forum-style-last-post-query/105991/2 "2007-06-05T20:57:38Z")

</div>

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.new(params[:comment])  
…  
…  
…  
end

protected  
def update\_last\_activity  
@comment.post.last\_activity = Time.now  
@comment.post.save # \<— I’m not sure if this is necessary  
end

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  
want.